Results 1 to 4 of 4

Thread: Upload Excel Data to SQL Table

  1. #1
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    13

    Upload Excel Data to SQL Table

    Code:
    Sub ExceltoSQLUpload(gc_strServerAddress As String, gc_strDatabase As String, strTableName As String) 
        Dim Cnn             As Object 
        Dim wbkOpen         As Workbook 
        Dim fd                          As FileDialog 
        Dim objfl                       As Variant 
        Dim rngName                     As Range 
         
        Set fd = Application.FileDialog(msoFileDialogOpen) 
        With fd 
            .ButtonName = "Open" 
            .AllowMultiSelect = False 
            .Filters.Add "Text Files", "*.xlsm,*.xlsx;*.xls", 1 
            .Title = "Select Raw Data File...." 
            .InitialView = msoFileDialogViewThumbnail 
            If .Show = -1 Then 
                For Each objfl In .SelectedItems 
                    .Execute 
                Next objfl 
            End If 
            On Error Goto 0 
        End With 
        Set wbkOpen = ActiveWorkbook 
        Set fd = Nothing 
        Set rngName = Application.InputBox("Select Range to Upload in newly opended file", , , , , , , 8) 
        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=" & gc_strServerAddress & ";Database=" & gc_strDatabase & "]." & strTableName 
        nJOIN = " SELECT * from [TempRange]" 
        Cnn.Execute nSQL & nJOIN 
        MsgBox "Uploaded Successfully", vbInformation, "Say Thank you to me" 
         
        wbkOpen.Close 
        Set wbkOpen = Nothing 
    End Sub
    How to Use:

    Code:
    Sub CheckUpload() 
         '==Precaution: Please Make sure Table Exists and Raw Data Headers must be Same as Table Headers====
         
         'Call ExceltoSQLUpload("ServerName", "Database Name", "Table Name")
        Call ExceltoSQLUpload("xxxxx", "yyyy", "zzzzz") 
    End Sub

  2. #2
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13
    Littleiitin

    This is quite useful - I have not tried the code yet - but I will - but properly write to Access tables - I assume the code will work the same - only the connection string is different. Guess I will find out soon enough.

    Do you have a full code example that both reads & writes to a table already in place as well as creates a new table if needed.
    xl2007 - Windows 7
    xl hates the 255 number

  3. #3

  4. #4
    Junior Member
    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    A good choice is to upload data using tools as process runner which helps you to manage manual data efficiently. Manufactured by Innowera this software allows automated SAP excel data migration.

Similar Threads

  1. Export data from Excel to Access Table (ADO) using VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-24-2015, 07:53 PM
  2. SQL output from Excel VBA macro
    By goldenbutter in forum Excel Help
    Replies: 3
    Last Post: 05-07-2013, 08:07 PM
  3. SQL in Excel
    By Nishant Choudhary in forum Excel Help
    Replies: 2
    Last Post: 01-24-2012, 05:55 AM
  4. Execute SQL From Excel (VBA)
    By Mechanic in forum Excel and VBA Tips and Tricks
    Replies: 8
    Last Post: 10-02-2011, 04:30 PM
  5. Execute SQL From Excel (VBA)
    By Mechanic in forum Excel Help
    Replies: 0
    Last Post: 05-13-2011, 10:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •