Software

Ask something
Discussion Locked

Placing Open dialog box in Excel macro

0 helpful
6 replies

by msoldan

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an Excel macro that imports a txt file and then manipulates the data. The file name is not always the same, so I need to replace the filename in the code with code that will present the user with an Open dialog box so that they can choose the file. I'm not sure if it is possible, however, any ideas would be great. Thanks. Matt

6 replies

by dklippert

Here's some code. Use fileToOpen as the file name.

Sub ChooseFile()
fileToOpen = Application _
.GetOpenFilename("All Microsoft Excel Files (*.xls), *.xls")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If

End Sub

by dklippert

Try:

Sub ChooseFile()
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If

Workbooks.OpenText Filename:= fileToOpen, 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))
End Sub

by msoldan

This is the code that Excel spit out at me. I would like to change the part of the filename so that it will present an Open dialog box. I don't think that I can put that in and get my desired result. Thanks.

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))

This discussion is closed to new replies.