Hello
I'm using the following code to export a crosstab query to an excel template. One of the fields doesn't have any data but I still want it to show up in the export. How do I update my code so it doesn't exclude the field that contains all zero's?
Code:
Public Sub ExportTTM()
Dim DB As Database
Dim qdf As DAO.QueryDef
Dim rst1, rst2 As Recordset
Dim intfields As Integer, i As Integer
Dim ex As New Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim rg1 As Range
Dim d As String, p As String, dt As String
Set DB = CurrentDb()
Set qdf = DB.QueryDefs("qCTSel_Rolling Trend_Export")
qdf.Parameters("[Forms]![f_MTD-YTD_Counts]![Selected Date]") = [Forms]![f_MTD-YTD_Counts]![Selected Date]
Set rst1 = qdf.OpenRecordset
d = "\\...\Shared\COMMON\Compliance - Reportables\Compliance Database\Templates\"
Set wb = Workbooks.Open(d & "Rolling Trend_Template.xlsx")
Set ws = wb.Sheets("Data")
Set rg1 = ws.Range("A2")
intfields = rst1.Fields.Count
For i = 1 To intfields
With ws
.Cells(1, i) = rst1.Fields(i - 1).Name
End With
Next i
rg1.CopyFromRecordset rst1
ws.Columns("A:M").Font.Size = 9
ws.Columns("A:M").EntireColumn.AutoFit
ws.Columns("B:M").HorizontalAlignment = xlCenter
dt = Format((Now), "yyyymmdd_hhmm")
p = "\\...\Shared\COMMON\Compliance - Reportables\Compliance Database\"
rst1.Close
Set rst1 = Nothing
wb.SaveAs p & "Rolling Trend_" & dt, , , , False
wb.Close
ex.Quit