'
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