Excel VBA Script

Uploading data to SQL from Excel (this example uploads a spreadsheet of forecast data)

Based on this kind of data;

TransDate ForecastModel ItemType ItemValue
01/09/2017 2017/18 A Thing 123.456
02/09/2017 2017/18 A Thing 234.567
03/09/2017 2017/18 A Thing 345.678

Sub UloadSqlServer()

‘nb Library added to enable this, done through Tools -> References = ‘Microsoft ActiveX Data Objects 2.8 Library’
‘select the relevant sheet to gather variables for SQL
Sheets(“SQL UPLOAD”).Select

Dim Svr As String
Dim tstSVR As String

Dim Dbse As String
Dim Tbl As String
Dim Fcastnme As String
Dim Outsht As String
Dim Tbl2 As String

‘cell L1 in sheet has server name
Svr = Cells(1, 12)

‘cell N1 in sheet has Test server name

tstSVR = Cells(1, 14)

‘cell L2 in sheet has Database name
Dbse = Cells(2, 12)

‘cell L3 in sheet has Database Table name
Tbl = Cells(3, 12)

‘cell L5 in sheet has Forecast name
Fcastnme = Cells(4, 12)

‘cell L6 in sheet has the excel output sheet name
Outsht = Cells(5, 12)

‘cell O1 in sheet has the Test Database server name
Tbl2 = Cells(14, 12)
‘the SQL bit…

Dim conn As adodb.Connection
Dim rs As adodb.Recordset
Dim sConnString As String

‘ Create the connection string.
sConnString = “Provider=SQLOLEDB;Data Source=” & tstSVR & “;” & _
“Initial Catalog=” & Dbse & “;” & _
“Integrated Security=SSPI;”

‘ Create the Connection and Recordset objects.
Set conn = New adodb.Connection
Set rs = New adodb.Recordset

conn.ConnectionTimeout = 600

‘ Open the connection and execute.
conn.Open sConnString

‘grab the data from the spreadsheet

startrow = 3
startcolumn = 1
‘deleting out anything in the database
ModelToDelete = Trim(Cells(startrow, 2).Value)

commandstring = “DELETE FROM ” & Tbl2 & ” WHERE ltrim(rtrim(FORECASTMODEL)) = ‘” & ModelToDelete & “‘”
conn.Execute commandstring

While Len(Trim(Cells(startrow, startcolumn).Value)) > 0

‘read data to memory

Transdate = Format(Cells(startrow, 1).Value, “dd mmm yyyy”)
FORECASTMODEL = Cells(startrow, 2).Value
ItemType = Cells(startrow, 3).Value
ItemValue = Cells(startrow, 4).Value

‘write it to database

commandstring = “INSERT INTO ” & Tbl2 & ” (transdate,ForecastModel,ItemType,ItemValue) Values (”
commandstring = commandstring & “‘” & Transdate & “‘,”
commandstring = commandstring & “‘” & FORECASTMODEL & “‘,”
commandstring = commandstring & “‘” & ItemType & “‘,”
commandstring = commandstring & “” & ItemValue & “)”
conn.Execute commandstring

‘move onto next row

startrow = startrow + 1

Wend

‘ Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

Qlik Sense & Excel Help Site

%d bloggers like this: