Exporting Excel to Microsoft SQL using Macro/VBA

Question

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.

May You need to click this link : https://stackoverflow.com/questions/39610133/how-can-i-import-an-excel-file-into-sql-server — user17084213, Jan 28 '22 at 18:22
Ahh I'm actually making this tool for someone else. Basically this macro will populate an email (which has already been tested and works properly) and send the data to a SQL database. I'm trying to make it so that person can do everything from within excel — Seungmin Lim, Jan 28 '22 at 20:39
Oops I seem to have missed copying that part from my section of the code. Dim OPID As String OPID = Environ$("username") — Seungmin Lim, Jan 28 '22 at 20:39
Make sure you have `On Error Resume Next` commented out - there's no reason to have it there, since all it does is silently hide errors, making it impossible to debug any issues. — Tim Williams, Jan 29 '22 at 04:04
The last field is SUB_DATE (is that a date type?) but the value is OPID as string ? — CDP1802, Jan 29 '22 at 09:10
oops I forgot to add in the OPID variable in the sample code up above. It is ``` OPID As String OPID = Environ$("username") ``` — Seungmin Lim, Feb 01 '22 at 14:29
Your SQL statement has the last field as SUB_DATE, that seems a odd field name to hold a username. — CDP1802, Feb 01 '22 at 15:58

score 1 · Answer 1 · answered Jan 28 '22 at 19:18

1

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

answered Jan 28 '22 at 19:18

Keith Swerling

136
1
6

This one managed to print out the ins_SQL into the cell! However I still have to deal with it skipping the conn.Open and how it does not seem to be updating the values each iteration of the loop. – Seungmin Lim Jan 28 '22 at 20:37

score 0 · Answer 2 · answered Feb 01 '22 at 16:15

0

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

answered Feb 01 '22 at 16:15

Keith Swerling

136
1
6

Exporting Excel to Microsoft SQL using Macro/VBA

2 Answers2