Setting up Directory for GetOpenFilename

bigck2

Board Regular
Joined
Feb 20, 2014
Messages
146
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:

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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Use ChDrive, then ChDir
 

bigck2

Board Regular
Joined
Feb 20, 2014
Messages
146
Thanks for the suggestion. We use a flexspace / cloud environment so if I'm working with a file on my desktop and I run this code:

Code:
MsgBox ThisWorkbook.FullName

I get this result: "\\co-file\FolderRedirection$\chris\Desktop\Test - 2016 Rolling Forecast Tool - V3.0.xlsm"

I'm not sure what my drive would be in this context. Any thoughts?

I've been trying some variations of:

Code:
UName = LCase(Environ("UserName"))


ChDrive ("\\co-file\FolderRedirection$\" & UName)


When I run this last block of code I get a Run-time error '5' message: Invalid procedure call or argument.

I think ChDrive is expecting something like 'C' or 'J'.
 

bigck2

Board Regular
Joined
Feb 20, 2014
Messages
146

ADVERTISEMENT

Hey shg,

Thanks for the referral to that post. I tried to use their solution, but I'm not sure that code works on 64 bit systems. I made a reply on that thread. Do you know if there is another solution, or modification for 64 bit?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
I don't, and I don't have Excel installed 64-bit to test if I did.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try this declaration for 64 bits :
Code:
[COLOR=#000000]Declare [B]PtrSafe[/B] Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long[/COLOR]
 
  • Like
Reactions: shg

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Thanks, Jaafar.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,560
Messages
5,596,842
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top