'Exporting Excel to Microsoft SQL using Macro/VBA

I'm trying to make a Excel Macro that sends lines of data to SQL. The table has alreayd been created within SQL. However I cannot seem to get the lines of code to work. Here is the code below, I've commented on the lines of code that VBA seems to be 'skipping' or not working.

Public conn As ADODB.Connection

Sub demo()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim i As Integer
Dim iRow As Integer

iRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row
i = 3



    VENDOR = "A" & i
    ITEM_NAME = "B" & i
    ITEM_CAT_ID = "C" & i
    ITEM_PS_ID = "D" & i
    ITEM_ATT = "E" & i
    SUB_VENDOR = "F" & i
    SUB_ITEM_CAT = "G" & i
    SUB_ITEM_ATT = "H" & i
    vCOMMENTS = "I" & i
    SUB_DATE = "J" & i

insertStatement = "VALUES('" _
        & Replace(Sheet1.Range(VENDOR).Value, "'", " ") & "', '" _
        & Replace(Sheet1.Range(ITEM_NAME).Value, "'", " ") & "', " _
        & Replace(Sheet1.Range(ITEM_CAT_ID).Value, "'", " ") & "','" _
        & Replace(Sheet1.Range(ITEM_PS_ID).Value, "'", " ") & "','" _
        & Replace(Sheet1.Range(ITEM_ATT).Value, "'", " ") & "','" _
        & Replace(Sheet1.Range(SUB_VENDOR).Value, "'", " ") & "','" _
        & Replace(Sheet1.Range(SUB_ITEM_CAT).Value, "'", " ") & "', '" _
        & Replace(Sheet1.Range(SUB_ITEM_ATT).Value, "'", " ") & "', '" _
        & Replace(Sheet1.Range(vCOMMENTS).Value, "'", " ") & "', '" _
        & OPID & "')"

insertData (insertStatement)
'The values for insertStatement pops up in the msgBox (have pasted code below) however the values never changes (as in that it seems like i does not seem to be increasing).
'This is actually part of a larger code that sends emails and pastes the values into the email.  That part of the code seems to be working fine (will paste different values in separate emails

i = i + 1
Loop

insertData (insertStatement)

End Sub

Here is the line of code defining insertData

Sub insertData(insdata As String)

    Dim constr, ins_Sql As String

constr = "DRIVER=Server;SERVER=numbers;UID=stackoverflow;PWD=stackoverflow;Database=SandboxDB"
    Set conn = New ADODB.Connection

ins_Sql = "INSERT INTO SandboxDB.dbo.ITEM_SUBS (VENDOR, ITEM_NAME, ITEM_CAT_ID, ITEM_PS_ID, ITEM_ATT, SUB_VENDOR, SUB_ITEM_CAT, SUB_ITEM_ATT, COMMENTS, SUB_DATE) " & insdata

MsgBox ins_Sql 
'This line of code seems to be working as I get a MsgBox in Sheet2 of ins_SQL'
    Sheet2.Range("O1").Value = ins_Sql 'This line of code does not seem to work.  I don't see anytihng in Sheet2 cell O1 even when I take out the quotation marks.
On Error Resume Next 'This code does not seem to make a difference in whether the whole thing runs or not'
conn.Open constr
    conn.Execute ins_Sql
    conn.Close
'These last three lines are skipped entirely (I've gone through the code line by line with F8).'

End Sub

The code does not give me an error. Instead it runs and then.. just gives me the msgBox popup.

sqlexcelvbaadodb


Solution 1:[1]

Sheet2.Range("O1").Value should be: Sheets("Sheet2").Range("O1").Value

Solution 2:[2]

After i = 3, the next line would be (assuming the first two lines are headings and the data starts on row 3):

Do while i <= irow

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Keith Swerling
Solution 2 Keith Swerling

Related Questions