Bossing the Text Import Wizard about

[Guest]

I need to import a series of comma delimited text files into Excel. The
following chunk of code (pilfered from the macro recorder so it's probably
more wordy than it needs to be) does what I want but it relies on the name
and path of the text file to be hardcoded in.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\admin\Desktop\Press Repor1.txt",
Destination _
:=Range("A2"))
.Name = "Press Repor1_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileTabDelimiter = True
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

What I need is for the user to be able to browse to the text file to be
imported, because it could be anywhere and called anything. Doing a File|open
and browsing to the text file means that the user would have to go through
the text import wizard, and I don't trust them to choose the right settings.

Can anyone provide a suggestion as to how I can allow the user to browse to
the text file and then for excel to get the text import settings from my code
rather than forcing the user to choose them? It's probably really simple but
I'm drawing a massive blank.

 

[Jim Rech]

You can prompt for a file like this:

Sub Demo()
Dim FName As Variant
FName = Application.GetOpenFilename("Text files(*.TXT),*.TXT")
If FName <> False Then
MsgBox FName
Else
MsgBox "User cancelled"
End If
End Sub


--
Jim Rech
Excel MVP
|I need to import a series of comma delimited text files into Excel. The
| following chunk of code (pilfered from the macro recorder so it's probably
| more wordy than it needs to be) does what I want but it relies on the name
| and path of the text file to be hardcoded in.
|
| With ActiveSheet.QueryTables.Add(Connection:= _
| "TEXT;C:\Documents and Settings\admin\Desktop\Press Repor1.txt",
| Destination _
| :=Range("A2"))
| .Name = "Press Repor1_2"
| .FieldNames = True
| .RowNumbers = False
| .FillAdjacentFormulas = False
| .PreserveFormatting = True
| .RefreshOnFileOpen = False
| .RefreshStyle = xlInsertDeleteCells
| .SavePassword = False
| .SaveData = True
| .AdjustColumnWidth = False
| .RefreshPeriod = 0
| .TextFilePromptOnRefresh = False
| .TextFilePlatform = 850
| .TextFileStartRow = 1
| .TextFileParseType = xlDelimited
| .TextFileTextQualifier = xlTextQualifierDoubleQuote
| .TextFileTabDelimiter = True
| .TextFileCommaDelimiter = True
| .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
| 1, 1, 1, 1, 1, 1, 1, 1, 1, _
| 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1,
| 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
| , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
| .TextFileTrailingMinusNumbers = True
| .Refresh BackgroundQuery:=False
| End With
|
| What I need is for the user to be able to browse to the text file to be
| imported, because it could be anywhere and called anything. Doing a
File|open
| and browsing to the text file means that the user would have to go through
| the text import wizard, and I don't trust them to choose the right
settings.
|
| Can anyone provide a suggestion as to how I can allow the user to browse
to
| the text file and then for excel to get the text import settings from my
code
| rather than forcing the user to choose them? It's probably really simple
but
| I'm drawing a massive blank.

 

[Kieran1028]

How would I use the sub Jim just wrote, and reference the user define
filename&path in the rest of my code?

Thanks,
Kiera