Creating links to workbooks in folder using VBA

Creating links to workbooks in folder using VBA

Post by Hugo Phon » Tue, 05 Jun 2012 16:57:25



   Dear Members

   I have a number of excel workbooks in a folder. I need to be able to have
   Excel check for the existance of Excel workbooks in the folder. Then
   using
   vba, write an automatic link formula from the contents of cells C1 to
   C20 in
   the files. The link formula will appear in a master workbook that links
   to
   all of the files in the folder. The number of files will vary from ten to
   thirty files. The link formula in the master file will commence in cell
   address c1 and then appear in columns D, E, F etc.

   I have been able to generate a link formula if I use specific filenames
   but
   if the files do not exist the code generates an error.

   Any assistance would be greatly appreciated.

Hugo Phong

Sub Linker()

Application.ScreenUpdating = False

Application.Goto Reference:="start"

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS1.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS1.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select
   ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS2.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS2.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS3.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS3.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

   ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS4.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS4.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select
   ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS5.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS5.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS6.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS6.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

  ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS7.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS7.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS8.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS8.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS9.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS9.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS10.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS10.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select
   ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS11.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS11.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select
   ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS12.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS12.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

'13.......................
ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS13.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS13.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS14.xlsm]Responses!R1C5"
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=[ANS14.xlsm]Responses!RC5"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A75"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A75").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(0, 1).Range("A1").Select

  ActiveCell.Offset(0, 1).Range("A1").Select

Application.ScreenUpdating = True

End Sub