SQL Query between 2 workbooks

Question

I am trying to perform a UNION on two different worksheets located in two different workbooks. I can extract data from one worksheet at a time and get it into a recordset object, but two at the same time is just not working. This is an automated Excel tool. I can't use Access to perform the Union as that is against my requirements. I can pull each table and loop through but if I can just Union (and later Inner Join) the two tables with one go, it would save a lot of looping.

here is the connection string I'm using:

'XXXX - Replaced with Path to source file, YYYY - Replaced with Excel version (Cur 8.0 or 12.0)

Global Const EXCELCONNSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XXXX;Extended Properties='excel YYYY;HDR=YES';Persist Security Info=False;"

The code goes:

Dim myRCD As New Recordset

Dim myConn As New Connection

Dim strSQL As String

'Open Connection

myConn.Open GetDBConnectionString(2, "C:\PartData.xlsx")

'Generate SQL

strSQL = SELECT * From [Purchased Parts$] UNION SELECT * From [Parts Data$] IN 'C:\PurchaseParts.xlsm' 'Excel 12.0'

'Get Database

myRCD.Open strSQL, myConn, adOpenForwardOnly, adLockOptimistic

Upon hitting the last line I get the following error:

Run-time error '-2147467259 (80004005)':

Unrecognized database format 'C:\PartData.xlsx'.

Thanks all!

---

Jared Streger

Answer 1

Hi,

I would go back to basics on this one.  You are trying to open 2 excel workbook types with one connection string.  So what I would do instead is to use 2 connection strings and 2 recordsets.  Yes this will require you to do more coding with in your function to manual do the join, but at least you will have better control of your connections and recordsets.

Though before you do anything double check you can open each workbook seperately

Andy

Answer 2

Jared:

There are a number of ways to approach this issue.  One suggestion might be to use VBA code to copy both worksheets to a third temporary workbook that is not visible to the user.  At that point, you could probably UNION or INNER JOIN (haven't had the time to test this).  You could then destroy the temporary workbook at the end of your operations.  I don't know if this option meets your requirements but it eliminates the two workbook issue which appears to be your main hurdle.

David Lloyd

Lemington Consulting

http://LemingtonIT.com