SQL Union in VBA

paulmc1981

New Member
Joined
Jan 16, 2017
Messages
9
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Re: Help with SQL Union in VBA

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

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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top