MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Importing a text file using VBA

Posted by Ben O. on August 15, 2001 11:57 AM

I need to autmate the process of importing reports into Excel as much as possible. I recorded a macro while importing a sample report into Excel using Get External Data > Import Text File. This is what my code looks like:

With Selection.QueryTable
.Connection = _
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With

I tried to replace this line:

.Connection = "TEXT;C:\WINDOWS\Desktop\Sample.txt"

With this:

.Connection = Chr(34) & "TEXT;" & Application.GetOpenFilename & Chr(34)

The "Open" dialog box opens and lets me choose a file, but once I press Open I get a run-time error (1004: Application-defined or Object-defined error). I'm thinking it might be because GetOpenFilename doesn't return the file's path. Could that be the problem? If so, how can I get around it?

I'd appreciate any help,


Posted by Russell Hauf on August 15, 2001 4:47 PM

Try it without the chr(34)'s. Let me know if it doesn't work (feel free to email me).