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