none
The specified dimension is not valid for the current chart type

    Question

  • While trying to save a workbook in a macro, I get the following error message which stops the macro, "The specified dimension is not valid for the current chart type". The workbook does have a chart in the 2nd tab.I am able to save the workbook manualyy with no problem. Why is it getting hung up in the macro?

    Any suggestions?


    Don Fox
    Tuesday, September 20, 2011 3:45 PM

Answers

  • Hi Calvin,

    As I mentioned earlier, there was a DIM command that was in the wrong place in the code. When I moved it to another place the code worked so I am going to infer that it was the DIM command that was causing the hangup. I have had no problems since I moved it. Here is the code in question;

    (This is within a WITH statement)

    Range("A10").Select
                ' Save the file as the division report.
                DIV = Range("A8").Value
                RD = Range("A8").Value
                RD = Left(RD, 2)
               
                While MD = "Y"
                MkDir "I:\ABT&S\SunGard\Reporting\OnBoard Reporting\BRSU_Reports\BRSU_Div_Reports_New\" & RD
                MD = "N"
            Wend
                SaveLocation = "I:\ABT&S\SunGard\Reporting\OnBoard Reporting\BRSU_Reports\BRSU_Div_Reports_New\"
                SaveLocation = SaveLocation & RD & "\"
               
               
                ActiveWorkbook.SaveAS Filename:=SaveLocation & DIV & "_RPT" & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
               
                ActiveWorkbook.Close
            End With


    Don Fox
    Thursday, September 22, 2011 1:31 PM

All replies

  • The macro is using a different filetype to perform the save than the manual workbook save.  I would have to see the save (saveas) command to determine what the proper solution will be.  Also what extension do you get when you save the file manually.
    jdweng
    Tuesday, September 20, 2011 6:57 PM
  • Thanks Joel,

    It had to do with a dim command earlier in the program (which made no sense to me). At the time I manually saved it I saw a.xlsx extensino which is the same ext that was in the macro,


    Don Fox
    Wednesday, September 21, 2011 1:26 AM
  • Below are my comments

    1) You probably are saving the entrie workbook and not just the 2nd tab.  Excel will always save the entire workbook unless you specify you want to save only one sheet.

    2) Error Message:The filetype in the SAVEAS statement is probably not an XLSX filetype, or the workbook type that is opened is not XLSX.  The workbook that is opened may be CSV which doesn't support a chart.  Look at the filetype in the Open workbook to see if it is XLSX.


    jdweng
    Wednesday, September 21, 2011 6:23 AM
  • 1) You probably are saving the entrie workbook and not just the 2nd tab.  Excel will always save the entire workbook unless you specify you want to save only one sheet.

    2) Error Message:The filetype in the SAVEAS statement is probably not an XLSX filetype, or the workbook type that is opened is not XLSX.  The workbook that is opened may be CSV which doesn't support a chart.  Look at the filetype in the Open workbook to see if it is XLSX

    I agree with this, but how to save only one sheet...The only approach occurs to me is to move the specified sheet into a new workbook and save the workbook. Worksheet.SaveAs method isn't designed for this as far as I know.

    Hi Don,

    Can you confirm the scenario on your side is somehow similar with what Joel said? If not, could you post your code so that we can troubleshoot the issue more efficiently?

    Good day,


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, September 22, 2011 9:09 AM
  • Hi Calvin,

    As I mentioned earlier, there was a DIM command that was in the wrong place in the code. When I moved it to another place the code worked so I am going to infer that it was the DIM command that was causing the hangup. I have had no problems since I moved it. Here is the code in question;

    (This is within a WITH statement)

    Range("A10").Select
                ' Save the file as the division report.
                DIV = Range("A8").Value
                RD = Range("A8").Value
                RD = Left(RD, 2)
               
                While MD = "Y"
                MkDir "I:\ABT&S\SunGard\Reporting\OnBoard Reporting\BRSU_Reports\BRSU_Div_Reports_New\" & RD
                MD = "N"
            Wend
                SaveLocation = "I:\ABT&S\SunGard\Reporting\OnBoard Reporting\BRSU_Reports\BRSU_Div_Reports_New\"
                SaveLocation = SaveLocation & RD & "\"
               
               
                ActiveWorkbook.SaveAS Filename:=SaveLocation & DIV & "_RPT" & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
               
                ActiveWorkbook.Close
            End With


    Don Fox
    Thursday, September 22, 2011 1:31 PM
  • As I mentioned earlier, there was a DIM command that was in the wrong place in the code. When I moved it to another place the code worked so I am going to infer that it was the DIM command that was causing the hangup. I have had no problems since I moved it. Here is the code in question;

    So now I have resolved the problem? Could you make your reply as an answer to close the thread?

    If you have any other development question about Office, welcome to post here:-)

    Thanks for your understanding and support.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 23, 2011 8:21 AM