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 |
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 |