Linking to files

Posted by George on May 08, 2001 12:38 PM

Col. A Col. B
Depart Link
A +'\\Lisbon\[A.xls]Sheet1'!$C$32C:
B +'\\Lisbon\[B.xls]Sheet1'!$C$32C:
C

I have over 200 departments. Edit replace will take too long. All department files are set up in the same format. How can I get $C$32 from each file?

Posted by Sean on May 09, 2001 6:04 AM

I have created a spreadsheet called Links.xls
The Tab name is also Links
Col A Col B

Department Link
A 378
B 8478
C 5778
D 3078

Row 1 Holds the Title The Data begins in Row 2

Enter in a continuos list all the Departments (ie Filenames) excluding the .xls qualifier

Then in VB (Alt +F11)

Paste the following macro into the module.

Sub Link_It()

Application.ScreenUpdating = False

Sheets("Links").Select
Range("A2").Select
Selection.End(xlDown).Select
curref = Selection.Address
lstrow = Right(curref, Len(curref) - 3)

For rowno = 2 To lstrow
Windows("Links.xls").Activate
deptrng = "Links!A" & rowno
lnkcell = "B" & rowno
deptfil = Range(deptrng).Value
deptfil2 = deptfil & ".xls"
Workbooks.Open FileName:=deptfil2
deptform = "=[" & deptfil & ".xls]Sheet1!R32C3"
Windows("Links.xls").Activate
Range(lnkcell).FormulaR1C1 = deptform
Workbooks(deptfil2).Close
Next

Application.ScreenUpdating = True

End Sub

Notes:

All the department files must be in the same (current) directory as must the linked (consolidating file).

The macro goes through each of your departments until the end of the continuous list is reached.

The macro will open and close these files as needed.

I hope this helps.

Sean
s-o-s@lineone.net