Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

Cross Tab Query Export to Excel with Formatting

I have a cross tab query.  I am using the query to allow users to export the data to excel.  With the cross tab, I have 'SOX Control' on the vertical access and 'Project' on the horizontal access.  With the cross tab, I have instances where 1 = True (selected by check box) and 0 = False.

Two things, is it possible to format the data before it is exported to excel, to have 1 = 'X' and 0 = 'N/A'?
The other thing, can I code the query when exported to have all cells that do not contain values (i.e., 1 or 0) to be greyed out in excel?

Thank you
0
davidkohne
Asked:
davidkohne
  • 2
1 Solution
 
Steve DubyoCommented:
Hi..

You should be able to export with your replacement values for 1 and 0, can you post your current SQL ?

To gray out certain cells in excel you are probably best to set conditional formatting within excel.
0
 
davidkohneAuthor Commented:
This is the only code that I have for the export of the Cross Tab.  I am playing with conditional formatting as we speak.  Thanks,

Private Sub cmdControlOverview_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry CrossTabSOXControl", "D:\Documents and Settings\All Users\Desktop\SOX 404 Control Log CrossTab Query.xls", -1
End Sub
0
 
Steve DubyoCommented:
Sorry, I should have been clearer.  I was asking for the SQL which makes up your query 'qry CrossTabSOXControl', if you open it up in Design View, then from the View menu, select SQL View, then post the code it shows there..

I'm thinking that in Access changing the output of the query would be more straight forward than using ado to change the recordset before exporting.

On the other hand you could do the replacement in Excel with a couple of lines of vba..

    With Range("C1:M100")   'Change to match you range of cells
          .Replace "1", "X"
          .Replace "0", "N/A"
    End With

 
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now