Excel macro text import file selection

[Ennes]

Hello all,
I have this macro to import a text file in to Excel. How can I modify it so it will ask me which file to import rather than the "dataone.txt". I would also like to rename the worksheet to the name of the text file (ex. dataone).

Thanks in advance.

Here it is:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/13/2003 by XXX
'

'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;c:\dataone.txt", Destination:=Range _
("A1&quot)
.Name = "dataone"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = "="
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[andrew299]

Try this on for size
Sub opentextfile()
Dim mybook
Set mybook = ThisWorkbook
Application.DisplayAlerts = False

'
filetoopen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt&quot
If filetoopen <> False Then

Workbooks.OpenText Filename:=filetoopen, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlNone, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=false, Space:=true, Other:=false, FieldInfo:=Array(1, 1)
Range(&quot;A1&quot.Select

End If
Application.DisplayAlerts = True
Cells.Select
Selection.Copy
mybook.Activate
Cells(1, 1).Select
ActiveSheet.Paste
'Workbooks(filetoopen).Close SaveChanges:=False
End Sub


Andrew299

 

[mmandk9]

Ennes,

Use the 'InputBox' function. The help pages can guide you through how to implement in your macro...

MM

 

[Ennes]

Worked beautifully.
Thanks