Macro Import Text File, File Name

[Bob S]

I recorded a Macro while using "Data/Get External Data/Import Tex
File".

The Macro is:
______________________________
Sub TestTextFileImport()
'
' TestTextFileImport Macro

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\My Documents\Test Text Import File.txt", Destination:
_
Range("A1"))
.Name = "Test Text Import File"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
________________________

The Macro works well and is fast, but if want to import another file
I have to edit the line:

"TEXT;D:\My Documents\Test Text Import File.txt", Destination:= _

by writing in the new files path name.


I would like to be able to write the name of the file I want to impor
in a worksheet cell and have the Macro use that as the file name fo
importing.

I defned a variable called "FileName" and set it to a Cell A2 on Sheet
which contained the same file name as in the Macro, (D:\M
Documents\Test Text Import File.txt).

I then inserted "FileName" where the files path was in the Macro. Th
changes were:
______________________________________
'
Dim FileName As Variant
'
FileName = Worksheets("Sheet2").Range("A2")

Sheets("Sheet1").Select

' With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;FileName", Destination:= _
Range("A1"))
_______________________________________

The rest of the Macro was the same.

When the Macro reached ".Refresh BackgroundQuery:=False" I got an erro
message that the file could not be found. (Remming out ".Refres
BackgroundQuery:=False" avoided the error, but the file didn't import
Dah!)

I also tried recording "Data/Get External Data/New Database Query". I
seemed slower that Text Import and didn't help me with using a fil
path on a Worksheet

I tried Pearson Software Consulting's macro "ImportTextFile", whic
works well but seems slow for my files. I guess wending thru 5,00
lines + of text would take some time.


Is there any way to import different files w/o editing the Macro?

Thanks in advance for your help,

Bob

 

[Ardus Petus]

'
Dim FileName As String'
FileName = Worksheets("Sheet2").Range("A2").Value

Sheets("Sheet1").Select

' With ActiveSheet.QueryTables.Add( _
Connection:= "TEXT;" & FileName, _
Destination:=Range("A1"))

HTH

 

[Bob S]

Ardus,
THANKS

WOW. It really does help to know what you are doing! It works Great.

I even added a separate Path and File Name, all by myself!

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & PathName & "\" & FileName, Destination:= _
Range("A1"))

Bob S