Issue Exporting Table From SQL to Excel

[justinua]

I've been working on a macro that exports data from a SQL table and imports it into an Excel worksheet. When I run the macro, it only brings in a portion of the data, but not all of it. Here is the code I've been using:

Sub CustCountData()    Dim Server As String
    Dim SQLString As String
    Dim MarkID As String
    Dim FCDate As String
    Dim EXDate As String
    Dim NMDate As String
    Dim PrevFCDate As String
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    
    Set ws = Sheets("Count Summary")
    Set ws2 = Sheets("Count DATA")
    Set ws3 = Sheets("Start")
    
    ws3.Range("CurrentTag").Formula = "='Count Summary'!B6"
    ws3.Range("Previousag").Formula = "='Count Summary'!D6"
    ws3.Range("CurrentTag").Value = ws3.Range("CurrentTag").Value
    ws3.Range("PreviousTag").Value = ws3.Range("PreviousTag").Value
    
    'Populate report parameters
    Server = Range("Server")
    SQLString = Range("CountQueryString3") ' Insert the name of the range/cell where the query is stored here
    MarkID = ws3.Range("MarkID")
    EXDate = Range("EXDate")
    NMDate = Range("NMDate")
    ForecastDate = Range("FCDate")
    PrevMonthForecastDate = Range("PrevFCDate")
        
    SQLString = Replace(SQLString, "@MarkID", MarketID)
    SQLString = Replace(SQLString, "@FCDate", FCDate)
    SQLString = Replace(SQLString, "@EXDate", EXDate)
    SQLString = Replace(SQLString, "@NMDate", NMDate)
    SQLString = Replace(SQLString, "@PrevFCDate", PrevFCDate)
    
With Sheets("Count DATA")
 With Sheets("Count DATA").ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=_____;UID=____;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=____;DATABASE=____" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = SQLString
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_CountData"
        .Refresh BackgroundQuery:=False
  End With
  End With


ws2.Columns("B:H").NumberFormat = "General"
ws2.Columns("A:A").NumberFormat = "m/d/yyyy"


'Changes any blank cells to
For Each cell In ws2.Range("Table_CountData")
If IsEmpty(cell) Then
cell.Value = 0
End If
Next

Here is the SQL query it is pulling:

[TABLE="width: 723"]
<tbody>[TR]
[TD]select ecd.fcast_date, ecd.type, ecd.class_code, sum(ecd.count/ecd.at_factor) as count, sum(ecd.count) as atcount, sum(ecd.con) as con, 'EX' as Type, 'Current' as TAG
from existing_con ecd
where ecd.mark_id = '@MarkID'
 and ecd.fc_date >= '@FCDate'
 group by fc_date, type, class_code
 Union All
 select ecda.fc_date, ecda.type, ecda.class_code, sum(ecda.count/ecda.at_factor) as count, sum(ecda.count) as atcount, sum(ecda.con) as con, 'EX' as Type, 'Previous' as TAG
from ca_con_archive ecda
where ecda.mark_id = '@MarkID'
  and ecda.archive_date = '@EXDate'
  and ecda.fc_date >= '@PrevFCDate'
  group by fc_date, type, class_code
order by tag, fc_date, type, class_code[/TD]
[/TR]
</tbody>[/TABLE]

When the data gets to my worksheet, it is only pulling the data from the table that gets the "Current" tab, but nothing from the table that gets the "Previous" tag. Essentially, anything after the "UNION ALL" is not pulling into Excel (although it runs correctly in SQL.) In addition, when the macro gets to: ".Refresh BackgroundQuery:=False" I get the error message "General ODBC Error". I have to change it from False to True to get the table to run. Lastly, I've tried pulling the data into a temp table 1st, but that just doesn't pull any data to my worksheet.

Does anyone know what I am doing wrong? How can i get it to pull in the data from both the "Current" and "Previous" tables?

Thanks for your help.

 

[justinua]

Is there anybody that is able to help with my question above? Thanks.