CODEWITHSUNDEEP
sql-serverexcelvba
mdjo
mdjo

Reputation: 11

How to upload whole Excel range of data to SQL server using VBA (without Wizards or looping row by row)?

I am trying to upload a range of data in excel (same structure as target SQL table) to a table in SQL server using VBA. I do not want to loop row by row but transfer the whole thing at once. Currently getting an error ("Incorrect syntax near *") so I assume something is wrong with the query but I don't know what.

Is what I am trying to do even possible this way? How would I go about doing it correctly?

Sub testMSSQL()

'----------------------------------------------------------------
'Reference  Microsoft ActiveX data object Library 2.8 ~~
Dim cnn As ADODB.connection
Set cnn = New ADODB.connection

cnn.Provider = "SQLOLEDB.1" '<~~ mssql

user = "user"
pw = "pw"
database = "sample_db"

cnn.ConnectionString = "driver={SQL Server};" & "server=EI-DB;uid=" & user & ";pwd=" & pw & ";database=" & database & ";Trusted_Connection=True;"
cnn.Open

If cnn.State = adStateOpen Then
Else
    MsgBox "Connection to SQL server could not be established. Exiting..."
    Exit Sub
End If

'----------------------------------------------------------------
'build SQL string
tabela = "materials.test"
excel_path = "\\blablblalb\Directory\"
excel_path = excel_path & ThisWorkbook.Name
sheet_name = "sql_test"
Set data_ws = ThisWorkbook.Worksheets(sheet_name)

last_row = data_ws.Cells(data_ws.Rows.Count, 1).End(xlUp).Row

'Col_Letter is a function which returns "B" in this case
last_col = Col_Letter(data_ws.Cells(1, data_ws.Columns.Count).End(xlToLeft).Column)

sql_str = "INSERT INTO [" & tabela & "] * SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=" & excel_path & ";HDR=YES','SELECT * FROM [" & sheet_name & "$A1:" & last_col & last_row & "]')"

Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
    cmd.CommandText = sql_str
    cmd.Execute

cnn.Close

Upvotes: 0

Views: 400

Answers (1)

CHill60
CHill60

Reputation: 2033

The * that the error refers to is the first * in this

sql_str = "INSERT INTO [" & tabela & "] * SELECT * ...

It should not be there. I.e. the code should read

sql_str = "INSERT INTO [" & tabela & "] SELECT *

Upvotes: 1

Related Questions