Point Open File Window to a specific directory 4

[prismtx]

I have an Excel macro that runs when a spreadsheet opens and I want it to give an open file message box that is pointing to a network drive where some csv files are stored. My problem is that the open box defaults to the last folder opened and I have not been able to change it to the network drive.

Here is what I have tried, but none are working:

 ChDir "//Hmm04\Shared\CSV\Daily Reports"
Filename = Application.GetOpenFilename("CSV Files (*.csv), *.csv")

InitDir = "//Hmm04\Shared\CSV\Daily Reports"
Filename = Application.GetOpenFilename("CSV Files (*.csv), *.csv")

ChDrive "//Hmm04\Shared"
ChDir "//Hmm04\Shared\CSV\Daily Reports"
Filename = Application.GetOpenFilename("CSV Files (*.csv), *.csv")

How can I get the Open file dialog to point to the network directtory?

 

[mscallisto]

have you tried
ChDir "\\Hmm04\Shared\CSV\Daily Reports

 

[prismtx]

Yes I tried ChDir "\\Hmm04\Shared\CSV\Daily Reports" [\b] and got the same results.

I have the network drive mapped to the letter "G" and can get it to work this way:

ChDir "G"
ChDir "CSV\Daily Reports"[\code]

But i don't know what the users will have the drive mapped to, or if they will even have it mapped.

Is there a way to make the network drive the current drive or directory without having it mapped to a letter?

 

[prismtx]

Sorry, I hit submit instead of preview. This is easier to read:

Yes I tried ChDir "\\Hmm04\Shared\CSV\Daily Reports" and got the same results.

I have the network drive mapped to the letter "G" and can get it to work this way:

ChDir "G"
ChDir "CSV\Daily Reports"

But i don't know what the users will have the drive mapped to, or if they will even have it mapped.

Is there a way to make the network drive the current drive or directory without having it mapped to a letter?

 

[mscallisto]

Try direct addressing

ChDir "\\123.45.67.890\shareName"
Filename = Application.GetOpenFilename("CSV Files (*.xls)")
MsgBox Filename

 

[prismtx]

I tried the ChDir "\\123.45.67.890\shareName" as you suggested, but the open file box still defaulted to the last directory that I had used.

 

[mscallisto]

But i don't know what the users will have the drive mapped to, or if they will even have it mapped.

ChDir "\\123.45.67.890\shareName" should solve that problem

you may still need to point to the correct folder

ChDir "CSV\Daily Reports"

 

[prismtx]

I know that the ChDir should work, but for some reason it isn't. I have tried ChDir "\\123.45.10.66\shared\CSV" and ChDir "\\hmm04\shared\CSV" but although it does not give any error, it still is opening to a directory on my C: drive.

 

[rocheey]

Its a 'feature' (spell "bug") in the way VB/A handles "CurDir". From distant memory, I remember solving it by using an API call to set the 'current' directory before making the Common Dialog call. Sorry that I dont remember the specific API ca;;, but I rememember I didnt have to hunt far to find it....

 

[PHV]

You may use an API call:

Private Declare Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long
...
SetCurrentDirectory "\\Hmm04\Shared\CSV\Daily Reports"
Filename = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mscallisto]

Thanks to both

 

[prismtx]

I was out for a few days, but just got back and tried PHV's solution. Worked like a charm! Problem solved. Thanks for the insight.