Opening File Open Dialog Box In A Specific Folder

markkramer

Board Regular
Joined
May 8, 2002
Messages
162
Hi.

I'm trying to develop a macro that brings up the File Open dialog box in a specific folder. I found the following code in several places on Mr. Excel but found it still opened up the dialog box in my C:\TEMP folder. Any ideas on how to get it to open in the U:\PAR folder?

Thanks for you help!

Mark

Dim DIRECTORY As String
Dim fileToOpen As Boolean
DIRECTORY = "u:\par\"
ChDir DIRECTORY
fileToOpen = Application.GetOpenFilename("Excel Files (u:\par\*.xls), *.xls")
Workbooks.Open fileToOpen
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> tmpFile

    ChDrive "U:\" <SPAN style="color:#007F00">'change the drive also</SPAN>
    ChDir "U:\PAR"

    DoEvents
    
    tmpFile = Application.GetOpenFilename(FileFilter:="Microsoft Excel Files (*.xls),*.xls")
    
    <SPAN style="color:#00007F">If</SPAN> tmpFile <> <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> Workbooks.<SPAN style="color:#00007F">Open</SPAN> FileName:=tmpFile
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Just one thing, since the ChDrive will only use the first letter of the string that its passed, you could just use

ChDrive "U"

altough no "real" difference in there...
 
Upvote 0
Hi Guys

I need help modifying this to fit what I need to do.

Currently I am opening a dat file (txt based data file) and am using these parameters to load it into an excel sheet.

Workbooks.OpenText Filename:= _
"c:\data.DAT", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True

I would like to modify the above macro to give me a choice to open the dat file (that I can do), but using these parameters when opening the file (that I can't do).

Thanks
John
 
Upvote 0
In a very simple manner, DoEvents passes the "focus" of the processor back to the OS allowing it to clear anything in it's cue. I've found that sometimes it is necessary to add this line after changing drives/directories so that the process actually completes. You could probably remove it from this line of code, and it would still work correctly, but I just put it in there to be safe.
 
Upvote 0
Hi TommyGun,

Since its your macro up there, you might be able to answer. Any ideas how to change it to allow me to specify those parameters above?

My own macro from the macro recorder works fine, but I have to preset the location and name of the file. I would prefer to browse to open.

Any ideas?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top