Results 1 to 2 of 2
  1. #1
    MissaLissa is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    2

    Missing data in export to excel template

    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

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Is the column in question displaying in your source query or is it not there because there's no data? I don't see anything obvious in your code that would exclude a column if it physically had a 0 in it (as opposed to being null). Have you got a sample database with some junk data for testing purposes you could share?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Query Results to Excel Template
    By laterdater in forum Macros
    Replies: 2
    Last Post: 09-25-2015, 11:20 AM
  2. Replies: 3
    Last Post: 07-31-2014, 01:08 AM
  3. Replies: 1
    Last Post: 10-28-2013, 12:32 PM
  4. Replies: 2
    Last Post: 03-05-2013, 10:52 PM
  5. Export table to excel using excel template
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 06-27-2011, 02:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums