Hi,

Help needed on the following:
I want to write a query in VB that combines data from a spreadsheet and an Access table.

conn.execute "SELECT * from 'spreadsheet.xls'"
returns an Error: Unrecognised DB format

Can anyone please help?

Regards,
Mihai

    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y("ODBC;DSN=Excel Files;DBQ=C:\source.xls;DefaultDir=C:\"), Array(";DriverId=790;MaxBufferSize=2048;PageTimeou t=5;")), Destination:=Range("A1"))
        .CommandText = Array("SELECT F1, F2, F3, F4, F5 FROM data ")
        .Name = "Query from Excel Files"
        .FieldNames = True
        .Refresh BackgroundQuery:=False
    End With

replace the DBQ and Default locations in the code above with the location and filename of the source data.

Ensure that the table of data is defined with a name of somesorts otherwise it may not work.

Cheers

Matthew

Thanks Matthew,

actually I have found the string for the connection (closer to your indication), using UDL to txt conversion.

"Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel Files;Mode=Share Deny None;Initial Catalog=workbook_name.xls"

Now I want to know if it is possible to join the table with an Access table. Can I work with 2 connections, or should I create 2 recordsets and then create another query based on the 2 recs?

Do you have another ideea?

Regards,
Mihai

If you are using access, then I would recommend creating a linked connection into Access from your Excel file. You can then manipulate the joins in query to bring back a recordset.

In Excel you can then link to the query results and exen pass through from excel certain parameters from text boxes or drop down boxes that interact with the query and return only the records you need.

synopsis, I recommend you get Access to do the hard work.

Cheers

Matthew