Setting up Directory for GetOpenFilename

[bigck2]

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:

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:

'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

 

[shg]

Use ChDrive, then ChDir

 

[bigck2]

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:

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:

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'.

 

[shg]

See http://www.mrexcel.com/forum/excel-...ons-setting-default-unc-path.html#post3239465

 

[bigck2]

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]

I don't, and I don't have Excel installed 64-bit to test if I did.

 

[Jaafar Tribak]

Try this declaration for 64 bits :

[COLOR=#000000]Declare [B]PtrSafe[/B] Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long[/COLOR]

 

[shg]

Thanks, Jaafar.

 

[Jaafar Tribak]

Thanks, Jaafar.

You are welcome