Open file Dialog box in Macro

[Joshua]

Ok, I'll do my best to explain what my problem is. I have
a macro that I run to import a text file, format it, sort
it and manipulate the workbook in a few other ways. My
problem comes in that the file's name is not always the
same so I don't want to hardcode the filename into it. I
want instead to have a "Browse" dialog box pop up and the
user be able to select a file to open for the macro to
use. Any help is greatly appreciated. Let me know if
there's something I can help clear up for you.

Joshua Dunn

 

[Tom Ogilvy]

fname = Application.GetOpenFilename()

workbooks.openText Filename:=fname, . . .

 

[Jim Rech]

Have a look at:

Application.GetOpenFilename

--
Jim Rech
Excel MVP
| Ok, I'll do my best to explain what my problem is. I have
| a macro that I run to import a text file, format it, sort
| it and manipulate the workbook in a few other ways. My
| problem comes in that the file's name is not always the
| same so I don't want to hardcode the filename into it. I
| want instead to have a "Browse" dialog box pop up and the
| user be able to select a file to open for the macro to
| use. Any help is greatly appreciated. Let me know if
| there's something I can help clear up for you.
|
| Joshua Dunn

 

[Bob Phillips]

Hi Joshua,

Help has a great example

fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
workbooks.Open filename:= fileToOpen
End If
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Joshua]

Thanks Guys,
That works. Could you tell me how to change the starting
directory in the Open dialog box? I want it to start in a
Network Share directory rather than in the default "My
Documents" folder.

Thanks again,
Joshua Dunn

 

[Bob Phillips]

Joshua,

Precede the GetFileOpen with

ChDrive "C:\"
ChDir "C:\MyTest"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

And if the network share is not mapped to a drive letter you could use:

Private Declare Function SetCurrentDirectoryA Lib "kernel32" _
(ByVal lpPathName As String) As Long

Public Sub bSetUNCPath(ByVal szPathToSet As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPathToSet)
End Sub


Then you would put in your code

bRes = bSetUNCPath("\\ComputerName\Directory")

Make sure you put the Declare statement at the top of the general module
where you will use the code.

Code originally posted by Rob Bovey.