INSERT INTO statement from Excel to SQL Server Table using VBA

Question

The code below is meant to simply add rows of information from an Excel sheet to a table in SQL Server. It is tediously written where it goes row by row and cell by cell. It runs with no error pop-ups, but when I check the table for my data, there is nothing there.

I have been querying in SQL to check by using a WHERE clause and the 'Analyst' field should match my name. Nothing pops up.

Why is my data not showing up in the table? AND I am open to hear anyone's suggestions on better ways to do this. Thanks!

Public Sub ConnectToDB()
    Dim DBCONT As Object
    Dim strConn As String
    Dim Server_Name As String
    Dim Database_Name As String
    Dim Table_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim strSQL As String
    Dim rs As Object
    Dim Fields As String
    Dim LastRowAudit As Long
    Dim i As Long

    Dim sAuditType, sClaimeReceivedDate, sDateAssigned, sDateCompleted, sAnalyst, sCustomer, sID, sAffiliate, sFacility, sDEA, sAcctNumber, sWholesaler, sVendor, sProduct, sNDC, sRef, sClaimedContract, sClaimedContractCost, sContractPriceStartDate, sContractPriceEndDate, sCatalogNumber, sInvoiceNumber, sInvoiceDate, sChargebackID, sContractIndicator, sUnitCost, sWAC, sPotentialCreditDue, sQTY, sSpend, sIpDshIndicator, sDSHorHRSANumber, sUniqueGPOCode, sComment, sResCode, sCorrectCost, sCRRBCM, sCRRBRebill, sCRRBDate As String

'   SET ALL VARIABLES
    Server_Name = "I have this in my actual code" ' Enter your server name here
    Database_Name = "I have this in my actual code" ' Enter your database name here
    Table_Name = "I have this in my actual code"
    User_ID = "I have this in my actual code" ' enter your user ID here
    Password = "I have this in my actual code" ' Enter your password here


    WkbName = ThisWorkbook.Name
    SheetName = "Audit Data" '  WHERE RS IS


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   SET SQL STRING

    strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
    " ([Audit],[Audit Type],[Claim Received Date],[Date Assigned],[Date Completed]," & _
    " [Analyst],[Customer],[ID],[Affiliate],[Facility],[DEA],[Acct Number],[Wholesaler],[Vendor],[Product],[NDC],[Ref],[Claimed Contract]," & _
    " [Claimed Contract Cost],[Contract Price Start Date],[Contract Price End Date],[Catalog Number],[Invoice Number],[Invoice Date]," & _
    " [Chargeback ID],[Contract Indicator],[Unit Cost],[WAC],[Potential Credit Due],[Qty],[Spend],[IP-DSH indicator Y/N]," & _
    " [DSH and/or HRSA Number],[Unique GPO Code],[Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
    " VALUES ('" & sAudit & "', '" & sAuditType & "', '" & sClaimeReceivedDate & "', '" & sDateAssigned & "', '" & sDateCompleted & "', '" & sAnalyst & "', '" & sCustomer & "', '" & sID & "', '" & sAffiliate & "', '" & sFacility & "', '" & sDEA & "', '" & sAcctNumber & "', '" & sWholesaler & "', '" & sVendor & "', '" & sProduct & "', '" & sNDC & "', '" & sRef & "', '" & sClaimedContract & "', '" & sClaimedContractCost & "', '" & sContractPriceStartDate & "', '" & sContractPriceEndDate & "', '" & sCatalogNumber & "', '" & sInvoiceNumber & "', '" & sInvoiceDate & "', '" & sChargebackID & "', '" & sContractIndicator & "', '" & sUnitCost & "', '" & sWAC & "', '" & sPotentialCreditDue & "', '" & sQTY & "', '" & sSpend & "', '" & sIpDshIndicator & "', '" & sDSHorHRSANumber & "', '" & sUniqueGPOCode & "', '" & sComment & "', '" & sResCode & "', '" & sCorrectCost & "', '" & sCRRBCM & "', '" & sCRRBRebill & "', '" & sCRRBDate & "')"


    Debug.Print strSQL

