Chart Formats
Posted by Mike on August 21, 2001 2:00 PM
I have an Excel 97 workbook that has several pages with a data table at the top reflecting the latest data and a chart at the bottom summarizing the data for the month. The chart at the bottom gets its data from another worksheet (including date formats). Through VBA code I copy the Worksheets, save them to a new worksheet, and email them. When the VBA code saves the worksheets I lose my x-axis (date) formats. The line of code is: ActiveWorkbook.SaveAs FileName:=MyFilename, FileFormat:=xlExcel5. The x-axis has linked to source checked. Any ideas how I can keep my date formats?
Posted by Mark W. on August 21, 2001 2:25 PM
Date formatting on the x-axis isn't supported by
Excel 5.0. You can prove this to yourself
by creating such a chart and saving it as an
Excel 5/Excel 95 document. Notice the warning
about unsupported features!!! Why are you saving
"down"?
Posted by Mike on August 22, 2001 4:28 AM
Unfortunately I inherited this project. I have tried saving as xlWorkbookNormal which is supposed to save in Excel 97 format but I got the same results. Any ideas?
Posted by Mark W. on August 22, 2001 6:24 AM
I hate to be a doubting Thomas, but I'm having a
hard time believing that this could happen with
Excel 97.
Posted by Mike on August 24, 2001 6:26 AM
Mark,
Thanks for your help. The problem is occurring in the save process. I have verified the file is in an Excel 97 format but the X-axis is still losing its date format and going to a general format. The one question I do have is since this is an embedded chart, when the sheet is copied and pasted with a pasted special (values & formats) does this apply to charts. If I don't see a response in a day or so I will repost this as it is getting far down the list. Thanks again.
Posted by Mark W. on August 24, 2001 6:46 AM
I don't follow... What formats are you pasting
and where? Keep in mind that if the x-axis
Format Axis... Number "Linked to source" check
box is checked any formatting changes made to
your data will be reflected in the chart as well. Mark,
Posted by Mike on August 24, 2001 8:56 AM
Sorry if I wasn't clear. Via code I am copying the worksheet into a new workbook. Then via code I am selecting and copying the usedrange and doing a paste special in the worksheet for values and formats (thus eliminating the formulas in the new worksheet). Things are OK at this point. Then via code I do a saveas w/fileformat=xlWorkbookNormal. When I open the new workbook the x-axis formats are gone. Do I need to do anything different since this is an embedded chart or should this work? Thanks. I don't follow... What formats are you pasting
Posted by Mark W. on August 24, 2001 10:02 AM
I'm wondering if the cell references in your
chart's SERIES functions are not referencing the
pasted data in the newly created worksheet. Check
and see. Sorry if I wasn't clear. Via code I am copying the worksheet into a new workbook. Then via code I am selecting and copying the usedrange and doing a paste special in the worksheet for values and formats (thus eliminating the formulas in the new worksheet). Things are OK at this point. Then via code I do a saveas w/fileformat=xlWorkbookNormal. When I open the new workbook the x-axis formats are gone. Do I need to do anything different since this is an embedded chart or should this work? Thanks. : I don't follow... What formats are you pasting
Posted by Mike on August 24, 2001 12:58 PM
Thanks. I'll let you know what I find. I'm wondering if the cell references in your
Posted by Mike on August 27, 2001 4:30 AM
You are right. The chart is not referencing the new pasted series data but the worksheet in the original workbook. I will somehow have to point the series function to look at the chart itself. Thanks. Thanks. I'll let you know what I find. : I'm wondering if the cell references in your
