none
VBA question about SaveAs RRS feed

  • Question

  •  Set wk = Workbooks.Add
         wk.SaveAs FileName:=path & newname & ".xlsx"

    '<<<other code here tries to copy data from thisworkbook to (newname) then close (newname)'

    using the above in some code to save existing workbook as new filename (works fine!)

    question-does the (newname) workbook open during the SaveAs or does it simply add file (newname) to the directory.

     getting a glitch in some code and think its related to whether (newname) is open at this point.

    thanks for any thoughts

    Doug

    Thursday, February 21, 2019 1:39 PM

Answers

  • Just relate it with when you manually use SaveAs.

    When a workbook is opened and you save it using SaveAs, it saves a copy of the opened workbook and that's why when you SaveAs an opened workbook with the same name, you get a warning that this workbook already exists, do you want to replace it? Isn't it?

    If you SaveAs the opened file with a different name, it will save a copy of the opened file with the name you provided, close the previously opened file and you will see that the file with new name is currently opened only.

    In your case, the line Set wk = Workbooks.Add, create a new workbook and at this point you will find that your macro workbook and the newly created workbook, both are opened at the same time.

    And when you use SaveAs just after creating a new workbook, the newly created file gets saved at the path along with the newname you provided and the file remains opened. And after copying the data to the newly added workbook, you will need to close it properly to save the changes you made to the new workbook i.e. wk.Close True

    What I would suggest you is, since you have already set a reference of newly added workbook to the variable wk, you can copy the data from ThisWorkbook to the workbook wk and in the end you can save it using SaveAs and close it properly. i.e.

    wk.SaveAs FileName:=path & newname & ".xlsx"
    wk.Close

    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by 6da4 Sunday, February 24, 2019 12:50 PM
    Thursday, February 21, 2019 7:59 PM

All replies

  • wk.SaveAs keeps the workbook open in memory and saves it to the folder specified in path. The workbook isn't closed and reopened.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 21, 2019 4:13 PM
  • Just relate it with when you manually use SaveAs.

    When a workbook is opened and you save it using SaveAs, it saves a copy of the opened workbook and that's why when you SaveAs an opened workbook with the same name, you get a warning that this workbook already exists, do you want to replace it? Isn't it?

    If you SaveAs the opened file with a different name, it will save a copy of the opened file with the name you provided, close the previously opened file and you will see that the file with new name is currently opened only.

    In your case, the line Set wk = Workbooks.Add, create a new workbook and at this point you will find that your macro workbook and the newly created workbook, both are opened at the same time.

    And when you use SaveAs just after creating a new workbook, the newly created file gets saved at the path along with the newname you provided and the file remains opened. And after copying the data to the newly added workbook, you will need to close it properly to save the changes you made to the new workbook i.e. wk.Close True

    What I would suggest you is, since you have already set a reference of newly added workbook to the variable wk, you can copy the data from ThisWorkbook to the workbook wk and in the end you can save it using SaveAs and close it properly. i.e.

    wk.SaveAs FileName:=path & newname & ".xlsx"
    wk.Close

    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by 6da4 Sunday, February 24, 2019 12:50 PM
    Thursday, February 21, 2019 7:59 PM
  • thanks Hans,

    really was just having a mental block...some code wasn't working and couldn't figure out why...turns out I was passing my file name thru a couple of variables (for some odd reason!!)  -the solution was to clean up the file name reference...

    anyway, as always thanks for your help

    Doug

    Sunday, February 24, 2019 12:50 PM
  • thanks Subodh,

    replied to Hans reply above explaining that the problem was not with whether the book was open or not...my code was weak when referencing the filename...cleaned that up and everything works!!

    that being said I marked your reply as answer...useful information clearly explained!!

    thank you for your reply

    Doug

    Sunday, February 24, 2019 12:55 PM
  • You're welcome Doug! Glad you found it helpful.

    Subodh Tiwari (Neeraj) sktneer

    Sunday, February 24, 2019 2:55 PM