Worksheet.opentext....???

[carlos_ray86]

Is there any way I can open a text file in the workbook that the
macros is running in. Everytime I try with my code it automatically
opens a new workbook. I have looked at other comments for other people
but none of them work because I have a datatype and fieldinfo.

This is my code to open a txt file

Dim UF As Variant
Dim SL As Variant
UF = Application.GetOpenFilename(FileFilter:="TXT Files(*.txt),*.txt",
Title:="log")

Workbooks.OpenText Filename:=UF, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2))

 

[Dave Peterson]

If you record a macro when you open one of the .txt files, won't you see the
info that you need for your code?

 

[Tom Ogilvy]

Opentext places the textfile in a new workbook. It is trivial to then copy
it to your existing workbook - that is the method I use.

Alternately you can use querytable to do it directly. This is an old post
that illustrates:
-------------<old post>-----------------

Sub TestGetTxtFile()


'


' TestGetTxtFile Macro


' Macro recorded 10/7/2003 by Authorized User


'


With ActiveSheet.QueryTables.Add(Connection:= _


"TEXT;C:\NWACCESS\DATA\TIR Updates\test.txt",
Destination:=Range("A1"))
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 2, 1, 1, 2, 1)
.Refresh BackgroundQuery:=False
End With
End Sub


So:


You're using the QueryTables collection object. Go to VBA help and type
that into the Answer Wizard box and read all about it.


If your text file is always saved as a certain name - "MyData mmddyy.txt"
(where mmddyy is a date identifier) into the same folder, then you can code
your file path and name to get the file without using a dialogue box.


Using the .TextFileColumnDataTypes = Array(1, 1, 2, 1, 1, 2, 1) line, you
can specify if any columns are general, text, or numeric. I just used
General (1) and Text (2).


Before the End Sub, insert code to SaveAs with a file path and name, and you
should be good to go.

-----------<End Old Post>-----------

 

[Ron de Bruin]

Or use
http://www.cpearson.com/excel/imptext.htm

 

[Tom Ogilvy]

Ron,
That is a good reference for a delimited file, but in his sample code he
showed

xlFixedWidth

So not sure it is applicable here.

 

[Ron de Bruin]

Correct Tom

I am building a page on this moment to import a lot of txt files and use Chip's ImportTextFile
in the loop in one of the examples.
Testing opentext also but I think I go for the QueryTables because it is much faster
if you want to import a lot of txt files

Any thoughts ?

 

[Ron de Bruin]

Here it is
http://www.rondebruin.nl/txtcsv.htm