Excel - Inner Join

Posted on 2011-02-24
Medium Priority
Last Modified: 2012-05-11

Is there a way in Excel to create an inner join on two spreadsheets, and output to a third spreadsheet?

Thank you.
Question by:tahirih
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 34975158
The routine below runs a SQL command against any workbook, open or closed, and returns the result into a destination range, an array, or as a recordset. The code can be placed in any general module. See the comments in the routine for more information about the parameters.

Below is an example using it to do an inner join using tables residing in the workbook in which the code resides.

   RunSQLQuery ThisWorkbook, "SELECT T1.Field1, T1.Field2, T2.Field2 FROM [Sheet1$A1:B100] AS T1 INNER JOIN [Sheet2$A1:B100] AS T2 ON T1.Field1=T2.Field1", Sheets("Sheet3").[A1], True, True

Below is the code for the routine.

[Begin Code Segment]

Public Sub RunSQLQuery( _
      ByVal SourceWorkbookPath As String, _
      ByVal SQLCommand As String, _
      ByRef Destination As Variant, _
      Optional TablesHaveHeaders As Boolean = True, _
      Optional WriteHeader As Boolean _

' Run SQL command against one or more tables in a workbook and write the
' resulting recordset to the range starting at the destination specified.
' Syntax
' RunSQLQuery(SourceWorkbookPath, SQLCommand, Destination, [WriteHeader])
' SourceWorkbookPath - The full path to the workbook in which the source tables
'   reside. The workbook must have been previously saved to disk (although the
'   most current version does not need to be saved to get the most recent
'   data). To get the full path for an open workbook using the workbook object
'   use:
'      SourceWorkbookPath.Path & "\" & SourceWorkbookPath.Name
'   The source workbook can be open or closed.
' SQLCommand - Any valid SQL command. Reference tables either by cell
'   reference:
'      SELECT * FROM [Sheet1$A1:C100]
'   or by range name:
'      SELECT * FROM [Table1]
'   Field names are determined by the values in the first row of each table. To
'   reference a field name use square brackets around the name:
'      SELECT T1.[FieldName] FROM [Table1] AS T1
' Destination - Any valid range or a variant variable. If a range is passed
'   then the resulting recordset is copied to the cells starting at the top
'   left cell in Destination. As many cells are used as are needed. If an empty
'   array is passed (UBound = -1) then the query result is returned as an
'   array. Otherwise the resulting recordset is returned in Destination.
' TablesHaveHeaders - Pass True if the source tables have a header row. Pass
'   False if not. Optional. If omitted then True is assumed. If the source
'   tables do not have headers then the columns are named "F1", "F2", etc.
' WriteHeader - Pass True to write a header row, False to not write the
'   headers. Optional. If omitted then False is assumed.

   Dim RecordSet As Object ' ADODB.RecordSet
   Dim ConnectionString As String
   Dim Column As Long
   Dim Row As Long
   Dim ReturnArray As Boolean
   Dim RecordCount As Long
   Set RecordSet = CreateObject("ADODB.RecordSet") ' New ADODB.RecordSet
   If IsArray(Destination) Then
      If UBound(Destination) = -1 Then ReturnArray = True
   End If
   ' Open the ADODB connection and run the query
   ConnectionString = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & SourceWorkbookPath & ";" & _
      "Extended Properties='Excel 8.0;HDR=" & IIf(TablesHaveHeaders, "Yes", "No") & "'"
   RecordSet.Open SQLCommand, ConnectionString, 2, 1, 1 ' adOpenDynamic, adLockReadOnly, adCmdText
   ' Handle the results according to what was passed in Destination
   If TypeName(Destination) = "Range" Then
      ' Copy recordset to range
      Application.ScreenUpdating = False
      If WriteHeader Then
         For Column = 1 To RecordSet.Fields.Count
            Destination.Cells(1, Column).Value = RecordSet.Fields(Column - 1).Name
         Next Column
         Destination.Cells(1, 1).Resize(1, RecordSet.Fields.Count).Font.Bold = True
         Set Destination = Destination.Offset(1)
      End If
      Destination.CopyFromRecordset RecordSet
      Application.ScreenUpdating = True
   ElseIf ReturnArray Then
      ' Return an array
      Do While Not RecordSet.EOF
         RecordCount = RecordCount + 1
      ReDim Destination(1 To RecordCount + IIf(WriteHeader, 1, 0), 1 To RecordSet.Fields.Count)
      Row = 1
      If WriteHeader Then
         For Column = 1 To RecordSet.Fields.Count
            Destination(Row, Column) = RecordSet.Fields(Column - 1).Name
         Next Column
         Row = Row + 1
      End If
      Do While Not RecordSet.EOF
         For Column = 1 To RecordSet.Fields.Count
            Destination(Row, Column) = RecordSet.Fields(Column - 1).Value
         Next Column
         Row = Row + 1
      ' Return recordset
      Set Destination = RecordSet
   End If

End Sub

[End Code Segment]

LVL 42

Expert Comment

ID: 34975259
Of course, a very simple way to purge code from a workbook is to save it as a macro-free workbook.

File SaveAs ExcelWorkbook or Exce 97-2003 Workbook.

Its that simple.  I had never done this before so I took a sample excel file with lots of macros in several modules and sheet codepages, saved as macro free and presto - the job was done.

LVL 42

Expert Comment

ID: 34975272
Please ignore my post - was meant for another question...


Author Closing Comment

ID: 34984021
Thank you

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question