Consulting

Results 1 to 11 of 11

Thread: Inner Join 2 Excel Sheets with ADO?

  1. #1
    VBAX Regular
    Joined
    Aug 2008
    Location
    Manchester,UK
    Posts
    14
    Location

    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....

  2. #2
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    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.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  3. #3
    VBAX Regular
    Joined
    Aug 2008
    Location
    Manchester,UK
    Posts
    14
    Location
    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.

  4. #4
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by Kained
    Any pointers will be much appreciated....
    I don't see 2 separate sheets in your code - same source file, same Sheet1$

    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

  5. #5
    VBAX Regular
    Joined
    Aug 2008
    Location
    Manchester,UK
    Posts
    14
    Location
    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?

  6. #6
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    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.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  7. #7
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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.

  8. #8
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  9. #9
    VBAX Regular
    Joined
    Aug 2008
    Location
    Manchester,UK
    Posts
    14
    Location
    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.

  10. #10
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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.

  11. #11
    VBAX Regular
    Joined
    Aug 2008
    Location
    Manchester,UK
    Posts
    14
    Location
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •