Putting path and filename in a macro

[JC]

I have a macro that I use that references several files
on a network. I'd like to use this spreadsheet as a
template and have the path and filename automatically
change to whatever the file is called. Example. I have
a file called \\FileShare\Excel\example.xls and I
reference that path in a macro. Now I want to open that
file and do a 'save as' and change the filename to
\\FileShare\Excel\example2.xls. Now I'd like to have it
automatically reflect the name change in the macro
without having to go into the VBA editor and manually
change the filename. Does anyone know if this is
possible?

Hope that makes sense.

Thanks
JC

 

[Harald Staff]

Try inserting into and running from a saved workbook:

Sub Paths()
MsgBox ThisWorkbook.Name
MsgBox ThisWorkbook.Path
MsgBox ThisWorkbook.FullName
End Sub

 

[JC]

Cool. How about if I am referencing that same file in
another worksheet? What I am doing is refreshing links
to all files (about 15 of them) from a macro that runs in
another file. If I change the name of the file I have to
manually change it in VBA. Is there a way to do this
dynamically?

Thanks,
JC