I have a macro that (among other things) opens makes some changes and renames a template file. The template file contains a vlookup that references a sheet that is updated (and resaved) every week, so it has to automatically update with the new filename. All of these files are saved on a server.
If I have the vlookup reference using the full server name ie
[/COLOR]
the link doesn't keep up with the resaved filename.
If I have the vlookup reference using the name my computer has set as the file name ie
[/COLOR]
the link updates on filename changes but when the macro is run part of the path is left out, rendering the link useless.
The applicable code for the macro is:
Hope that makes sense, any help would be appreciated (I'm not exactly a VBA pro). Thanks!
If I have the vlookup reference using the full server name ie
Code:
=vlookup(example, '[COLOR=black]\\SERVER1\Folder1...[/COLOR]Folder2[COLOR=black]\[Master.xls]Example Sheet'!A1,1)
the link doesn't keep up with the resaved filename.
If I have the vlookup reference using the name my computer has set as the file name ie
Code:
=vlookup(example, '[COLOR=black]P:\[/COLOR]Folder2[COLOR=black]\[Master.xls]Example Sheet'!A1,1)
the link updates on filename changes but when the macro is run part of the path is left out, rendering the link useless.
The applicable code for the macro is:
Code:
Dim LongPath As String
LongPath = [URL="file://\\SERVER1\Folder1...Folder2\"]\\SERVER1\Folder1...Folder2[COLOR=black]\[/COLOR][/URL]
...
Workbooks.Open Filename:=LongPath & "Template X Form.xls"
Hope that makes sense, any help would be appreciated (I'm not exactly a VBA pro). Thanks!