Set Location and Filename to replace GetOpenFilename

[Guest]

I currently have code that when the macro is executed, allows the user to
choose the file needed to write data to. I wrote it this way as I will be
sending the file out to multiple locations and cannot control where the user
may place the file. Here is the code that allows the user to choose the
location and filename:

Do
fName = Application.GetOpenFilename
Loop Until fName <> False
Set BonusFile = Workbooks.Open(Filename:=fName)

What I would like to do is have the user, on first use of the macro, go to
the location where they initially saved the file and open it, but then have a
message box open and ask the user if they would like to save this path and
filename as the default location and filename.
If they answer yes, the macro would save the specific path to the file and
then specific filename in the macro and have the macro use this path and
filename in the future without the user having to open the file manually
again.
There would probably need to be a trap so that if the file is renamed or
moved after the location is created, the macro would go back to letting the
user once again choose the location and file, but when chosen, would then ask
if the user wants to set the path and filename again.

Can you help with this?

 

[Tom Ogilvy]

create a defined name

insert=>Name=>Define

in the workbook that contains the code.

put the name in that. Then in your code, check if there is a string stored
there that is a valid filename. If so, use that file. If not, then prompt.

After you update the string in the defined name/range, you will need to save
the file.

You can make this name visible = false

 

[Guest]

Yes...that sounds like exactly what I want to do...but as a new user, I'm not
sure of the code involved.....Can you be more specific with the code
required?
Thanks again!

 

[Tom Ogilvy]

Dim sStr as String
Dim BonusFile as Workbook
sStr = ""
On Error Resume Next
sStr = Evaluate(thisworkbook.Names("ABC").RefersTo)
On Error goto 0
if sStr = "" then
Do
fName = Application.GetOpenFilename
Loop Until fName <> False
Set BonusFile = Workbooks.Open(Filename:=fName)
ThisWorkbook.Names.Add Name:="ABC", _
RefersTo:="=""" & BonusFile.FullName & """", _
Visible = False
else
set Bonusfile = Workbooks.Open(sStr)
End if