CODEWITHSUNDEEP
sqlexcelvba
CraigM
CraigM

Reputation: 65

How do I INSERT INTO SELECT from two different worksheets

I have a sheet BF_UPLOAD, that has rows in it. I need to add data from two other sheets Diamond and Variations to BF_UpLoad. The two input worksheets need to be joined, neither sheet has all the needed data.

It might be easier to add a column [Diamond$].name to the Variations sheet before moving the data to the BF_UpLoad sheet.

Sheets Diamond and Variations have a common key [Diamond$].Code is the equivalent of [Varations$].sku

I think this is the correct JOIN statement.

FROM [Diamond$] INNER JOIN [Variations$] ON [DIamond$].code = [Varations$].sku

To add records at the bottom of BF_UpLoad I believe I need to use a:

INSERT INTO statement

The code I am working on is included at the bottom of this posting. The code works for the sku, regular_price and sale_price fields.

I cannot find a way to SELECT data from the two sheets much less get the JOIN to work.

I have included source data below as well as a sample of the desired output.

WorkSheets:

BF_UpLoad has the following columns: 

sku, post_title, regular_price, sale_price

Variations has the following columns, which are NOT contiguous: 

sku, regular_price, sale Price

Diamond has the following columns, which are NOT contiguous: 

Code, Name

On the Diamond sheet CODE is equal to the sku column on the other sheets.

DESIRED OUTPUT IN sheet: BF_UpLoad

Sheet: BF_UpLoad: Columns are NOT contiguous

sku         post_title             regular_price  sale_price
ABT00201    BioCare, Fly Trap      11.81          10.69
ABT00204    BioCare,Moth Trap       4.95          9.06  
ABT00415    BioCare,Fruit Fly Trap  6.9           12.63 
AB00017     Acrobird Playland 20"   59.81         56.41
AB00018     Acrobird Playland 24"   79.78         73.24
AB00021     Acrobird Playland 14"   30.84         24.35

INPUT WORKSHEETS

Sheet: Diamond: Columns are NOT Contiguous

sku       Name
AB00017   Acrobird Playland 20"
AB00018   Acrobird Playland 24"
AB00021   Acrobird Toddler Playland 14"

Sheet: Variations: Columns are NOT contiguous

sku,      regular_price, sale_price
AB00017   59.81          56.41
AB00018   79.78          73.24
AB00021   30.84          24.35

This is the code I am trying to get working:

Private Sub cmbVariations2BF_UpLoad_Click() 
    Dim objConnection As ADODB.Connection
    Dim objRecordSet As ADODB.Recordset
    Dim i As Integer
    Dim strSQL As String
    Dim strConn As String
    Dim strTarget As String
    Dim hdrName As Variant
    Dim wksName As Worksheet
    Dim wksOutput As Worksheet
    Dim cnn As ADODB.Connection
    Dim strConnectionString As String
    Dim sh As Worksheet

    Dim strInsert As String

    Dim lngRow As Long

    Dim LastUsedCell As Integer

    Set sh = Sheets("Variations")

    strTarget = "BF_UpLoad"

    pubBF_UpLoadRows = Sheets(strTarget).Cells(Rows.Count, 1).End(xlUp).Row      

    Set objConnection = New ADODB.Connection
    Set objRecordSet = New ADODB.Recordset

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & ActiveWorkbook.FullName & ";" & _
                "Extended Properties=""Excel 12.0;HDR=Yes;"";"

    objConnection.Open strConn

    objConnection.Execute "Insert Into [BF_UpLoad$] (sku, regular_price, Sale_price) SELECT sku, regular_price, Sale_price from [Variations$]"

End Sub

Thanks for your input, CraigM

Upvotes: 0

Views: 824

Answers (1)

Rory
Rory

Reputation: 34075

Based on your description, the SQl would be like this:

objConnection.Execute "Insert Into [BF_UpLoad$] (sku, post_title, regular_price, Sale_price) SELECT V.sku, D.[Name], V.regular_price, V.Sale_price from [Variations$] V, [Diamond$] D WHERE D.[Code] = V.[sku]"

Upvotes: 1

Related Questions