Comma delimited text file import to Excel failing

[Beezkneez]

Maybe its late in the day but this one is doing my head in. I have a .txt file Im seeking to import to an Excel worksheet where wierd and wonderful macros will do fascinating things to it.

But the actual text import is erroring and my brain has screamed that its after 4pm and then gone to the corner to pout.

Sub CDSGet()
Sheet4.Activate
Dim CDSFile As String
CDSFile = Sheet2.Range("B11").Value
Debug.Print CDSFile
[COLOR=blue]Set qtCDSRecordset = Sheet4.QueryTables _
    .Add(Connection:=CDSFile, _
        Destination:=Sheet4.Cells(1, 1))[/COLOR]
With qtCDSRecordset
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With
End Sub

The Variable CDSFile picks up the filename and directory path from the cell B11 in Sheet2. The debug line confirms that is picking up cleanly.

When stepped through, the highlighted section is where I get a Runtime Error 1004, Application-defined or object-defined error.

I have a similar piece of code working in other workbooks and have hit a wall in figuring out why its not working here. Any guidance would be keenly appreciated.

NB: All worksheets quoted in the code exist, currently have no protection and are unhidden. Target import file exists in the specified location.

 

[hsk]

If this piece of code is working good for other file, just check ....
the data is delimited in to how many columns?

I think Excel can take only upto 256 ...

 

[Beezkneez]

I imported the text file into Excel manually with comma delimitation and had no issue. Only 32 columns. Approx 3500 rows. No field contains more than about 50 characters.

Oh, using Excel 2003, VB 6.3.