Opening text file in Excel through macro
 

I copied the data you gave into a .txt file, then imported it into Excel
with the macro recorder turned on. I go the following:



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.



HTH

Ed

"Mike" wrote in message
...
Hello,

I want to create a macro that would open my "mytext.txt"
file to excel worksheet without going through the prompt
that needs some formatting, like comma delimited, etc. My
text file will be look like this before I open it to excel:

2,70037,"8832322",25914,"2128123","ABC COMPANY","030628"
879,307,"100101118141",3522,"2227711","BERRY INC","030628"
.
.
.
etc.

Can anyone provide me some ideas or tips.

Thanks in advance for your help.
Mike