'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()
Sheets("New").Columns("A").ClearContents
Sheets("New").Columns("B").ClearContents
'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
ThisWorkbook.Worksheets("New").Select
'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'
ThisWorkbook.Worksheets("sqlResult").Select
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
'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()
Sheets("New").Columns("A").ClearContents
Sheets("New").Columns("B").ClearContents
'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
ThisWorkbook.Worksheets("New").Select
'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'
ThisWorkbook.Worksheets("sqlResult").Select
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
