import Excel worksheet to SQL Server table
ryguy7272,
I used your code got it to work. However, now I'm getting an error
everywhere in my application that I have never seen before.
"Code execution has been interrupted"
How can I get rid of this. I have closed my file and the sql server, and
reopened the excel file and started to run my app. Now I'm getting this
error constantly.
what can I do?
Dennis
"DennisB" wrote:
Thank you. I got the code to work for deleting the records. however, I have
a 16 column and 5,000 record table in Excel that I want to append to an
existing table. I have a range variable for the whole table and I want to
append the range to the SQL table. The SQL table has the same field names
and datatypes.
Is there a quick way to do this or do I have to loop through each range
value (16 per row) and then loop through the rows to update my SQL table?
"ryguy7272" wrote:
I know I just posted here; not seeing it now though so I'll try once more.
Sub Rectangle1_Click()
'TRUSTED CONNECTION
On Error GoTo errH
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strPath As String
Dim intImportRow As Integer
Dim strFirstName, strLastName As String
Dim server, username, password, table, database As String
With Sheets("Sheet1")
server = .TextBox1.Text
table = .TextBox4.Text
database = .TextBox5.Text
If con.State < 1 Then
con.Open "Provider=SQLOLEDB;Data Source=" & server &
";Initial Catalog=" & database & ";Integrated Security=SSPI;"
'con.Open
End If
'this is the TRUSTED connection string
Set rs.ActiveConnection = con
'delete all records first if checkbox checked
If .CheckBox1 Then
con.Execute "delete from tbl_demo"
End If
'set first row with records to import
'you could also just loop thru a range if you want.
intImportRow = 10
Do Until .Cells(intImportRow, 1) = ""
strFirstName = .Cells(intImportRow, 1)
strLastName = .Cells(intImportRow, 2)
'insert row into database
con.Execute "insert into tbl_demo (firstname, lastname)
values ('" & strFirstName & "', '" & strLastName & "')"
intImportRow = intImportRow + 1
Loop
MsgBox "Done importing", vbInformation
con.Close
Set con = Nothing
End With
Exit Sub
errH:
MsgBox Err.Description
End Sub
Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There
are many ways to do this. Inputs can come from cells, or be hardcoded,
whatever you prefer.
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"AB" wrote:
You can try reading the data in excel and pushing the data into the
server either as APPEND queries (sql statements) (via
connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out
and see if you gain any speed advantage. I would not be that surprised
if Server Management Studios native solution is faster than any 'home
made' but give it a shot - who knows.
I'm always pushing the data via the two above methods (append query or
recordset.addnew) so I wouldn't have a clue how it compares to the
wizard.
On May 1, 4:22 pm, DennisB wrote:
I have an ADO connection to SQL Server 2005 and I use SQL Server Management
Studio's import wizard to import Excel sheets to tables. This is very time
consuming and I was wondering what would be the best way to import the files
to SQL using VBA from Excel. I currently call various stored procedures from
VBA.
Any code snippets or help?
DennisB
.
|