Hello,
I have a program where the user needs to select two files to import. One file will typically be somewhere on the user's local machine (the folder could be different from user to user) and the second file is on our company's shared drive 'J:\' in a specific folder.
To select the file I use the GetOpenFileName function. To try and make this process as easy as possible I have changed the current working directory using some code like this:
Then a little bit later in the program the user will need to select another file and I explicitly state the J drive folder to look in:
The issue I'm having is that after doing this second import, when the user starts over to run the program again it automatically redirects to that J drive folder.
I don't know where on the machine the file may be, but what I want to do is have the dialogue box open to the path of the current file. That is why I specify the 'ChDir Tool.Path'. It seems like VBA ignores that part of the code if I run the program again.
Please let me know if you have any thoughts?
Thanks,
Chris
I have a program where the user needs to select two files to import. One file will typically be somewhere on the user's local machine (the folder could be different from user to user) and the second file is on our company's shared drive 'J:\' in a specific folder.
To select the file I use the GetOpenFileName function. To try and make this process as easy as possible I have changed the current working directory using some code like this:
Code:
Set Tool = ActiveWorkbook
'Set up list of file filters
Finfo = "All Files (*.*),*.*"
'Display *.* by default
FilterIndex = 1
'Set the dialog box caption
Title = "Select a File to Import"
'Change Working Directory
ChDir Tool.Path
'Get the filename
FileNAME = Application.GetOpenFilename(Finfo, FilterIndex, Title)
Then a little bit later in the program the user will need to select another file and I explicitly state the J drive folder to look in:
Code:
'Set up list of file filters
Finfo = "All Files (*.*),*.*"
'Display *.* by default
FilterIndex = 1
'Automatically select working directory with Old Rolling Forecasts
ChDir "J:\Multifamily\Rolling Forecast\2016\2016 02 Feb Rolling Forecast"
'Set the dialog box caption
Title = "Select a your most recent Rolling Forecast (.xlsm) file to Import"
'Get the filename
FileNAME = Application.GetOpenFilename(Finfo, FilterIndex, Title)
The issue I'm having is that after doing this second import, when the user starts over to run the program again it automatically redirects to that J drive folder.
I don't know where on the machine the file may be, but what I want to do is have the dialogue box open to the path of the current file. That is why I specify the 'ChDir Tool.Path'. It seems like VBA ignores that part of the code if I run the program again.
Please let me know if you have any thoughts?
Thanks,
Chris