none
Problem with "Save as" Marco with password protection in Excel RRS feed

  • Question

  • I create a macro in which I use a SaveAs code. It works well before I add password protection for the vba code.

    After I add the password protection, only run button is actived,which is good. But when I click the run button, It threw me the message "runtime error 1004, method SaveAs of object _Workbook failed".

    How can I use the SaveAs code while keep the password to protect my code.

    Friday, March 29, 2013 4:34 AM

Answers

  • Hi Isaac_SU,

    Is the file format OK for the SaveAs macro? 

    ThisWorkbook.SaveAs "D:\Test.xlsm" works fine.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 29, 2013 12:44 PM
    Moderator
  • take look on this

    Old Style: 

    'save with password and close
    ActiveWorkbook.SaveAs FileName:="Path\fileName.xls", FileFormat:=xlNormal, password:="pass"
    ActiveWorkbook.close
    
    'open using password
    Workbooks.Open "Path\fileName.xls", password:="pass"

    New one:

    ActiveWorkbook.SaveAs Filename:="Path\fileName.xlsm", FileFormat:=52, Password:="pass"
    ActiveWorkbook.Close
    
    Workbooks.Open "Path\filename.xlsm", password:="pass"


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, March 29, 2013 6:58 PM
    Answerer

All replies

  • Hi Isaac_SU,

    Is the file format OK for the SaveAs macro? 

    ThisWorkbook.SaveAs "D:\Test.xlsm" works fine.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 29, 2013 12:44 PM
    Moderator
  • take look on this

    Old Style: 

    'save with password and close
    ActiveWorkbook.SaveAs FileName:="Path\fileName.xls", FileFormat:=xlNormal, password:="pass"
    ActiveWorkbook.close
    
    'open using password
    Workbooks.Open "Path\fileName.xls", password:="pass"

    New one:

    ActiveWorkbook.SaveAs Filename:="Path\fileName.xlsm", FileFormat:=52, Password:="pass"
    ActiveWorkbook.Close
    
    Workbooks.Open "Path\filename.xlsm", password:="pass"


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, March 29, 2013 6:58 PM
    Answerer