GetOpenFilename with default directory

[Ken Puls]

Hey guys,

I've just run into an issue with GetOpenFilename, and I can't get past it as of yet...

I'm trying to open the window in a specific directory, but I want to save the path that the user intially has, so that I can reset it there after I'm done. (This file is stored where I don't really want my users fooling around.)

Here's what I've got so far:

Dim InitialPath As String, SourceWBPath as String
 'Read the current directory and hold it in memory
 InitialPath = CurDir
'Change the directory to where the file should be
     ChDir "O:\GL Import"
     On Error Resume Next
     SourceWBPath = Application.GetOpenFilename("Quickbooks Files (*.IIF),*.IIF")
'Prompt the user to open the file, (limiting the display to QuickBooks files,) and import it
     Workbooks.OpenText Filename:= _
         SourceWBPath, Origin:=437, StartRow _
         :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
         ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
         , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
         Array(3, 2), Array(4, 3), Array(5, 9), Array(6, 2), Array(7, 1), Array(8, 9), Array(9, 2), _
         Array(10, 9), Array(11, 9), Array(12, 9), Array(13, 9), Array(14, 9), Array(15, 9))
If Err.Number <> 0 Then
         'Means no file was opened
         MsgBox "Could not complete procedure!", vbOKOnly
         Exit Sub
     End If
     On Error GoTo 0
'Change the directory back to user's initial directory path
     ChDir InitialPath

For some reason, the ChDir command does not seem to be working.... or maybe it is. What I was expecting this to do was to change the path, so that when I hit File|Open (or use GetOpenFilename) it would be in the correct directory.

If anyone could shed some light on this, it would be appreciated!

[Ken Puls]

Got it!

For the record, didn't realize that ChDir will not change the drive, only the directory. Had to throw in a ChDrive as well. Key parts were:

ChDrive "O:\"
 ChDir "O:\GL Import"
{rest of code}
ChDrive Left(InitialPath, 3)
 ChDir InitialPath

[Zack Barresse]

Ha! I was just about to post that. Good thing I refreshed! Good job!

[Richie(UK)]

Hi Ken,

Been there, done that!

You don't actually have to try to shorten the string for ChDrive to work - it will automatically use only the first letter of the string, so we can just use:

Sub test()
    Dim InitialPath As String
InitialPath = CurDir
'do stuff with drives and dirs
ChDrive InitialPath
    ChDir InitialPath
End Sub

[Ken Puls]

I hate it when I post and come up with a solution within minutes. Always makes me feel like I didn't look hard enough!

[Ken Puls]

it will automatically use only the first letter of the string

Hey cool! Thanks Richie, I'm heading to update my code right now! (Maybe there was a reason to post it after all, eh? )

[Ken Puls]

Go figure this...

I got it running beautifully with Office 2003, deployed it to my 97 user and ran into runtime errors!

Who knew that 97 does not support the Origin:=437 in with GetOpenFilename, but rather must have it specified as Origin:=xlWindows.

And here I thought that numerical constants were the only way to go!