'   SET TO CONNECTION VARIABLES
    Set DBCONT = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

'   LOOP THROUGH AND APPEND TO TABLE
    ThisWorkbook.Worksheets("Audit Data").Select
    LastRowAudit = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    With ActiveSheet

    DBCONT.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"



        For i = 2 To LastRowAudit
            sAudit = Cells(i, 1)
            sAuditType = Cells(i, 2)
            sClaimeReceivedDate = Cells(i, 3)
            sDateAssigned = Cells(i, 4)
            sDateCompleted = Cells(i, 5)
            sAnalyst = Cells(i, 6)
            sCustomer = Cells(i, 7)
            sID = Cells(i, 8)
            sAffiliate = Cells(i, 9)
            sFacility = Cells(i, 10)
            sDEA = Cells(i, 11)
            sAcctNumber = Cells(i, 12)
            sWholesaler = Cells(i, 13)
            sVendor = Cells(i, 14)
            sProduct = Cells(i, 15)
            sNDC = Cells(i, 16)
            sRef = Cells(i, 17)
            sClaimedContract = Cells(i, 18)
            sClaimedContractCost = Cells(i, 19)
            sContractPriceStartDate = Cells(i, 20)
            sContractPriceEndDate = Cells(i, 21)
            sCatalogNumber = Cells(i, 22)
            sInvoiceNumber = Cells(i, 23)
            sInvoiceDate = Cells(i, 24)
            sChargebackID = Cells(i, 25)
            sContractIndicator = Cells(i, 26)
            sUnitCost = Cells(i, 27)
            sWAC = Cells(i, 28)
            sPotentialCreditDue = Cells(i, 29)
            sQTY = Cells(i, 30)
            sSpend = Cells(i, 31)
            sIpDshIndicator = Cells(i, 32)
            sDSHorHRSANumber = Cells(i, 33)
            sUniqueGPOCode = Cells(i, 34)
            sComment = Cells(i, 35)
            sResCode = Cells(i, 36)
            sCorrectCost = Cells(i, 37)
            sCRRBCM = Cells(i, 38)
            sCRRBRebill = Cells(i, 39)
            sCRRBDate = Cells(i, 40)



            DBCONT.Execute strSQL

         Next i
    End With

    Call CloseDB

    MsgBox i & " Lines Imported."

End Sub

Sub CloseDB()
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    DBCONT.Close
    Set DBCONT = Nothing
End Sub

Parfait · Accepted Answer · 2018-10-19 20:09:21Z

5

Reconsider your heavy Excel migration to SQL Server for several solutions:

SQL Server: Save Excel data into text format (.txt, .tab, .csv) and use any number of SQL Server's bulk handling operations: bcp utility, OPENROWSET, OPENDATASOURCE, import wizard, and BULK INSERT. Place this process in a stored procedure to be run inside database.

Below are two equivalent OPENROWSET examples on Excel workbooks where no individual values are handled but whole table operations:

-- OLEDB PROVIDER
INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...) 
SELECT COl1, Col2, Col3, ... 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Path\To\Excel\Workbook.xlsx; HDR=YES; IMEX=1', [Sheet$]);

