SQL query in VBA with QueryTable Object

[magnaral]

SQL query in VBA with QueryTable Object
Hello

I am trying to import selected data from a comma separated text file using the following method.


Sub PerformQuery()

Dim thisSheet As Worksheet
Set thisSheet = ThisWorkbook.Worksheets(2)

Dim ThisSQL
ThisSQL = "SELECT * "

Dim connString As String
connString = "TEXT;q:\arbeid\20040903.icm.95pc.dvf.txt"

Dim thisQT As QueryTable
Set thisQT = thisSheet.QueryTables.Add(Connection:=connString, Destination:=Range("A1"))

thisQT.BackgroundQuery = False

thisQT.TextFileCommaDelimiter = True
thisQT.TextFileParseType = xlDelimited
thisQT.TextFileStartRow = 3

thisQT.sql = ThisSQL

thisQT.Refresh

End Sub

The problem is that I get this error message: "Runtime Error '1004': Incomplete datasource"

What are the name of the tables/coloumns/fields in the text file I need to refer to in the Sql query?


With best regards,

M. Alexander Kleiven

 

[PHV]

Have you tried MS-Query and the macro-recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

I agree with PHV - macro recording and then observng & modifying the code will help you the most.

Your SQL String needs to include From TableName probably something like this...

ThisSQL = "SELECT * From q:\arbeid\20040903.icm.95pc "

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[magnaral]

The MS Query does not find any tables at all, although I defined them when created the data source from MS Text Driver. The macro recorder did not record anything from this creation procedure.

Alexander

 

[SkipVought]

Did you try to modify your sql as advised?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[magnaral]

Yes, I still get the same error message.

I am able to manually create a new text-based datasource in Excel, but not able to find any fields in it when I open MS Query.

Alexander

 

[SkipVought]

Unfortunately, I have ZERO experience with TEXT source as opposed to ODBC source queries.

If you can get your data into Excel, then SAVE AS an .xls (Excel) workbook and then use the Excel Files data source to create your query. Be sure that your table has one heading row.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[magnaral]

The problem is that one of the text files has over 400000 rows, and Excel can't handle that. I want a selection of these into Excel.

Alexander