home

-- Excel - Excel Process SQL Statements
Author Nigel Rivett

Sub InsertData()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim irow As Integer
Dim sql As String
 
    con.ConnectionString = "Driver={SQL Server};Server=MyServer\MyInstance;Database=MyDB;Trusted_Connection=yes"
    con.Open
    cmd.CommandType = adCmdText
    cmd.ActiveConnection = con

Dim wks As Excel.Worksheet
Dim sql As String

    Set appExcel = Excel.Application
    For Each wks In appExcel.Worksheets
       If wks.Cells(1, 1).Value = "--SQL Script" Then
           irow = 2			
           Do While wks.Cells(irow, 1).Value <> "--SQL Script End"
               sql = wks.Cells(irow, 1).Value
               sql = sql & "'" + Replace(Trim(wks.Cells(irow, icol).Value), "'", "''") + "',"
               cmd.CommandText = sql
                    
               cmd.Execute
               irow = irow + 1
           Loop
       End If
    Next


home