Excel import to SQL via VBA (Run-Time Error)

Question

I am trying to import via a VBA button a ton of Excel data (around 30k + daily) into an existing table in SQL server. My question is how can I do this as simple as possible, code speaking? The headers both in my Excel file and SQL table are 1:1 the same so I just want to import everything into the SQL table

This is what I started to write but when I try to make the code work I get a "Run-time error '-2147217865 (80040e37): Invalid object name "dbo.Rawdata".

Private Sub cmdImport_Click()

    Dim r As Range
    Dim c As Range
    Set r = Sheet1.Range("A6:DA269239")
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.ConnectionString = _
        "Provider=MSOLEDBSQL;" & _
        "Server =localhost\name" & _
        "Database =name;" & _
        "Trusted_Connection=yes;"
    
    con.Open
    
    Dim iRowNo As Integer
    Dim strn_reference As String
    Dim batchInsert As String
    
    Dim batchSize As Integer
    batchSize = 1000
    
    iRowNo = 0
    For Each cl In r
        iRowNo = iRowNo + 1
        batchInsert = batchInsert + (IIf(iRowNo > 1, ",", "")) + "('" & Replace(cl.Value2, "'", "''") & "')"
         If (iRowNo = batchSize) Then
             con.Execute "insert into dbo.Rawdata (trn_reference) Values " & batchInsert
             iRowNo = 0
             batchInsert = ""
         End If
     Next
     If Len(batchInsert) > 0 Then con.Execute "insert into dbo.Rawdata (trn_reference) Values " & batchInsert


    MsgBox "Reference Numbers imported"
 
    con.Close
    Set con = Nothing
    
End Sub

Thank you everyone for the help!

The loop does work for me. As soon as iRowNo reaches 1000 the if branch also runs and iRowNo will be reset to 0. — Storax
– Storax, Commented Dec 19, 2022 at 16:43
hmm interesting that it works for you. I tested it again with a Debugger and it says that he actually has a problem with the "con.Execute "insert into dbo.Rawdata..." and i get a run-time error -2147217865 (80040e37): Invalid object name "dbo.Rawdata". Any idea what this could be ? — AlexandraStk
– AlexandraStk, Commented Dec 19, 2022 at 19:45
I am afraid but that is a different problem now. Please edit your post accordingly — Storax
– Storax, Commented Dec 19, 2022 at 20:55
Thank you for the tip! I edited the post (correctly hopefully) :) I am sorry if I was unclear before, I am quite new to SQL and this platform also — AlexandraStk
– AlexandraStk, Commented Dec 20, 2022 at 9:41

Mecanik · Accepted Answer · 2022-12-24 05:50:56Z

0

I guess you should refer to the table name from SQL server not dbo.Rawdata but directly:

Rawdata|"Insert into Rawdata(column_name) VALUES ('" & vba_variable & "')"

This should be the SQL statement from VBA.

This work for me very well.

edited Dec 24, 2022 at 5:50

Mecanik

1,0741 gold badge24 silver badges62 bronze badges

answered Dec 20, 2022 at 12:47

Salanta Gheorghe

11 bronze badge

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

4 Comments

AlexandraStk Over a year ago

Thank you so much for the answer! I will test it in my code and see if anything changes. If i understood correctly this part should look like this or ? If (iRowNo = batchSize) Then con.Execute "insert into Rawdata (column_name) Values ('" & vba_variable & "') batchInsert" iRowNo = 0 batchInsert = "" Sorry if I understood wrong, I am quite new to writing code, hope you can understand!

Salanta Gheorghe Over a year ago

batchinsert should be the vba_variable this should be the execute code "insert into Rawdata (column_name) Values ('" & batchinsert & "')" if your batchinsert is a number datatype (integer, BigInteger, etc) then after values you should put "( ' & batchinsert & ')"

AlexandraStk Over a year ago

Now it seems to work better but it still has a problem with the con.Execute line...i have way to many columns to declare in the (column_name) part and if I delete that part it asks me for parameters (no value given for one or more required parameters).

Salanta Gheorghe Over a year ago

if you have more columns, that put them separated with comma INSERT INTO Rawdata (Column1,Column2,etc) VALUES (Column1Value,Volumn2Value,etc), if you do not put any column name, then you should go with INSERT INTO Rawdata ValueFor1stColumn,ValueFor2ndColumn,etc you need to put values separated with comma for each column in table (except identified columns, ID column in most cases, and calculated columns, or default value)

Collectives™ on Stack Overflow

Excel import to SQL via VBA (Run-Time Error)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related