Open file Dialog box in Macro
 

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

Open file Dialog box in Macro
 

fname = Application.GetOpenFilename()

workbooks.openText Filename:=fname, . . .

--
Regards,
Tom Ogilvy

"Joshua" wrote in message
...
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

Open file Dialog box in Macro
 

Have a look at:

Application.GetOpenFilename

--
Jim Rech
Excel MVP
"Joshua" wrote in message
...
| 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

Open file Dialog box in Macro
 

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" wrote in message
...
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

Open file Dialog box in Macro
 

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

-----Original Message-----
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" wrote in
message
...
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


.

Open file Dialog box in Macro
 

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)

"Joshua" wrote in message
...
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

-----Original Message-----
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" wrote in
message
...
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


.

Open file Dialog box in Macro
 

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.

--
Regards,
Tom Ogilvy


"Joshua" wrote in message
...
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

-----Original Message-----
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" wrote in
message
...
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


.