Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel data to SQL Sever table
I want to automate sending Excel source data into a SQL Server database.
Here is the code that I wrote but I'm wondering if there is an easier way. I have an ADO connection to SQL Server with a database called dbbudget. Sub testCall() Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Dim sFile As String '============================================== 'this procedure is to load data from an Excel 'file into an SQL Server table 'this results in an ODBC call failed message '============================================== sFile = "D:\dbrininger\1MyWork\Projects\Development\FX S Work\Transactions\YTD_Invoices.xls" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sFile & ";" & _ "Extended Properties=Excel 8.0" 'Import by using Jet Provider. strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _ "Server=172.28.20.204;Database=dbbudget;" & _ "UID=dbrininger;PWD=test123].ImportTest " & _ "FROM [Invoices$]" cn.Execute strSQL cn.Close Set cn = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel data to SQL Sever table
Are you saying this works? If so, I have never seen SELECT INTO used in this way.
A typical insert statment: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) The pseudocode I use for moving excel rows into a database is as follows: open excel file open database connection for each excel column, create a database parameter begin transaction for each row in excel for each excel column, copy the value to the applicable parameter execute insert statement (or stored procedure), on error break out of for-next loop next if error, then rollback transaction, else ask to commit transaction close database connection close excel file Cheers, Rob DennisB wrote: I want to automate sending Excel source data into a SQL Server database. Here is the code that I wrote but I'm wondering if there is an easier way. I have an ADO connection to SQL Server with a database called dbbudget. Sub testCall() Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Dim sFile As String '============================================== 'this procedure is to load data from an Excel 'file into an SQL Server table 'this results in an ODBC call failed message '============================================== sFile = "D:\dbrininger\1MyWork\Projects\Development\FX S Work\Transactions\YTD_Invoices.xls" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sFile & ";" & _ "Extended Properties=Excel 8.0" 'Import by using Jet Provider. strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _ "Server=172.28.20.204;Database=dbbudget;" & _ "UID=dbrininger;PWD=test123].ImportTest " & _ "FROM [Invoices$]" cn.Execute strSQL cn.Close Set cn = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sever side excel automation issue | Excel Programming | |||
Excel date fields to MS SQL Sever via OLE | Excel Programming | |||
read data in an excel file on a remote sever | Excel Programming | |||
Excel Pivot Table Plugin? (crosstab to data table) | Excel Discussion (Misc queries) | |||
How to install OLE Sever | Setting up and Configuration of Excel |