Excel VBA to import into SQL Server 2008

[Biz]

Hi,

I am new to using Excel VBA to import into SQL Server 2008.

Currently I have 17 files to upload up to 1000 rows manually. Is there a VBA that can load an excel file directly into SQL?
It would be faster then loading 1 file at time in SQL import.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I was thinking having named range and having SharePoint addresses of xlsm /xlsx files and looping it through.
<o> </o>
Any suggestions would be greatly appreciated.

Biz

 

[Biz]

Got question if I am using Excel 2007 what VBA code do I use to upload it to SQL Server 2008.

 

[Biz]

Hi,

Guys I have played around with code but it does not work.
The error messgae I get is The OLE provider "Microsoft.Ace.OLED.12.0" has not been registered.

I have tried everything as mentioned in the article below
http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/

Does anyone have any suggestion? Pls note I am using xlsm and xlsx files and importing named range Excel_Fcast into SQL Server 2008

Sub UploadFromExcelToSQL()
    Dim cn As ADODB.Connection
    Dim CommandText As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
      
    cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=FinToolTestWed;Data Source=AMAAPP02;Use " _
        , "Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AMA0149;Use Encryption for Data=False;Tag with colum" _
        , "n collation when possible=False"
 
    'Import by using OPENDATASOURCE.
    CommandText = "SELECT * INTO Excel_Fcast FROM " & _
        "OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', " & _
        "'Data Source=H:\SQL Server upload\38102-SpecialProjects-Forecast Test v3.4.xlsm;" & _
        "Extended Properties=Excel 12.0')...[Excel_Fcast$]"
    Debug.Print strSQL
    cn.Execute CommandText, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
    cn.Close
    Set cn = Nothing
End Sub

Biz

 

[dk]

Hi

Does this help?
http://stackoverflow.com/questions/...ledb-12-0-provider-is-not-registered-resolved - in particular, ensuring that you have the correct Access 12 driver installed on the server that SQL server is sat on.

Btw, I successfully managed to get the Excel 2003 version of that code to work but I don't have 2007 on my work computer so cannot test that version. Nice technique though!

Also, you are using OPENDATASOURCE whereas the article refers to OPENROWSET. I don't think this is the reason for your error but it will probably cause problems later on if you don't change it.

DK

 

[Fazza]

Hi.

Google led me to http://www.ozgrid.com/forum/showthread.php?t=51440&pagenumber=

I don't know if it helps! I never work with SQL server.

F

 

[Fazza]

I guess you've seen http://support.microsoft.com/kb/321686

 

[Biz]

Hi.

Google led me to http://www.ozgrid.com/forum/showthread.php?t=51440&pagenumber=

I don't know if it helps! I never work with SQL server.

F

Hi Fazza,

I am importing the Forecast Worksheet tab to Excel_Fcast table in SQL Server 2008.

I tried your link and made code below.

What do I use for [odbc;Driver={SQL Server};?

Sub test()

   Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
   On Error GoTo test_Error
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=H:\SQL Server upload\38102-SpecialProjects-Forecast Test v3.4.xlsm;" & _
        "Extended Properties=Excel 12.0"
    
    'Import by using Jet Provider.
    strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
        "Server=AMAAPP02;Database=Excel_Fcast;" & _
        "Workstation ID=AMA0149;PWD=""].tbl_Excel_Fcast" & _
        "Select * FROM [Forecast$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing
   On Error GoTo 0
   Exit Sub
test_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"
End Sub

Biz

 

[Fazza]

Hi.

Google led me to http://www.ozgrid.com/forum/showthread.php?t=51440&pagenumber=

I don't know if it helps! I never work with SQL server.

F

I don't know. I never work with SQL server. I used google to find the link. It is not my code. I think best I avoid trying to help further 'cause I really don't know anything about this. regards

 

[Biz]

I don't know. I never work with SQL server. I used google to find the link. It is not my code. I think best I avoid trying to help further 'cause I really don't know anything about this. regards

It’s kewl mate.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Biz]

Hi

Does this help?
http://stackoverflow.com/questions/...ledb-12-0-provider-is-not-registered-resolved - in particular, ensuring that you have the correct Access 12 driver installed on the server that SQL server is sat on.

Btw, I successfully managed to get the Excel 2003 version of that code to work but I don't have 2007 on my work computer so cannot test that version. Nice technique though!

Also, you are using OPENDATASOURCE whereas the article refers to OPENROWSET. I don't think this is the reason for your error but it will probably cause problems later on if you don't change it.

DK

The reason I'm using importing entire worksheet but I need named range Excel_Fcast.

Could I post Xl 2003 code so I can see if I can extract new ideas to make this work?

Biz

 

[dk]

The reason I'm using importing entire worksheet but I need named range Excel_Fcast.

Could I post Xl 2003 code so I can see if I can extract new ideas to make this work?

Biz

I think you were on the right track with what you were doing earler. I have successfully tested the code below using an Excel 2003 file and SQL Server 2000 database. It will copy data from the named range Forecast and create a new table on the SQL database named FORECAST_TABLE. I believe that the same code could also be used to copy data from an Excel 2003 file to a SQL Server 2005 or 2008 database table, although I cannot test this as I don't have either of those database available for testing.

Do you have access to the SQL Query Analyzer application? If so, you can test the query part without using VBA by just doing something like this:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\temp\test.xls',
'select * from Forecast');

Once you have got the query working in Query Analyzer then you will be able to implement it in your VBA. In order to get it to work with Excel 2007 files you will need to change the connection string e.g.

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\temp\test.xlsx',
'SELECT * FROM Forecast');

However, if you try that then you will most likely come back to the issue of it complaining that it cannot find te ACE.OLEDB.12 driver and if that is the case then you will need to refer back to what I posted earlier.

Anyway, here's the code for pulling the data from an XLS file.

Sub CopyFromExcelToSQLServer()

Dim cn As ADODB.Connection
Dim sConn As String
Dim cmd As ADODB.Command
Dim sSQL As String

Set cn = CreateObject("ADODB.Connection")

'I have changed my actual connection details to x's - you will need to update for your actual connection.
sConn = "Provider=sqloledb;Server=xxx;Database=NORTHWIND;User Id=xx;Password=xx"

cn.Open sConn

sSQL = "SELECT * INTO FORECAST_TABLE FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
        "'Excel 8.0;HDR=YES;Database=C:\temp\test.xls'," & _
        "'select * from Forecast');"

cn.Execute sSQL

cn.Close
Set cn = Nothing

End Sub