VBA Excel Set Path to Open File

[dprayner]

Hi People. I want to put the full path for the user to browse for a certain file. The path is always the same for this application. I can open the file dialog box (see code below), but the user has to find the folders manually. Does anyone know how I can modify my code to include the path I need? Thank you. DAVE

fileToOpen = Application.GetOpenFilename("Document Direct Files (*.rpt), *.rpt, Excel Files (*.xls), *.xls, All Files (*.*), *.*")

' Open File after user selects Filename Workbooks.OpenText filename:=fileToOpen, Origin:=437, StartRow:= _
1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(24, 1), Array(28 _
, 1), Array(40, 1), Array(54, 1), Array(67, 1), Array(80, 1), Array(93, 1), Array(103, 1)), _
TrailingMinusNumbers:=True

 

[CautionMP]

It looks like [tt]Application.GetOpenFilename()[/tt] will always return the My Documents folder of the current user. I tried monkeing with [tt]Application.DefaultFilePath[/tt] to no avail.

It looks like you may have to use the [tt]GetOpenFileName[/tt] function from [tt]COMMDLG.DLL[/tt] or whatever the corresponding function is in the [tt]Comdlg32.dll[/tt]. Here is a MSDN link for the usage How to Use the Windows OpenFile Dialog Box (1.x/2.0)

This will let you change the default directory

      '...
      '* Set up the default directory
       szCurDir$ = CurDir$ & Chr$(0)
      '...

It isn't pretty but I hope it helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[rmikesmith]

Dave,

You might try something like:

SaveCurrentPath = CurDir
ChDir "YourPathStringHere"
fileToOpen = Application.GetOpenFilename("Document Direct Files (*.rpt), *.rpt, Excel Files (*.xls), *.xls, All Files (*.*), *.*")
ChDir SaveCurrentPath

This will change the path that appears in the File Open dialog to your desired path then restore the current path to what is was.

Note: The ChDir function cannot change to a new drive; for that you need to use ChDrive.

Regards,
Mike