VBA Query that pulls data from both ODBC database and excel table

[tdweinrich]

Hello. I have been working on this for quite a while and I can't seem to figure it out. I am trying to create a query in excel that combines information from an excel table and database. I can do each of them separately no problem.

Here is the VBA code for the excel query:

Sub ExcelQuery()
'
    Range("Table_Query_from_Excel_Files5[[#Headers],[Customer:]]").Select
    With Selection.ListObject.QueryTable
        .Connection = Array(Array("ODBC;DSN=Excel Files;DBQ=Z:\OEM Office\Trevor Weinrich\Projects\BOM Template 2.0\BOM template 2017-08-16 1.xlsm;DefaultDir=Z:\OEM Office\Trevor Weinrich\Projects\BOM Template 2.0;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT `BOM$`.`Customer:`" & Chr(13) & "" & Chr(10) & "FROM `BOM$` `BOM$`" & Chr(13) & "" & Chr(10) & "WHERE (`BOM$`.`Customer:` Is Not Null)" _
        )
        .Refresh BackgroundQuery:=False
    End With
'
End Sub

And here is the VBA code for the database query:

Sub DatabaseQuery()
'
    With Selection.ListObject.QueryTable
        .Connection = _
        "ODBC;DSN=OEM;Description=OEM;UID=trevor.weinrich;Trusted_Connection=Yes;APP=Microsoft Office 2016;WSID=DFP-OEM-0913-A;DATABASE=OEM"
        .CommandText = Array( _
        "SELECT DISTINCT p21_view_item_uom.item_id, p21_view_item_uom.unit_of_measure, p21_view_item_uom.purchasing_unit" & Chr(13) & "" & Chr(10) & "FROM OEM.dbo.p21_view_item_uom p21_view_item_uom" & Chr(13) & "" & Chr(10) & "WHERE (p21_view_item_uom.delete_flag=" _
        , "'N')" & Chr(13) & "" & Chr(10) & "ORDER BY p21_view_item_uom.item_id")
        .Refresh BackgroundQuery:=False
    End With
'
End Sub

I want to join these together because there are about 140,000 line items in the database query, and I only care about the the instances where the "item_id" field from the database matches up with the "Customer:" field.
I just can't figure out how to join the two of them. I would greatly appreciate the help.

[Olly]

Much easier to do with Power Query, than with VBA...

[tdweinrich]

I definitely agree with you on that, but I have been avoiding using Power Query because this is a file that will be put on our server and used by quite a few people with various versions of Excel. Last time I used Power Query I recall a few people in my office unable to refresh the data.

[tdweinrich]

Here is an update: I just checked some of my coworker's computers, and not all of them have Power Query. A few of them only don't have versions of Excel that can support Power Query. So unless I can have IT upgrade these people, I am going to need to do it outside of Power Query.

Any help would be very much appreciated.

[tdweinrich]

Could anyone be able to help me out with this problem? It's been besting me for quite a while now.