susanbo
asked on
How can I keep a cell/file reference in a formula from looking for the file on my computer, rather than searching on the drive it is on?
For example,, when I point to this file on my computer for the lookup, it works. When I email the file to my client, it will not look on their computer for the file. Can I set it to look in the same folder as the file that they are working on?
=IF(J5=2010,HLOOKUP(J9,'C: \Users\Sus an\Documen ts\Kaiser Supply Demand Model 2010\PCAPT\[Gardena Schedules DEMO.xlsx .... etc.....
=IF(J5=2010,HLOOKUP(J9,'C:
I don't think file references in cells are relative references. There are just too many possibilities. The way I deal with this is to change on reference and then replace all the rest. It goes fairly simply. ... Thinkpads_User
ASKER
Whenever I make a change to the file, and email it back, the references look for my computer. I have to physically go to the client and change all formulas, even the ones I haven't altered. And there are hundreds! I don't see why this is happening... I've been building worksheets and databases for clients for years.
Could there be a setting in Excel?
Could there be a setting in Excel?
I think it could be relative vs. absolute links issue.
http://support.microsoft.com/kb/328440
http://support.microsoft.com/kb/328440
You could try the following (in the nature of an experiment):
Put the file reference in a cell (C:\Users\Susan\Documents\ Kaiser Supply Demand Model 2010\PCAPT\[Gardena Schedules DEMO.xlsx). Now see if you can build formulas based only on the cell. Then the client may only have to change the one cell.
... Thinkpads_User
Put the file reference in a cell (C:\Users\Susan\Documents\
... Thinkpads_User
The problem is its looking for the file in the Documents folder (presumably) of the current user. If you don't have the same usernames, then that could be the problem.
=IF(J5=2010,HLOOKUP(J9,'C: \Users\Sus an\Documen ts\Kaiser Supply Demand Model 2010\PCAPT\[Gardena Schedules DEMO.xlsx .... etc.....
Suggest you create a folder, "C:\Project" and put the files there, then change your links from C:\Users\Susan\Documents to C:\Project
Test, to make sure it works, then send the file to your client, ensuring that your client moves those documents to those files as well.
Another, perhaps better? alternative would be to have the client change links of the files you send to his/her source directory - or provide a macro to change the links from old links to new links:
a simple oldFolder= newFolder= and a loop through all the workbook's links substituting new for old would do the trick.
If you'd like to pursue this last suggest, let me know and I can assist.
Good luck!
Dave
=IF(J5=2010,HLOOKUP(J9,'C:
Suggest you create a folder, "C:\Project" and put the files there, then change your links from C:\Users\Susan\Documents to C:\Project
Test, to make sure it works, then send the file to your client, ensuring that your client moves those documents to those files as well.
Another, perhaps better? alternative would be to have the client change links of the files you send to his/her source directory - or provide a macro to change the links from old links to new links:
a simple oldFolder= newFolder= and a loop through all the workbook's links substituting new for old would do the trick.
If you'd like to pursue this last suggest, let me know and I can assist.
Good luck!
Dave
Susan, here's some code I currently use that can help you automatically change links to the user's Documents folder (rather than yours) - assuming this is the problem...
sub testChange()
dim oldPath as string, newPath as string
oldPath = "C:\Users\Susan\Documents"
newPath = "C:\Users\" & Environ("UserName") & "\Documents"
if changeLinkPathOnly(activew orkbook,ol dPath,newP ath) then
msgbox "successful link change completed"
else
msgbox "unsuccessful change of links"
end if
end sub
Function changeLink(myWkb As Workbook, oldPath As String, newpath As String) As Boolean
Dim aLinks As Variant
Dim oldLink As String
Dim newLink As String
Dim i As Long
on error goto badLinkUpdate
aLinks = myWkb.LinkSources(xlExcelL inks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
oldLink = aLinks(i)
newLink = Replace(oldLink, oldPath & "\", newpath & "\")
'Debug.Print "Changing Old: " & oldLink
'Debug.Print "--> New: " & newLink
If oldLink <> newLink Then
myWkb.changeLink Name:=oldLink, newName:=newLink
End If
Next i
End If
changeLink = True
exit Function
badLinkUpdate:
changeLink = False
End Function
sub testChange()
dim oldPath as string, newPath as string
oldPath = "C:\Users\Susan\Documents"
newPath = "C:\Users\" & Environ("UserName") & "\Documents"
if changeLinkPathOnly(activew
msgbox "successful link change completed"
else
msgbox "unsuccessful change of links"
end if
end sub
Function changeLink(myWkb As Workbook, oldPath As String, newpath As String) As Boolean
Dim aLinks As Variant
Dim oldLink As String
Dim newLink As String
Dim i As Long
on error goto badLinkUpdate
aLinks = myWkb.LinkSources(xlExcelL
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
oldLink = aLinks(i)
newLink = Replace(oldLink, oldPath & "\", newpath & "\")
'Debug.Print "Changing Old: " & oldLink
'Debug.Print "--> New: " & newLink
If oldLink <> newLink Then
myWkb.changeLink Name:=oldLink, newName:=newLink
End If
Next i
End If
changeLink = True
exit Function
badLinkUpdate:
changeLink = False
End Function
ASKER
Dave-
Wow, thank you! I could have never come up with that. So do I make that a macro that I can run once the file is on the client's server? And I have the worksheets protected, so I assume I would need to unprotect before I run that?
Thanks so much,
Susan
Wow, thank you! I could have never come up with that. So do I make that a macro that I can run once the file is on the client's server? And I have the worksheets protected, so I assume I would need to unprotect before I run that?
Thanks so much,
Susan
Alternatively, you could create a username on your machine, with your client's username, then link everything up there. No macro's needed, just move the files and manually link it all up, protect and save.
You would need to unprotect the sheet, run the macro, then re-protect the sheet.
I would put some code in the Workbook_Open() event to check an invisible named range variable (e.g., "Installed_User" having the value of the User that its installed on). If the Installed_User is not what you find in the Environ("Username") property, then change the links.
Here's a quick setup for you.
The code:
In ThisWorkbook, we have code to prevent user intervention by disabling the interrupt key, then we run the checkInstallation function and if unsuccessful, the file closes itself out.
In a public Module, we have the checkInstallation function which looks at the named range "InstalledUser" and compares that name with the current username based on the Environment variable, UserName. There's an initialSetup() macro to initialize the "InstalledUser" to your username.
Once the app determines the current user is different from installed user (e.g., different from you - be sure to change oldPath variable to your path, if different), then the app unprotects the workbook, changes links, and protects the workbook back (you need to supply the specific code steps you use to protect/unprotect, as any code I put in there may not suit your needs and of course your password). If it doesn't make it through for some reason, the message pops up alerting the client where the problem was, and FALSE is propogated back to the original call from the Workbook_Open event, at which point the user is notified that its aborting and the file closes itself out.
Let me know if you can take it from here :)
See attached demo file.
Enjoy!
Dave
deployFile.xlsm
You would need to unprotect the sheet, run the macro, then re-protect the sheet.
I would put some code in the Workbook_Open() event to check an invisible named range variable (e.g., "Installed_User" having the value of the User that its installed on). If the Installed_User is not what you find in the Environ("Username") property, then change the links.
Here's a quick setup for you.
The code:
In ThisWorkbook, we have code to prevent user intervention by disabling the interrupt key, then we run the checkInstallation function and if unsuccessful, the file closes itself out.
Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled 'disable any user intervention while the next steps are run
If Not checkInstallation Then
'code to close the file/abort installation
MsgBox "This file will now be closed", vbCritical, "Please notify Susan..."
Application.DisplayAlerts = False
ThisWorkbook.Close savechanges:=False
End If
Application.EnableCancelKey = xlInterrupt
End Sub
In a public Module, we have the checkInstallation function which looks at the named range "InstalledUser" and compares that name with the current username based on the Environment variable, UserName. There's an initialSetup() macro to initialize the "InstalledUser" to your username.
Once the app determines the current user is different from installed user (e.g., different from you - be sure to change oldPath variable to your path, if different), then the app unprotects the workbook, changes links, and protects the workbook back (you need to supply the specific code steps you use to protect/unprotect, as any code I put in there may not suit your needs and of course your password). If it doesn't make it through for some reason, the message pops up alerting the client where the problem was, and FALSE is propogated back to the original call from the Workbook_Open event, at which point the user is notified that its aborting and the file closes itself out.
Sub initialSetup() 'Run this once, to set up the defined name for the workbook
If Not createModifyName(ThisWorkbook, "InstalledUser", Environ("UserName"), False) Then
MsgBox "can't create initial name, for some reason"
End If
End Sub
Public Function checkInstallation() As Boolean 'sub to check for "InstalledUser" Name, and establish new links if necessary
Dim myWkb As Workbook
Dim myName As Names
Dim wksUser As String
Dim currUser As String
Dim oldPath As String
Dim newPath As String
Set myWkb = ThisWorkbook
oldPath = "C:\Users\Susan\Documents"
newPath = "C:\Users\" & currUser & "\Documents"
currUser = Environ("UserName")
On Error Resume Next
wksUser = Right(myWkb.Names("InstalledUser").Value, Len(myWkb.Names("InstalledUser").Value) - 1)
If Err.Number <> 0 Then
checkInstallation = False
MsgBox "Unable to complete installation, please notify Susan...", vbCritical, "Error in checkInstallation() function"
Exit Function
End If
On Error GoTo 0
'if we make it here, then we can proceed with resetting links
If currUser <> wksUser Then
Call unProtectWorkbook
If Not changeLink(myWkb, oldPath, newPath) Then
checkInstallation = False
MsgBox "Unable to complete installation, please notify Susan...", vbCritical, "Error 2 in checkInstallation() function"
Exit Function
Else
Call createModifyName(ThisWorkbook, "InstalledUser", Environ("UserName"), False)
End If
Call ProtectWorkbook
End If
checkInstallation = True
End Function
Sub unProtectWorkbook()
'your code to unprotect the workbook goes here
End Sub
Sub ProtectWorkbook()
'your code to protect the workbook goes here
End Sub
Function createModifyName(myWkb As Workbook, myName As String, myValue As String, myVisible As Boolean) As Boolean
On Error Resume Next
myWkb.Names.Add Name:=myName, RefersTo:=myValue, Visible:=myVisible
If Err.Number <> 0 Then
MsgBox "Unable to complete installation, please notify Susan...", vbCritical, "Error 3 in checkInstallation() function"
createModifyName = False
Exit Function
End If
On Error GoTo 0
createModifyName = True
End Function
Function changeLink(myWkb As Workbook, oldPath As String, newPath As String) As Boolean
Dim aLinks As Variant
Dim oldLink As String
Dim newLink As String
Dim i As Long
On Error GoTo badLinkUpdate
aLinks = myWkb.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
oldLink = aLinks(i)
newLink = Replace(oldLink, oldPath & "\", newPath & "\")
If oldLink <> newLink Then
myWkb.changeLink Name:=oldLink, newName:=newLink
End If
Next i
End If
changeLink = True
Exit Function
badLinkUpdate:
changeLink = False
End Function
Let me know if you can take it from here :)
See attached demo file.
Enjoy!
Dave
deployFile.xlsm
ASKER
Dave,
Thanks so much but this is beyond me. I have no idea how to run that code. I tried changing one formula only to have it revert back as soon as I moved away from that cell. As an example, I had this:
=IF(J5=2013,HLOOKUP(J9,'C: \Users\Sus an\AppData \Local\Mic rosoft\Win dows\Tempo rary Internet Files\Content.Outlook\Z47J VBWF\[Gard ena Schedules.xlsx]GRD Schedule 2013'!$E$1:$JE$3,3,FALSE), IF(J5=2011 ,HLOOKUP(J 9,'C:\User s\Susan\Ap pData\Loca l\Microsof t\Windows\ Temporary Internet Files\Content.Outlook\Z47J VBWF\[Gard ena Schedules.xlsx]GRD Schedule 2011'!$E$1:$JE$3,3,FALSE), IF(J5=2012 ,HLOOKUP(J 9,'C:\User s\Susan\Ap pData\Loca l\Microsof t\Windows\ Temporary Internet Files\Content.Outlook\Z47J VBWF\[Gard ena Schedules.xlsx]GRD Schedule 2012'!$E$1:$JE$3,3,FALSE)) ))
So I went in and deleted all the folder references and just left [Gardena Schedules.xlsx] etc...
As soon as I copied that over it reverted all of them back to looking in temporary files. Why is that? They are on my C drive. On a side note, my client will be storing the files on a server, not a local drive.
Sorry this is such a mess - I really appreciate all the code you sent me but I have no idea how to run that. Thanks again for all your help!
-Susan
Thanks so much but this is beyond me. I have no idea how to run that code. I tried changing one formula only to have it revert back as soon as I moved away from that cell. As an example, I had this:
=IF(J5=2013,HLOOKUP(J9,'C:
So I went in and deleted all the folder references and just left [Gardena Schedules.xlsx] etc...
As soon as I copied that over it reverted all of them back to looking in temporary files. Why is that? They are on my C drive. On a side note, my client will be storing the files on a server, not a local drive.
Sorry this is such a mess - I really appreciate all the code you sent me but I have no idea how to run that. Thanks again for all your help!
-Susan
Ok - I then revert back to my previous comment, or one similar... Create a similar directory structure as your client and save. When you client opens, your client will be looking at the same location. The other alternative is to have your client link to your clients files on your client's server.
Either there's manual intervention, or you have to attack it with macros :)
Dave
Either there's manual intervention, or you have to attack it with macros :)
Dave
>>So I went in and deleted all the folder references and just left [Gardena Schedules.xlsx] etc...
As soon as I copied that over it reverted all of them back to looking in temporary files. Why is that?
That's because you have to change LINKS to the file locations where they reside at the time of changing links.
Good luck - I'll check back in the AM.
Dave
As soon as I copied that over it reverted all of them back to looking in temporary files. Why is that?
That's because you have to change LINKS to the file locations where they reside at the time of changing links.
Good luck - I'll check back in the AM.
Dave
ASKER
Dave,
Thank you so much. I think as long as the referenced file is open, the formulas find it ok. I'll have to test it out on my client's server... but I have one last question. Is there a way to have a second file automatically open when the first one is opened, without the user having to remember to do it?
Thanks again,
Susan
Thank you so much. I think as long as the referenced file is open, the formulas find it ok. I'll have to test it out on my client's server... but I have one last question. Is there a way to have a second file automatically open when the first one is opened, without the user having to remember to do it?
Thanks again,
Susan
Sure - you can have a file opened based on an EVENT. If I understand you correctly, you'd like to have a second file opened when the client opens the file you send them.
Put this code in your primary file's Workbook_Open() event. It goes in the ThisWorkbook code module (Hit Alt-F11 to get to the VBA Editor, look to the left - usually - and expand objects, then search for ThisWorkbook).
See attached. Hit Alt-F11 and look at the ThisWorkbook codepage to see the code, here.
Dave
OpenSecondFile-r1.xls
Put this code in your primary file's Workbook_Open() event. It goes in the ThisWorkbook code module (Hit Alt-F11 to get to the VBA Editor, look to the left - usually - and expand objects, then search for ThisWorkbook).
Private Sub Workbook_Open()
Dim secondWkb As Workbook
myfile = ActiveWorkbook.Path & "\test2.xlsm" 'put the path and name of the second file to be opened, here - e.g., myFile = "\\Server\Path\File.xlsm", or myFile = "C:\Users\Desktop\file.xlsm"
On Error Resume Next 'in case the next statement creates an error
Set secondWkb = Application.Workbooks.Open(Filename:=myfile, UpdateLinks:=3) 'UpdateLinks:=1 for user specify, UpdateLinks:=2 don't update links, UpdateLinks:=3 - to update links
If Err.Number = 0 Then
'successful open of second workbook
Else
On Error GoTo 0
'error handling here
End If
End Sub
See attached. Hit Alt-F11 and look at the ThisWorkbook codepage to see the code, here.
Dave
OpenSecondFile-r1.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dave-
Seems to be working - thank you so much! I really appreciate your time on this.
-Susan
Seems to be working - thank you so much! I really appreciate your time on this.
-Susan