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?
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?