Reputation: 33
I am new in Excel VBA and SQL. I have managed to create a macro button and push Excel cell data to SQL server tables. However, I am a bit puzzled:
How can I take Excel cell data from different sheets and then push them to different tables in SQL Server database? (Currently, I have 3 sheets - Customers, Test, Information - in one Excel file.)
Current working code:
Sub Button1_Click()
Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim sCustomerId, sFirstName, sLastName As String
With Sheets("Customers")
'Open a connection to SQL Server
conn.Open "Provider=SQLOLEDB;Data Source=TESTpc\SQLEXPRESS;Initial Catalog=ExcelSQLServerDemo;Trusted_connection=yes"
'Skip the header row
iRowNo = 2
'Loop until empty cell in CustomerId
Do Until .Cells(iRowNo, 1) = ""
sCustomerId = .Cells(iRowNo, 1)
sFirstName = .Cells(iRowNo, 2)
sLastName = .Cells(iRowNo, 3)
'Generate and execute sql statement to import the excel rows to SQL Server table
conn.Execute "INSERT into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"
iRowNo = iRowNo + 1
Loop
MsgBox "Customers Exported To Database"
conn.Close
Set conn = Nothing
End With
End Sub
Do I need to store the data in arrays and then push them to the database?
Upvotes: 1
Views: 220
Reputation: 32642
You shouldn't use insert queries for every row you want to export. Instead, if you want to do it manually, open a recordset:
Sub Button1_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim iRowNo As Integer
Dim sCustomerId, sFirstName, sLastName As String
With Sheets("Customers")
'Open a connection to SQL Server
conn.Open "Provider=SQLOLEDB;Data Source=TESTpc\SQLEXPRESS;Initial Catalog=ExcelSQLServerDemo;Trusted_connection=yes"
conn.CursorLocation = adUseClient 'Use a client-side cursor
rs.Open "SELECT * FROM dbo.Customers", conn, adOpenDynamic, adLockOptimistic 'Open the table into a recordset
'Skip the header row
iRowNo = 2
'Loop until empty cell in CustomerId
Do Until .Cells(iRowNo, 1) = ""
rs.AddNew 'Add a new row
rs!CustomerId = .Cells(iRowNo, 1) 'Set row values
rs!FirstName = .Cells(iRowNo, 2)
rs!LastName = .Cells(iRowNo, 3)
rs.Update 'Commit changes to database, you can try running this once, or once every X rows
iRowNo = iRowNo + 1
Loop
MsgBox "Customers Exported To Database"
conn.Close
Set conn = Nothing
End With
End Sub
This has several advantages, including but not limited to increased performance, the ability to insert quoted values and increased stability.
Upvotes: 1
Reputation: 135
Use Sql Server Import and Export Data 64 Bit or 32 Bit
Follow the next steps
Upvotes: 0