CODEWITHSUNDEEP
sql-serverexcelvba
Gray Meiring
Gray Meiring

Reputation: 431

Run SQL Script (pre generated in Excel) in SQL Server Database, from Excel

I have generated an Excel report with two fields, ETA (Col H), and Supplier Invoice Number (Col I).

The spread sheet runs out data, and, and my client simply needs to make changes within these two fields. Then in Col J, I have an Excel string formula that combines the various fields, and created the required SQL script.

Currently my client then copies all text from Col J, and pastes it into SQL Server Management Studio, then runs the queries, thus updating the SQL tables. Simple enough.

However, I would like to avoid my client having to run these scripts in SQL Server. Rather, I am looking for advice or a way that they can, from within Excel, click a button, and Col J to be automatically copied, and run in SQL Server, all from Excel (perhaps with a button, with some form of Macro).

Is this possible?

Below is a screen grab of the Excel report, showing to auto generated SQL Query in col J.

Please could I be assisted in building this feature in Excel, perhaps via VBA?

enter image description here

I hope I have explained my requirements correctly?

Thank you!

Upvotes: 0

Views: 403

Answers (1)

CDP1802
CDP1802

Reputation: 16267

You could create a text file and run the query using the command line utility SQLCMD, or use an ADODB connection.

Option Explicit
Sub test()

    Const METHOD = 1 '1=cmdsql 2=ADODB
    Const SERVER = "test\sqlexpress"
    Const DATABASE = "test"

    Dim fso As Object, ts As Object, ar
    Dim ws As Worksheet
    Dim iLastRow As Long, i As Long
    Dim sql As String, timestamp As String
    Dim Folder As String, SQLfile As String, LOGfile As String
    Dim t0 As String: t0 = Timer

    ' query file and log filenames
    timestamp = Format(Now, "YYYYMMDD_HHMMSS")
    Folder = ThisWorkbook.Path & "\"
    SQLfile = timestamp & ".sql"
    LOGfile = timestamp & ".log"

    Set fso = CreateObject("Scripting.FileSystemObject")
  
    ' read data from sheet into array to build sql file
    Set ws = ThisWorkbook.Sheets("Sheet1")
    iLastRow = ws.Cells(Rows.Count, "J").End(xlUp).Row
    If iLastRow = 1 Then
        MsgBox "No data in Column J", vbCritical
        Exit Sub
    End If
    ar = ws.Range("J2").Resize(iLastRow - 1).Value2

    ' connect to server and run query
    If METHOD = 1 Then ' SQLCMD

        ' create sql file
        Set ts = fso.CreateTextFile(SQLfile)
        For i = 1 To UBound(ar)
            sql = sql & ar(i, 1) & vbCr
        Next
        ts.write sql
        ts.Close
        
        ' execute sql using slqcmd
        Dim wsh As Object, sCommandToRun As String
        Set wsh = VBA.CreateObject("WScript.Shell")
    
        LOGfile = timestamp & ".log"
        sCommandToRun = "sqlcmd -S " & SERVER & " -d " & DATABASE & _
                       " -i " & Folder & SQLfile & _
                       " -o " & Folder & LOGfile
            
        wsh.Run sCommandToRun, 1, 1
        MsgBox "See CMDSQL log file " & LOGfile, vbInformation, Format(Timer - t0, "0.0 secs")

    ElseIf METHOD = 2 Then 'ADODB
 
        Dim sConn As String, conn, cmd, n As Long
        sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
                ";Initial Catalog=" & DATABASE & _
                ";Trusted_Connection=yes;"
    
        ' open log file
        Set ts = fso.CreateTextFile(LOGfile)

        ' make connection
        Set conn = CreateObject("ADODB.Connection")
        conn.Open sConn
    
        ' execute sql statements
        Set cmd = CreateObject("ADODB.Command")
        With cmd
            .ActiveConnection = conn
            For i = 1 To UBound(ar)
                ts.writeLine ar(i, 1)
                .CommandText = ar(i, 1)
                .Execute
            Next
        End With
        ts.Close
        conn.Close
        MsgBox UBound(ar) & " SQL queries completed (ADODB)", vbInformation, Format(Timer - t0, "0.0 secs")

    End If
End Sub

Upvotes: 1

Related Questions