Links don't work in Excel Macro

[kyleboyd]

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

=vlookup(example, '[COLOR=black]\\SERVER1\Folder1...[/COLOR]Folder2[COLOR=black]\[Master.xls]Example Sheet'!A1,1)

[/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

=vlookup(example, '[COLOR=black]P:\[/COLOR]Folder2[COLOR=black]\[Master.xls]Example Sheet'!A1,1)

[/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:

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!