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

Try inserting into and running from a saved workbook:

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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"JC" wrote in message
...
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

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
-----Original Message-----
Try inserting into and running from a saved workbook:

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

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"JC" wrote in
message
...
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


.