Export Named Range to SQL 2005 Databse

[j844929]

Hi,

I've hit a brick wall with this one. I have the code (which works) to export an Excel named range to an Access (mdb) existing database table, which I have pasted below:

Sub ExportToAccess()

     
    Dim Conn As ADODB.Connection
    Dim strSQL As String, stCon As String, stDB As String
    Dim strSQL2 As String
     
     
         'database path - currently same as this workbook
        stDB = ThisWorkbook.Path & Database
        stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=ServerToUse; Initial Catalog=DatabaseName; User Id=MyUsername; Password=MyPassword;" & ";"
         
         'SQL code for GL Insert to Access
        strSQL = "INSERT INTO TableName SELECT * FROM [Named_Range$] IN '" _
        & ThisWorkbook.FullName & "' 'Excel 8.0;'"
         
         'set connection variable
        Set cnt = New ADODB.Connection
         
         'open connection to Access db and run the SQL
        With Conn
            .Open stCon
            .CursorLocation = adUseClient
            .Execute (strSQL)
        End With
         
         'close connection
        Conn.Close
         
         'release object from memory
        Set Conn = Nothing
    

End Sub

What I'm stuck on is changing this to export to a SQL 2005 database table. Can anyone tell me how to export an Excel named range to SQL?

Thanks for your time.

Tim

 

[j844929]

Brick wall knocked down!!

SOLUTION FOR HOW TO EXPORT DATA FROM A NAMED RANGE IN EXCEL (2003) TO AN SQL DATABASE TABLE:

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Source.xls;" & _
            "Extended Properties=Excel 8.0"
        
        'Import by using Jet Provider.
        strSQL = "INSERT INTO [odbc;Driver={SQL Server};" & _
            "Server=<ServerName>;Database=<DBName>;" & _
            "UID=<MyUsername>;PWD=<MyPassword>].DatabaseTableName" & _
            "SELECT * FROM [Named_Range]"
        'Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        'Debug.Print "Records affected: " & lngRecsAff
            
        cn.Close
        Set cn = Nothing

The debug.print lines are commented out, as they're for testing. Simpl replace all of the sections in <> brackets with your own required details. Hope this helps people out who are looking for a similar solution!

Tim

 

[j844929]

I just thought I'd add to my last post - it appears that text in triangle brackets within the code section doesn't appear in the post once it's submitted, so I've written them in again to make the code easier to understand.

Sub ExportToSQLDatabaseTable()


    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Source.xls;" & _
            "Extended Properties=Excel 8.0"
        
        'Import by using Jet Provider.
        strSQL = "INSERT INTO [odbc;Driver={SQL Server};" & _
            "Server=ServerName;Database=DatabaseName;" & _
            "UID=MyUsername;PWD=MyPassword].DatabaseTableName" & _
            "SELECT * FROM [Excel_Named_Range]"
        'Debug.Print strSQL
        cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
        'Debug.Print "Records affected: " & lngRecsAff
            
        cn.Close
        Set cn = Nothing

End Sub

/[code]

Hope this helps.

Tim