Solved

Cross Tab Query Export to Excel with Formatting

Posted on 2007-03-21
3
713 Views
Last Modified: 2013-11-27
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
Comment
Question by:davidkohne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 18770114
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
 

Author Comment

by:davidkohne
ID: 18771700
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
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 250 total points
ID: 18771859
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question