-- ODBC DRIVER
INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...) 
SELECT COl1, Col2, Col3, ...
FROM OPENROWSET('MSDASQL',
     'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
      DBQ=C:\Path\To\Excel\Workbook.xlsx', 'SELECT * FROM [Sheet$]');

MS Access: Use Excel's database sibling, Access, as medium between the source and destination sources. Specifically, have Excel sheet and SQL Server table as linked tables where you can run a simple append query:
```
INSERT mySQLServerTable (Col1, Col2, Col3, ...) 
SELECT Col1, COl2, Col3 ... 
FROM myExcelTable
```

MS Excel: If you have to run in Excel, use parameterization with ADO Command object and avoid the need to concatenate VBA values to SQL code with numerous quote enclosures.

... same as above...

' PREPARED STATEMENT
strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
         "    ([Audit], [Audit Type], [Claim Received Date], [Date Assigned], [Date Completed]," & _
         "     [Analyst], [Customer], [ID], [Affiliate], [Facility], [DEA], [Acct Number], [Wholesaler]," & _
         "     [Vendor], [Product], [NDC], [Ref], [Claimed Contract], [Claimed Contract Cost]," & _
         "     [Contract Price Start Date], [Contract Price End Date], [Catalog Number], [Invoice Number], [Invoice Date]," & _
         "     [Chargeback ID], [Contract Indicator], [Unit Cost],[WAC], [Potential Credit Due]," & _
         "     [Qty], [Spend],[IP-DSH indicator Y/N], [DSH and/or HRSA Number], [Unique GPO Code]," & _ 
         "     [Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
         " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," _
         "         ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

DBCONT.Open ...

Set sh = ActiveSheet

For i = 2 To LastRowAudit
    Set cmd = CreateObject("ADODB.Command")

    With cmd
      .ActiveConnection = DBCONT   ' CONNECTION OBJECT
      .CommandText = strSQL        ' SQL STRING     
      .CommandType = adCmdText

      ' BINDING PARAMETERS
      .Parameters.Append .CreateParameter("sAuditParam", adVarChar, adParamInput, 255, sh.Cells(i, 1))
      .Parameters.Append .CreateParameter("sAuditTypeParam", adVarChar, adParamInput, 255, sh.Cells(i, 2))
      .Parameters.Append .CreateParameter("sClaimeReceivedDateParam", adVarChar, adParamInput, 255, sh.Cells(i, 3))

       '... rest of parameters

      .Execute                     ' RUN ACTION
    End With

    Set cmd = Nothing
Next i

answered Oct 19, 2018 at 20:09

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

S Meaden Over a year ago

Masterly answered.

Parfait Over a year ago

Thanks, @SMeaden! If not the OP, I hope this can help future readers.

Cam David Over a year ago

This is awesome stuff! Using the parameterization, would I still use the variables I currently have in the strSQL that go with VALUES and then create new variables for the parameters? I just see '?" and not sure what is meant to go there.

Parfait Over a year ago

The qmarks , ?, are placeholders which are later binded by position with .Parameters.Append lines. Your Excel cell range values are in the last argument of .CreateParameter(Name, Type, Direction, Size, Value) method. Parameterization divorces SQL from app layer, here being VBA. You never need to touch the prepared statement.

nir Over a year ago

Hi, your code works perfectly, thanks, i have a small improvement to easily adjust the code for other tables. instead of creating all the column names and ? values for the strSQL, just create an array of the column names "mycolumns=array("col1","col2")", and loop all the array to create a string of column names with "],[" seperator and list of "?" for values, use this 2 strings in the strSQL. you can also use it in the parameter append if you upload consecutive columns, this way you get more robust code, easy to fit any table, and much less code

|

Tim Williams · Accepted Answer · 2018-10-19 18:10:26Z

1

You already built strSQL using empty variables, and you never update it after that, so it remains that way.

You need to insert the field values each time through the loop.

Move this:

strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
'etc etc

to just before

DBCONT.Execute strSQL

answered Oct 19, 2018 at 18:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

2 Comments

Cam David Over a year ago

THANKS @TimWilliams! Works like a dream on a sheet that has 84 rows. However, when I went to use it on a sheet with 30K+ rows it gave me the following error at DBCONT.Execute strSQL: method 'execute' of object '_connection' failed It ran through about 199 of the rows and successfully uploaded them. I then pressed DEBUG and started stepping through the code manually. It actually continued to work and upload each time, but gave me the error message right at the same spot. Does this happen if the amount is too larger?

Cam David Over a year ago

Was able to solve this issue by adding some code of DBCONT.commandtimeout DBCONT.connectiontimeout and let it run. The scenario is we have a bunch of analysts who are working reports. In order to keep track of audits we need this data and first append some information for record keeping. Then, the analyst has to upload it to the server. Therefore, being able to use the same Excel tool I have created vs. having them save a file, then open the SQL and append another way is not the way it has been asked.

Collectives™ on Stack Overflow

INSERT INTO statement from Excel to SQL Server Table using VBA

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related