none
NewBook.SaveAs hitting '1004' Runtime Error RRS feed

  • Question

  • Hi,

    I have this code which worked on older version of Excel. But upon using it on Excel 2016, I kept hitting '1004' runtime error, saying that either the path is not accessible or there are special characters in the filename.

    I created a msbox to display FileName1 and it shows "C:\Runbooka.xlsx"

    My code as below:

            FileName1 = Worksheets("#Sheet1").Cells(8, 8).Value
            MsgBox FileName1
            ActiveWorkbook.Save
            Newbook.SaveAs FileName:=FileName1, _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    The above works if I replace 'Newbook.SaveAs FileName:=FileName1, _' with 'Newbook.SaveAs FileName:="C:\Runbooka.xlsx", _'

    Is there a way to saveas using a variable like FileName1?

    Thanks!

    • Moved by KareninstructorMVP Thursday, December 21, 2017 10:00 PM moved from vb.net forum
    Thursday, December 21, 2017 3:47 PM

All replies

  • Is this in a VB.NET project or is this VBA code or VB6?

    If this is not VB.NET the above question will assist me in moving your question to the right forum. If VB.NET we need more details (yet doubt this is VB.NET from the format of how you use MsgBox).


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, December 21, 2017 4:12 PM
  • Hi,

    It's a VBA from Excel 2016.

    Thanks!

    Thursday, December 21, 2017 5:34 PM
  • Hi soots3,

    I'm afraid you are saving an Excel file with macro (.xlsm).
    I suppose
    (1) the file name in Cell(8, 8) must be "C:\Runbooka.xlsm".
    (2) FileFormat must be "FileFormat:=xlOpenXMLWorkbookMacroEnabled"

    Regards,

    Ashidacchi

    P.S.
    Please provide/specify what you want to do in your code for better assistance.
    • Edited by Ashidacchi Friday, December 22, 2017 2:36 AM
    Friday, December 22, 2017 2:27 AM
  • Hi soots3,

    >> The above works if I replace 'Newbook.SaveAs FileName:=FileName1, _' with 'Newbook.SaveAs FileName:="C:\Runbooka.xlsx", _'

    I am wondering whether it is real work for this.

    I made a test with your code, it throws exception due to that the code did not have suitable permission to store the files under c:

    I suggest you replace Cells(8,8).VALUE to “D:\Runbooka.xlsx”.

    If it still did not work, could you share us a simple file through OneDrive?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 22, 2017 7:04 AM