Open dialog in macro
 

I have this macro that is importing a text file and manipulating the data afterwards. However, the file name is not the same every time that they run the macro. The following code is what I have, however, I would like to have an Open dialog box come up so they can choose the file and then it will import the file and do everything else. Any suggestions would be great. Thanks in advance. Matt. The first line is where I would like to have an Open dialog box because it won't always be the same name

Workbooks.OpenText Filename:="D:\JAN2004_MOMHP0000_135608.TXT", Origin:=xlWindows,
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False,
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 4), Array(9, 4), Array(10
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1))

Open dialog in macro
 

Matt

Use the GetOpenFileName method of the Application object.

Sub OpenTxt()

Dim FName As String

FName = Application.GetOpenFilename(",*.txt")

If Not FName Then
Workbooks.OpenText Filename:=FName, etc...
End If


End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.



"Matt" wrote in message
...
I have this macro that is importing a text file and manipulating the data
afterwards. However, the file name is not the same every time that they run
the macro. The following code is what I have, however, I would like to have
an Open dialog box come up so they can choose the file and then it will
import the file and do everything else. Any suggestions would be great.
Thanks in advance. Matt. The first line is where I would like to have an
Open dialog box because it won't always be the same name.

Workbooks.OpenText Filename:="D:\JAN2004_MOMHP0000_135608.TXT",
Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 4), Array(9, 4), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1),
Array(15, 1))