SQL Union in VBA

[paulmc1981]

Hi,

I have used some code to join 3 spreadsheets and place these into 1 workbook (Using 3 sql statements joined by UNION):

Dim strConnection As String
Dim strQuery As String
Dim objConnection As Object
Dim objRecordSet As Object


strConnection = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;" & _
"Data Source='" & ThisWorkbook.FullName & "';" & _
"Mode=Read;" & _
"Extended Properties=""Excel 12.0 Macro;"";"


strQuery = _
"SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " '& _
'"ORDER BY Agents;"


Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open strConnection
Set objRecordSet = objConnection.Execute(strQuery)
RecordSetToWorksheet Sheets(1), objRecordSet
objConnection.Close


What I need sounds quite simple, but I am not sure how to achieve this:

In essence, I need the 3 parts to the SQL to do the following:


SQL 1:

If X1 = "Yes" then do not execute the first statement, but continue to SQL 2

If X1 = "No" then:

"SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _

SQL 2:

If X2 = "Yes" then do not execute the second statement, but continue to SQL 3

If X2 = "No" then:

"SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _

SQL 3:

If X3 = "Yes" then do not execute the trird statement

If X3 = "No" then:

"SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " '& _
'"ORDER BY Agents;


Once all 1, 2 or 3 SQL statements have ran, the VBA can continue to populate my spreadsheet with the data as per the rest and the original code, only returning the results where the SQL was able to execute.

Hopefully that makes sense, but if not, please let me know?

 

[mse330]

Re: Help with SQL Union in VBA

This is one way you can do it ... Try the below code

Dim SQL1 As String, SQL2 As String, SQL3 As String

If X1 = "No" Then
SQL1 = "SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] "
End If

If X2 = "No" Then
SQL2 = "SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] "
End If

If X3 = "No" Then
SQL3 = "SELECT * FROM [Combined$] " & _
"IN 'C:\Users\paul.coan\Desktop\Station_Stats\27092017\Avaya_Stats.xlsm' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] "
End If

If Len(SQL1) > 0 And Len(SQL2) > 0 Then
    SQL1 = SQL1 & " UNION  "
End If

If Len(SQL1) > 0 And Len(SQL2) = 0 And Len(SQL3) > 0 Then
    SQL1 = SQL1 & " UNION  "
End If

If Len(SQL2) > 0 And Len(SQL3) > 0 Then
    SQL2 = SQL2 & " UNION  "
End If

strQuery = SQL1 & SQL2 & SQL3