none
regarding connection between excel and sql server RRS feed

  • Question

  • dears 

    good greeting

    how can make a connection between excel sheet and sql server 

    i have a userform vba and i want to export all excel datasheet from excel to sql server

    to let the userform connect directly to the sql sever and made the save and edit to be save inside sql not inside excel

    is there any way to made that ?


    Tuesday, March 13, 2018 7:42 PM

Answers

  • Here are a couple of ways to push data from Excel into a SQL Server table.

    Sub UpdateTable()
    
        Dim cnn As Object
        Dim wbkOpen As Workbook
        Dim objfl As Variant
        Dim rngName As Range
        Workbooks.Open "C:\Users\Excel\Desktop\Excel_to_SQL_Server.xls"
        Set wbkOpen = ActiveWorkbook
        Sheets("Sheet1").Select
        Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
        rngName.Name = "TempRange"
        strFileName = wbkOpen.FullName
        Set cnn = CreateObject("ADODB.Connection")
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
        nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Excel-PC\SQLEXPRESS;Database=[Northwind].[dbo].[TBL]]"
        nJOIN = " SELECT * from [TempRange]"
        cnn.Execute nSQL & nJOIN
        MsgBox "Uploaded Successfully"
        wbkOpen.Close
        Set wbkOpen = Nothing
        
    End Sub
    
    Sub InsertInto()
    
    'Declare some variables
    Dim cnn As adodb.Connection
    Dim cmd As adodb.Command
    Dim strSQL As String
    
    'Create a new Connection object
    Set cnn = New adodb.Connection
    
    'Set the connection string
    cnn.ConnectionString = "Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;"
    
    'Create a new Command object
    Set cmd = New adodb.Command
    
    'Open the connection
    cnn.Open
    'Associate the command with the connection
    cmd.ActiveConnection = cnn
    
    'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
    cmd.CommandType = adCmdText
    
    'Create the SQL
    strSQL = "UPDATE TBL SET JOIN_DT = 2013-01-13 WHERE EMPID = 2"
    
    'Pass the SQL to the Command object
    cmd.CommandText = strSQL
    
    'Open the Connection to the database
    cnn.Open
    
    'Execute the bit of SQL to update the database
    cmd.Execute
    
    'Close the connection again
    cnn.Close
    
    'Remove the objects
    Set cmd = Nothing
    Set cnn = Nothing
    
    End Sub
    

    Here are a few ways to grab data from Excel and move it into SQL Server.

    SELECT * 
    FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
      'Data Source=C:\DataFiles\EmployeeData1.xlsx;
       Extended Properties=Excel 12.0 Xml')...[vEmployee$];
    
    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=YES;
       Database=C:\DataFiles\EmployeeData1.xlsx',
       [vEmployee$]);
    
    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=NO;
       Database=C:\DataFiles\EmployeeData1.xlsx',
       [vEmployee$]);
    
    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=YES;
       Database=C:\DataFiles\EmployeeData2.xlsx',
       [vEmployee$]);
    
    SELECT * INTO EmployeeInfo1
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=YES;
       Database=C:\DataFiles\EmployeeData1.xlsx',
       [vEmployee$]);
    
    

    See this for more info.

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/



    Finally, please take a look at the links below for additional ideas of how to integrate Excel and SQL Server.

    https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction

    http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx


    MY BOOK

    • Proposed as answer by Terry Xu - MSFT Wednesday, March 14, 2018 3:11 AM
    • Marked as answer by TAREK SHARAF Wednesday, March 14, 2018 4:28 AM
    Wednesday, March 14, 2018 1:47 AM

All replies

  • Here are a couple of ways to push data from Excel into a SQL Server table.

    Sub UpdateTable()
    
        Dim cnn As Object
        Dim wbkOpen As Workbook
        Dim objfl As Variant
        Dim rngName As Range
        Workbooks.Open "C:\Users\Excel\Desktop\Excel_to_SQL_Server.xls"
        Set wbkOpen = ActiveWorkbook
        Sheets("Sheet1").Select
        Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
        rngName.Name = "TempRange"
        strFileName = wbkOpen.FullName
        Set cnn = CreateObject("ADODB.Connection")
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
        nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Excel-PC\SQLEXPRESS;Database=[Northwind].[dbo].[TBL]]"
        nJOIN = " SELECT * from [TempRange]"
        cnn.Execute nSQL & nJOIN
        MsgBox "Uploaded Successfully"
        wbkOpen.Close
        Set wbkOpen = Nothing
        
    End Sub
    
    Sub InsertInto()
    
    'Declare some variables
    Dim cnn As adodb.Connection
    Dim cmd As adodb.Command
    Dim strSQL As String
    
    'Create a new Connection object
    Set cnn = New adodb.Connection
    
    'Set the connection string
    cnn.ConnectionString = "Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;"
    
    'Create a new Command object
    Set cmd = New adodb.Command
    
    'Open the connection
    cnn.Open
    'Associate the command with the connection
    cmd.ActiveConnection = cnn
    
    'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
    cmd.CommandType = adCmdText
    
    'Create the SQL
    strSQL = "UPDATE TBL SET JOIN_DT = 2013-01-13 WHERE EMPID = 2"
    
    'Pass the SQL to the Command object
    cmd.CommandText = strSQL
    
    'Open the Connection to the database
    cnn.Open
    
    'Execute the bit of SQL to update the database
    cmd.Execute
    
    'Close the connection again
    cnn.Close
    
    'Remove the objects
    Set cmd = Nothing
    Set cnn = Nothing
    
    End Sub
    

    Here are a few ways to grab data from Excel and move it into SQL Server.

    SELECT * 
    FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
      'Data Source=C:\DataFiles\EmployeeData1.xlsx;
       Extended Properties=Excel 12.0 Xml')...[vEmployee$];
    
    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=YES;
       Database=C:\DataFiles\EmployeeData1.xlsx',
       [vEmployee$]);
    
    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=NO;
       Database=C:\DataFiles\EmployeeData1.xlsx',
       [vEmployee$]);
    
    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=YES;
       Database=C:\DataFiles\EmployeeData2.xlsx',
       [vEmployee$]);
    
    SELECT * INTO EmployeeInfo1
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0 Xml; HDR=YES;
       Database=C:\DataFiles\EmployeeData1.xlsx',
       [vEmployee$]);
    
    

    See this for more info.

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/



    Finally, please take a look at the links below for additional ideas of how to integrate Excel and SQL Server.

    https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction

    http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx


    MY BOOK

    • Proposed as answer by Terry Xu - MSFT Wednesday, March 14, 2018 3:11 AM
    • Marked as answer by TAREK SHARAF Wednesday, March 14, 2018 4:28 AM
    Wednesday, March 14, 2018 1:47 AM
  • thanks too much my dear

    Wednesday, March 14, 2018 4:29 AM