Exporting to excel and formatting

[arsenal042004]

When I export a report excel launches with a file with the name of my report and the file is completely blank. What do I need to do to fix this? Same for Word..

Is there an easy way to format a simple pie chart report? If you format reports by editing..using MS Chart I think it's v8.0, then return to MS Access, the formats fonts and sizing are not the same. There must be an easier way.

Also is there a better wizard than the one in access to generate reports via a vendor as an add-in...that you would recommend? Where can I get it?
Thank you,
Todd

 

[GingerR]

charts within Access are a bit of a bear, but once you get the hang of it, they can do what you need and you can avoid going back and forth between Access and Excel. It's not that hard once you do it the first time. If you want to pursue trying to get your Access charts to work for you, let me know and I'll give you more details.

g

 

[arsenal042004]

GingerR,

Thanks for your help.

The main issue for me is that I can't export the reports to excel. Excel opens then there is nothing. I have done this before on another computer and it worked. I think that there is a problem.

Can you give me more details as you mentioned? Formatting reports, charts specifically in access is not fun.

Thanks and best regards,
Todd

 

[GingerR]

I have never exported a chart to Excel. I can export data, but not a chart. If you want to mail it or whatever, you can export as SNP (Snapshot file). Snapshot Viewer is a free utility provided by MS and is a standard at our company (100k employees).

as for building charts in Access--here are some tips:

To make the chart 'template' look like YOUR data instead of the North-South-EastWest thing so you can edit it better:

1)make a chart using the chart wizard, based on your table or query. doesn't matter what you choose cause we're going to change it. When it's done, view the properties of the chart. make sure SIZE MODE = "Stretch" then stretch out the size of your chart to what you want it to be.

2) view the ROW SOURCE (table or query the chart is based on; run it so you can see the dataset result; hit keyboard ctrl+A (Select All) then ctrl+C (Copy). get out of that. Double-click on the chart object and get to the Datasheet (looks like Excel). click in the upper-left hand corner, and hit ctrl+v (paste). Answer OK or YES or whatever it says when it says your data isn't the same size. this will put your data into the 'template' instead of the North-South-East-West stuff.

3) Close the datasheet, and look at the chart. Now it's what it will look like whenever you open it with your own data. The data will be replaced with 'new' data each time, but this way you can see what your chart will really look like so you can play with it now and alter the colors and chart type and such.

OTHER HINTS TO MAKE IT LOOK GOOD:
4) make the background of the print area white.
5) make the gridlines, if you want them, dashed and grey.
6) be aware of color-blind people (I'm serious). Try not to make two column or area or pie chunks that are next to each other, similar colors. Try to alternate light and dark colors.
7) DATA TABLE: a good thing to use--it's like an excel spreadsheet below the chart.

hope this helps get you started.

g

 

[GMoore123]

GingerR (or anyone) - Can you provide any instructions as to how to export data (no graphics) into excel while maintaining formating and grouping like that in the report?
I'm pushing data out to non-Access users that they will need to update and return to me.

Thanks, Galen

 

[HBWAL]

I cut and pasted from couple of routines but this is how I do it. I set up a master file with my charts based on a datasource. I do the calculations in access and export the data to the "source" of the excel charts. This is how I do the export. I use arrays for some of the other exports but this particular code I paste columns. If you need more help let me know. I use this several times and it is very, very fast.


strmsg = CopyColumnToSheet(objsheet, strqry, "IssueAddress", 1, 2) 'Hidden
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "Issue Id", 2, 2) 'Hidden
strmsg = strmsg & CreateHyperlink(objsheet) 'column 3
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "Project", 4, 2)
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "Severity", 5, 2)
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "DaysOpen", 6, 2)
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "Title", 7, 2)
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "Owner", 8, 2)
strmsg = strmsg & CopyColumnToSheet(objsheet, strqry, "DaysSinceModified", 9, 2)

Public Function CopyColumnToSheet(objsheet As Excel.Worksheet, strTbl As String, strField As String, intcolumn As Integer, intRows As Integer, Optional blnSkip As Boolean) As String
On Error GoTo CopyColumnToSheet_Err
'Access Automation Help Code
'This function loops through the query and places the data in cells on the spreadsheet

Dim rst As DAO.Recordset

Dim strmsg As String

Dim varData As Variant

Dim i As Integer
Set rst = CurrentDb.OpenRecordset(strTbl)
With rst
Do While Not .EOF
If IsNull(rst(strField)) Then
objsheet.Cells(intRows, intcolumn).Value = ""
Else
objsheet.Cells(intRows, intcolumn).Value = rst(strField).Value
End If
.MoveNext
intRows = intRows + 1
Loop
.Close
CopyColumnToSheet = intRows
End With

 

[HBWAL]

btw, I got this code from Sybex Access 97 Developer's handbook. I use it in Access 2002 though. If you need help formatting excel columns and such just execute a macro in excel and copy and paste the code to access. Works like a charm.

 

[GMoore123]

Thanks HBWAL -

Another solution I stumbled across was MS KB article 253639 (surprise!) which suggests exporting the report as an .rtf, opening the .rtf in Word, executing a 'Copy All' in word and pasting the result into Excel.

Works like a charm but some formatting is lost and one must be very deliberate in the report field location/sequence to keep columns aligned.

Galen