If you find any post useful then, please do share with others. Thanks!

Popular Posts


Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Tuesday, March 13, 2018

VBA: SQL Connection String and Some Data Formatting

'The below code is for data formatting to use in a SQL query
'We are using connection string to pull data using SQL query
'In comments, below, the code is explained. This is also for future reference, we can copy blocks and use

Sub Amiq()
'The above lines clear the contents of A Worksheet namely 'New' from Columns A & B
Sheets("AuditPortal").Columns("R").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("New").Columns("A"), Unique:=True
'The above line copies the column 'R' from Worksheet namely 'AuditPortal', removes duplicates  and paste it in Sheet 'New' and Column A
'Select the Sheet 'New'
'In below lines we are using For Loop and for all the Column A rows starting from 2, storing their value in variable SDest after adding single quotes and comma along
'and then along doing some more formatting i.e. Left and Len Function to remove the last comma and adding a single quote at start and finally pasting it in Column 2's first cell
SDest = ""
For iCounter = 2 To WorksheetFunction.CountA(Columns(1))
SDest = SDest & "'" & Cells(iCounter, 1).Value & "'" & ","
Next iCounter
Cells(1, 2).Value = "'" & Left(SDest, Len(SDest) - 1) & ")"

'Below is the SQL connection string. We are first selecting the sheet namely 'sqlResult'
'Our SQL is pasted in Cells(1, 2) of same sheet
'Clearing the range a5:i1000 as we want our results to appear there. A to I range is determined after looking at the columns in our select statement SQL
'Change the name of server from 'OurServer'
Dim sql
sql = Sheets("sqlResult").Cells(1, 2)
Range("a5:I1000") = ""
Dim connString
connString = "ODBC;DSN=OurServer;Description=OurServer;Trusted_Connection=Yes"
Dim thisQT As QueryTable
With ActiveSheet.QueryTables.Add(Connection:=connString, Destination:=Range("a5"))
.BackgroundQuery = False
.sql = sql
.Refresh BackgroundQuery:=True
End With
End Sub

No comments:

Post a Comment