External file link

[superkopite]

Hiya guys and gals

I have a problem.

I have a work sheet that references 10 external workbooks.

What i need to do is for my worksheet to look for the external workbook
in the same folder as the main workbook is stored in instead of having
the full C:\Documents and Settings\James\Excel\

As i have to create multiple documents and email them to others they
may chose to save them in alternate locations. If they do this when the
workbooks are opened they will not be able to recognise the links.

I hope i have explained myself correctly.

Regards

James

 

[edcosoft]

Yes, I understood your problem. The only way to solve it is to put
all the Worksheets in the same Workbook because there's no way to
"bundle" more than one Workbook, but you can "bundle" many Worksheets
in one Workbook.

ed

 

[Guest]

Maybe you could try a Workbook Open Event handler to change the links. Paste
the code into the thisworkbook module in the VB Editor (Alt+F11, View/Project
Explorer, double click on the thisworkbook module of your spreadsheet to open
up a code window). Be sure to back up your work.

Private Sub Workbook_Open()
Dim varLinks As Variant
Dim i As Long
Dim strFName As String

varLinks = ThisWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(varLinks) Then
For i = LBound(varLinks) To UBound(varLinks)
With ThisWorkbook
strFName = Left(.FullName, _
InStrRev(.FullName, "\", -1, _
vbTextCompare)) & Right(varLinks(i), _
Len(varLinks(i)) - InStrRev(varLinks(i), _
"\", -1, vbTextCompare))

If Dir(strFName) <> "" Then
.ChangeLink Name:=varLinks(i), _
NewName:=strFName, Type:=xlExcelLinks
Else: MsgBox "File " & strFName & "Not Found"
End If
End With
Next i
End If
End Sub

 

[superkopite]

thanks guys

I will try these out when i get to work and let you know

King Regards

Jame

 

[superkopite]

thanks guys

I will try these out when i get to work and let you know

King Regards

James

 

[broro183]

Hi,
Personally, I'd use an approach like the one suggested by JMB, but if
you have users who disable macros when opening files then another
(potentially v slow to implement but) possible approach is to use the
indirect function instead of direct links.

To do this you'd need a reference cell that provides the correct
directory & then convert all your direct formulae to indirect functions
which grab the value from the reference cell.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Guest]

Good point. Sometimes I put a message on the main sheet that macros must be
enabled to use the workbook properly. Or, he could put a message that macros
are disabled, and have the workbook open code change it to say macros are
enabled so the user will have some idea of what's going on.

I think one consideration with INDIRECT is if the OP makes changes to the
source workbooks (cut/paste or insert/delete rows), the links in the target
workbook will not automatically change to reflect the new cell addresses,
unlike using a direct link (assuming both the soure and target workbook are
open). If I used INDIRECT, I would consider leaving the direct links in my
copy, then maybe use a macro to change the formulae in the externally linked
cells to use INDIRECT and distribute that copy to the users.