Solved: Importing data file into spreadsheet

[jwise]

I have a "text file" created by an outside program. I want to import this file into a new worksheet in an existing workbook, and then update another worksheet with this data. Ultimately, this saves the keying of this data into Excel.

All parts of this program now work with one problem. This technique was selected by recording a macro when I manually imported this data. I now believe that I am not using the proper technqiue. Here is the relevant code:



zName = """TEXT;" & fName & """"

rc = MsgBox("***Debug*** zName " & zName)

With Sheets(shName).QueryTables.Add(Connection:= _
zName, Destination:=Range("A1"))

'With Sheets(shName).QueryTables.Add(Connection:= _
'"TEXT;C:\Data\PNL01.DAT", Destination:=Range("A1"))
.Name = shName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


Notice the two sets of "With" statements. If I comment out the first set and use the second set, the program works. The difference is that the first set allows me to use a file name selected by the user instead of hard-coded. You can see that I have a display of the derived file name, and it displays correctly. However, the program gets a "1004" object error on the "With" statement unless I use version 2.

My gut feel is that "SetQueryTables" is inappropriate, but my attempt at finding a better choice failed. I thought "SetTextMethod" might be of use, but the documentation implied that it was only for a "new workbook with one worksheet". Since I can easily do this manually, I know it can be done in a macro.

FYI: fname is the file name, e.g. "C:\Data\PNL01.DAT"

Ideas? TIA

[jwise]

In further experiments, I discovered that the "zName" variable was incorrectly built. Although version 2 of the "With" statement shows the data within quotes, when I left the quotes OFF the macro worked. Somehow, I still believe that "QueryTables" is not the correct import tool. It is, regardless of my intuition, working.


Here is the corrected code, and do notice the first statement:
zName = "TEXT;" & fName

rc = MsgBox("***Debug*** zName " & zName)

With Sheets(shName).QueryTables.Add(Connection:= _
zName, Destination:=Range("A1"))

'With Sheets(shName).QueryTables.Add(Connection:= _
'"TEXT;C:\Data\PNL01.DAT", Destination:=Range("A1"))
.Name = shName
.FieldNames = True
.RowNumbers = False
...



I freely admit I have no idea why this works. Thanks to those who thought about this, and hopefully you learned how to handle similar situations.