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