UtterAccess Discussion Forums > Exporting Reports To Excel

Full Version: Exporting Reports To Excel

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

MikeLyons

Apr 23 2012, 03:36 PM

Here is one that has come up at my work and I haven't been able so far to resolve.

We have a report that has a group header and detail section. The BackColor of the detail section is the default white, while the group header is only slightly off-white ( a faint touch of grey) just enough to visibly differentiate the two.

When the user exports the report to Excel, one column that in the report had its ForeColor set to blue, comes out in not just a light grey, but a dark grey, and the text is there but invisible.

To make it show up, the user has to manually change the cell style to "Normal". Also, when the user selects the entire column, the text is faintly visible.

Anyone seen this before and have any idea how to fix it? I have never run into this before.

Edit: Forgot to mention, same effect in both Access 2000 and 2010.

Mike

RCJT

Apr 23 2012, 04:04 PM

Can you use VBA or a Macro to change the property of that cell as part of your export/import process?

I recorded a quick Macro that looks like:

CODE

Sub mcrtest()
'
' mcrtest Macro

    Range("B3:B8").Select 'your range here
    Application.CutCopyMode = False
    With Selection.Interior 'Cell tweaks here.
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
        .PatternTintAndShade = 0
    End With
End Sub

or this VBA can be modified to include the cell property you want.

CODE

Sub SetActive()
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range("B5").Activate
    ActiveCell.Font.Bold = True
End Sub

MikeLyons

Apr 23 2012, 04:51 PM

QUOTE (RCJT @ Apr 23 2012, 02:04 PM)

Can you use VBA or a Macro to change the property of that cell as part of your export/import process?

I don't think that would work in our case -- we've just exposed the built-in Export command to the user. Access then takes over and outputs the file to whatever format and filename the user specified. They won't want to have to manually invoke yet another command in order to make the resulting Excel file readable.

But it's a moot point now, as a result of some more tinkering on my part. I discovered that somehow the BackColor property of the one textbox got changed, so even though I reduced the shading in the section, and the textbox's BackStyle is Transparent, it seems the Transparent property is not taken into consideration when exporting to Excel. I was just coming on here again to update.

Thanks anyhow for the attempt.

Mike

RCJT

Apr 23 2012, 05:22 PM

Glad you got it working! JT

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.