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.:confused:

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. :doh: 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! :yes

[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]

:rotlaugh:

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! :yes

[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? :*))
:beerchug:

[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.:dunno

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