Inner Join 2 Excel Sheets with ADO?

Hi there,

I hope someone can help me here.

What I'd like to attempt to accomplish is bring together two spreadsheets of data without the use of Access by using SQL in VB code.

I already update a report of mine using CreateObject("ADODB.Connection") and open a record set using a query string.

I'd like to do something along the lines of the following... where excelfile_2 has the latest status of my records.

[VBA]SELECT [Sheet1$].[ID], ["Provider=Microsoft.Jet.OLEDB.4.0;Data Source=exelfile_2.xls;Extended Properties=""Excel 8.0;HDR=YES"";sheet1$].[Status]
FROM [Sheet1$]
INNER JOIN [Sheet1$].[ID] ON ["Provider=Microsoft.Jet.OLEDB.4.0;Data Source=exelfile_2.xls;Extended Properties=""Excel 8.0;HDR=YES"";Sheet1$].[ID] = [Sheet1$].[ID][/VBA]


Any pointers will be much appreciated....:thumb

Correct me if I'm wrong - are you trying to pull a list of all records where ID is the same in both worksheets? If so, then I may have a workaround for you.

Yes, IDs are unique and are the same in each sheet, hence my wish to try this method.
One source comes from a published Business Warehouse report, the other is a Siebel extract.
The source for both is the same, i.e. Siebel. However the report is only published every 2 weeks.

Quote:

---

Originally Posted by Kained

Any pointers will be much appreciated....:thumb

---

I don't see 2 separate sheets in your code - same source file, same Sheet1$:dunno

but assuming exelfile_2 has Sheet1$ and Sheet2$ that need to be joined, why not open the cConn = CreateObject("ADODB.Connection") then just run your SQL through cConn.Execute mySQLStatement

.02 Stan

Thanks Stanl, but the data needs to come from two separate files.

My thoughts were that a connection to the first file would already be opened using CreateObject("ADODB.Connection") hence no reference to it with SELECT [Sheet1$].[ID]

..while a second connection (to exelfile_2) would need establishing 'in-line' in the SQL script? :dunno

You have to create 2 separate connection objects, one for each worksheet you want to open. Then you need to create 2 separate recordset objects, one for each worksheet.

Once you've created both recordsets, you could easily create a loop that uses the .find method to look and see if an [ID] that exists in RecordsetA also exists in RecordsetB. You could also create an empty recordset and update the duplicate records into it, then you would have a recordset that contains all accounts that exist in both.

This is how I would go about this, anyway.

Quote:

---

Originally Posted by Kained

Thanks Stanl, but the data needs to come from two separate files.

---

I think you are confusing a file with a sheet. In the two Provider Strings you gave in your initial code you have

Data Source=exelfile_2.xls

that is a file, both strings reference the same file. Then since you only reference Sheet1$ that to me was the same file and the same sheet - hence my confusion. If the data does, as you say, have to come from two files, they both cannot be named exelfile_2.xls.

Quote:

---

Originally Posted by CreganTur

You have to create 2 separate connection objects, one for each worksheet you want to open. Then you need to create 2 separate recordset objects, one for each worksheet.

---

In my experience you don't. An individual .xls file can be treated by ADO as a database and each sheet, or named range as a table. Therefore, a single Connection to the file can process information from multiple sheets in that same file. And from another perspective, data from multiple tables (say in Access) can be SELECTED or INSERTED into multiple sheets on the same xls file with a single connection. .02 Stan

Quote:

---

Originally Posted by CreganTur

You have to create 2 separate connection objects, one for each worksheet you want to open. Then you need to create 2 separate recordset objects, one for each worksheet.

---

Thanks CreganTur,
I considered this, but reading the second set of data into an array and then searching the array for matching IDs (Assuming this would be faster than the .Find ?)

Stanl, the two provider strings reference the second file twice because of the inner join. Its effectively the second table.
Examples of similar ...
http://www.excelkb.com/article.aspx?...7&cNode=1J5L2H
http://groups.google.co.uk/group/mic...f82f7f2ae61cbc

Thanks for for all your input guys, I appreciate the time you have spent on this. My Google research has gone as far as it can, so I'll opt for the above workaround if this ends here. :peace:

Quote:

---

Originally Posted by Kained

Stanl, the two provider strings reference the second file twice because of the inner join. Its effectively the second table.
Examples of similar ...
http://www.excelkb.com/article.aspx?...7&cNode=1J5L2H
http://groups.google.co.uk/group/microsoft.public.excel.misc/browse_thread/thread/6596bee5b98e96a1/94f82f7f2ae61cbc?lnk=st&q="outer+join"+excel+workbooks#94f82f7f2ae61cbc

---

neither of those references have anything to do with inner joins. I probably should have just asked you to post an example workbook rather than try to interpret your initial post.

Sorry Stanl, this is where my VB experience is lacking. From my point of view they illustrate how to reference a further sheet with the SQL code.

I probably wrongly assumed I could extend this to using INNER JOINS.

[VBA]stSQL = "INSERT INTO [Blad1$] IN 'E:\Arbetsmaterial\ALADO.xls' 'Excel 8.0;'" & _
"SELECT * FROM [Sheet1$A1:A2]"
[/VBA]

This statement reference 2 workbooks, the one from the connection ([Sheet1$A1:A2]) and then the other (ALADO.xls)

but perhaps am missing something?