PDA

View Full Version : Solved: Saving a Workbook



chem101
11-10-2010, 08:13 AM
Hello Everyone,

I need some help saving a workbook. I have a workbook called 'Template' that users use to create other workbooks. I would like the user to enter data in 'Template' and then save the 'Template' workbook with the data they've entered under a different file name for future reference. I would like the user at this point to continue to work with the 'Template' workbook to create other workbooks and save those workbooks as described above. With the code I have in place this doesn't happen. Can you help me? Here's what I have so far:

Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application. _
GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save This Estimate As")

ThisWorkbook.Close

Workbooks.Open Filename:="Template.xlsm", _
AddToMru:=True

End Sub

Thank you in advance for any help you can provide!

Bob Phillips
11-10-2010, 10:33 AM
Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save This Estimate As")

ThisWorkbook.SaveCopyAs UserFile
End Sub

chem101
11-10-2010, 10:46 AM
Works great! Thank you very much!!

chem101
11-10-2010, 11:27 AM
One question - How do we change/add to your code if: The user starts with 'Template' and modifies it, then saves it as TemplateSave1'. While 'Template' is open the user opens TemplateSave1, modifies it and wants to re-save it as TemplateSave1' and also close 'TemplateSave1' so they can continue to use 'Template'? Please advise.

Thank you!!

Bob Phillips
11-10-2010, 11:37 AM
I don't understand what you are saying, that codes doesn't keep the new workbook open.

mdmackillop
11-10-2010, 11:38 AM
Use SaveCopyAs to save copies and continue with the original book open

chem101
11-10-2010, 11:42 AM
Here is a copy of the code:

Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save Workbook As")
ThisWorkbook.SaveCopyAs UserFile
End Sub

Thank you!!

Bob Phillips
11-10-2010, 01:08 PM
Use SaveCopyAs to save copies and continue with the original book open

That is what he is (should be) doing MD, I suggested that earlier.

Bob Phillips
11-10-2010, 01:09 PM
Here is a copy of the code:

Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save Workbook As")
ThisWorkbook.SaveCopyAs UserFile
End Sub

Thank you!!

So explain again, what is the problem?

chem101
11-10-2010, 01:15 PM
Thank you for your resolution. I just have one more question about this item If the user starts with 'Template' and modifies it then saves it as 'TemplateSave1'. Using your code, the file is saved and the user is returned to 'Template' (perfect so far!). While in 'Template' if the user opens 'TemplateSave1 and modifies it further then tries to re-save it as 'TemplateSave1' using the code you provided we get an error that states "The workbook you are trying to save has the same name as a currently open workbook". How can the code be modified to allow the file to be re-saved under the same name? Normally Excel would ask if we want to replace the existing file. Can this be done with VBA code?
Thank you very much for your assistance!

Bob Phillips
11-10-2010, 02:20 PM
Don't use that code to save 'TemplateSave1', use the Save button.

chem101
11-10-2010, 02:26 PM
Not possible. For this worksheet I've disabled the ribbon. The user is saving their work via the code attached to a control button placed on the worksheet.

Bob Phillips
11-10-2010, 02:36 PM
See if this does what you want



Sub GetUserSaveFile()
Dim wb As Workbook
Dim UserFile As Variant
Dim Filename As String
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save Workbook As")
Filename = Right$(UserFile, Len(UserFile) - InStrRev(UserFile, "\"))
On Error Resume Next
Set wb = Workbooks(Filename)
On Error GoTo 0
If wb Is Nothing Then

ThisWorkbook.SaveCopyAs UserFile
Else

wb.Save
End If
End Sub

Bob Phillips
11-11-2010, 02:17 AM
Not possible. For this worksheet I've disabled the ribbon. The user is saving their work via the code attached to a control button placed on the worksheet.

BTW, wouldn't Ctrl-S still work?