VBA code for save as and close workbook

[Jonathan Jones]

Hi,

I would like to save the active workbook as a specific name which is obtained from a cell in the workbook, then close the workbook.

It's a test results sheet. The engineer opens the master, he adds his results and then clicks a button which says "Testing Complete". I'd like the master to be closed without saving, then the one he's added his results to is saved as a value in a cell to a specific folder on the server and then closed. Ideally, I'd like a warning box to come up which says "Testing Complete?" with an option of yes or no. No, cancels, yes proceeds with the above process.

I have just started to learn VBA, so I was hoping to work it out for myself but I need this for something at work, and have got impatient!

Any help, would be very much appreciated.

Jonathan

 

[iliace]

In the simplest form, if your file path is on Sheet1 in cell A1:

Public Sub TestingComplete()
  Dim answer As VBA.VbMsgBoxResult
  
  answer = MsgBox("Testing complete?", vbYesNo + vbQuestion, "Confirm")
  If answer = vbNo Then Exit Sub
  
  ThisWorkbook.SaveCopyAs Sheet1.Range("A1").Value
  
  ' add any other code before this line
  ThisWorkbook.Close savechanges:=False
 
End Sub

 

[Jonathan Jones]

In the simplest form, if your file path is on Sheet1 in cell A1:

Public Sub TestingComplete()
  Dim answer As VBA.VbMsgBoxResult
  
  answer = MsgBox("Testing complete?", vbYesNo + vbQuestion, "Confirm")
  If answer = vbNo Then Exit Sub
  
  ThisWorkbook.SaveCopyAs Sheet1.Range("A1").Value
  
  ' add any other code before this line
  ThisWorkbook.Close savechanges:=False
 
End Sub

Hi,

Thanks very much for such a quick reply. I just have a couple of questions. 1. Your code doesn't seem to specify what file type the document is saved as, I would like it to be saved as just a standard excel file format without macros enabled (although it's not the end of the world if achieving this is overly complicated). 2. I can't see in your code where the folder the file is saved in is specified (this is important).

Thanks,

Jonathan

 

[sunny102]

change this ThisWorkbook.SaveCopyAs Sheet1.Range("A1").Value

to

ThisWorkbook.SaveCopyAs Sheet1.Range("A1").Value & ".xlsx", FileFormat:=51 ', CreateBackup:=False

 

[Jonathan Jones]

Hi,

Thanks again for the help. How do I specify the folder for the file to be saved in?

Jonathan

 

[shg]

If you use SaveCopyAs, you can't specify the file format (because if it's different, then it's not a copy). If it needs to be different, then you need to use SaveAs.

To specify the path,

  ThisWorkbook.SaveCopyAs "C:\somePath\" & Sheet1.Range("A1").Value

 

[iliace]

If you use SaveCopyAs, you can't specify the file format (because if it's different, then it's not a copy). If it needs to be different, then you need to use SaveAs.

I thought about this too. I think it depends on how exactly you want to structure your workflow, and what you want the experience to be like for the end user.

If you use SaveAs, then the "master" workbook is essentially closed, and the new file is in front of the user. But, you can specify the file format.

If you use SaveCopy As, then the new file is saved in its location (in the same file format), and the master workbook is still shown to the user. You can then choose to close the master workbook, but have the option not to.

If you want the best of both worlds - i.e. specify file format, and close the new file and still show the master workbook) - then you would need to do a SaveAs, re-open the master workbook, and close the newly saved-as file. This is a little more cumbersome, especially if the files are large and saved on the network (saving/re-opening will not be instantaneous), it degrades the user experience because there will be a "please wait" type flicker on the screen. But it can be done, if that's what you're trying to accomplish.

 

[shg]

I would do SaveCopyAs, open the new file, and then SaveAs in whatever format was desired.