Macro Import Text File, File Name
 

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 we
______________________________________
'
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

--
Bob
-----------------------------------------------------------------------
Bob S's Profile: http://www.excelforum.com/member.php...fo&userid=3272
View this thread: http://www.excelforum.com/showthread.php?threadid=52558

Macro Import Text File, File Name
 

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

Sheets("Sheet1").Select

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

HTH
--
AP

_______________________________________

"Bob S" a écrit dans le
message de ...

I recorded a Macro while using "Data/Get External Data/Import Text
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 import
in a worksheet cell and have the Macro use that as the file name for
importing.

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

I then inserted "FileName" where the files path was in the Macro. The
changes we
______________________________________
'
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 error
message that the file could not be found. (Remming out ".Refresh
BackgroundQuery:=False" avoided the error, but the file didn't import.
Dah!)

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

I tried Pearson Software Consulting's macro "ImportTextFile", which
works well but seems slow for my files. I guess wending thru 5,000
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 S


--
Bob S
------------------------------------------------------------------------
Bob S's Profile:
http://www.excelforum.com/member.php...o&userid=32721
View this thread: http://www.excelforum.com/showthread...hreadid=525581

Macro Import Text File, File Name
 

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


--
Bob S
------------------------------------------------------------------------
Bob S's Profile: http://www.excelforum.com/member.php...o&userid=32721
View this thread: http://www.excelforum.com/showthread...hreadid=525581