G
Guest
I used to have one workbook: raw data on some worksheets and pivot tables
and charts on other sheets.
The raw data sheets were actually exported to the workbook from Access. Why
not do charts in Access? Because the charting formatting features inside
Access are very restrictive compared to Excel.
Everything was working ok, and I was able to simply export the Access
Queries to the workbook, and the export would replace the worksheet with an
updated worksheet.
Then I wanted to save the charts to image files (gif, jpg, etc...) with
decent resolution. (Saving as a webpage does the trick, but the resolution
is really bad on the resulting image files)
Unfortunately, the only way to do this with Excel 2007 is to write a VBA
script and export them to PNG. So that's what I did. This requires that you
save the workbook as a macro-enabled workbook.
Unfortunately, YOU CAN'T EXPORT FROM ACCESS 2007 TO A MACRO-ENABLED EXCEL
2007 WORKBOOK!
So I split the workbook in two: One with just worksheets of raw data as
exported from Access, and the other with the pivot charts + VBA scripts.
HOWEVER... ever since I changed the pivot charts to obtain their data from
another workbook... refreshing the data is UNBEARABLY SLOW!
About 30% of the time I try to refresh the pivot data, it renders my entire
computer useless, and I get "not responding" on just about every window I
have open (and I don't have many... just Outlook, IE, and maybe a file
explorer window).
Also, if it does end up refreshing, for a very very long time, I still get a
strange little status message at the bottom of the Excel 2007 window that
says: "Calculating: (1 Processor(s)): 0%. If I leave it as-is, it will
finally creep-up to 100%, and finish whatever it is that it's doing (I'm not
sure, because it seems that the refresh does happen). While this is going
on... Excel is hogging up about 90-99% of my CPU.
So my questions are:
Why would performance degrade so badly simply because your pivot charts are
based on data in another workbook instead of the same workbook? Both are in
the same folder on my harddrive.
Why can't I export from Access to a macro-enabled workbook? (It's not an
option while I'm exporting...)
and charts on other sheets.
The raw data sheets were actually exported to the workbook from Access. Why
not do charts in Access? Because the charting formatting features inside
Access are very restrictive compared to Excel.
Everything was working ok, and I was able to simply export the Access
Queries to the workbook, and the export would replace the worksheet with an
updated worksheet.
Then I wanted to save the charts to image files (gif, jpg, etc...) with
decent resolution. (Saving as a webpage does the trick, but the resolution
is really bad on the resulting image files)
Unfortunately, the only way to do this with Excel 2007 is to write a VBA
script and export them to PNG. So that's what I did. This requires that you
save the workbook as a macro-enabled workbook.
Unfortunately, YOU CAN'T EXPORT FROM ACCESS 2007 TO A MACRO-ENABLED EXCEL
2007 WORKBOOK!
So I split the workbook in two: One with just worksheets of raw data as
exported from Access, and the other with the pivot charts + VBA scripts.
HOWEVER... ever since I changed the pivot charts to obtain their data from
another workbook... refreshing the data is UNBEARABLY SLOW!
About 30% of the time I try to refresh the pivot data, it renders my entire
computer useless, and I get "not responding" on just about every window I
have open (and I don't have many... just Outlook, IE, and maybe a file
explorer window).
Also, if it does end up refreshing, for a very very long time, I still get a
strange little status message at the bottom of the Excel 2007 window that
says: "Calculating: (1 Processor(s)): 0%. If I leave it as-is, it will
finally creep-up to 100%, and finish whatever it is that it's doing (I'm not
sure, because it seems that the refresh does happen). While this is going
on... Excel is hogging up about 90-99% of my CPU.
So my questions are:
Why would performance degrade so badly simply because your pivot charts are
based on data in another workbook instead of the same workbook? Both are in
the same folder on my harddrive.
Why can't I export from Access to a macro-enabled workbook? (It's not an
option while I'm exporting...)