Macro or VBA help

[Guest]

I would like to add a button to my spreadsheet that
opens "save as" and then after the user saves it it
reopens the original file.
Can anyone help me get that going?

Thanks.

 

[Norman Jones]

Hi,

It is not necessary to close the file and re-open it. Using SaveCopyAs will
save a copy to disk whilst keeping the original open.

Try attaching the following to your button:

Sub MyCopyAs()
Dim fName As Variant, Res As Variant
Res = MsgBox("Save a copy of this file?", vbYesNo)
If Res = vbYes Then
fName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files Files (*.xls), *.xls")
If fName <> False Then
ActiveWorkbook.SaveCopyAs Filename:=fName
End If
End If
End Sub

---
Regards,
Norman


Try attaching the following macro to your button

 

[Bob Phillips]

Here is some code to attach to the button


thisFile = ActiveWorkbook.Fullname
ans = MsgBox("Save file now?", vbYesNo)
If ans = vbYes Then
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excle Files Files (*.xls), *.xls")
If fileSaveName <> False Then
Activworkbook.SaveAs Filename:=fileSaveName
Workbooks.Open filename:= thisFile
End If
End If

 

[Ed]

Here's code that make that work:
Sub SaveMe()
Dim wkbk As String
' grab name of open workbook
wkbk = ActiveWorkbook.FullName
' use built-in dialog to SaveAs
Application.Dialogs(xlDialogSaveAs).Show
' close saved workbook
ActiveWorkbook.Close
' reopen original workbook
Application.Workbooks.Open (wkbk)
End Sub

I think I would put it in the WorkSheet module for the sheet that's going to
have the button on it. Create your button and assign this macro to it.

Ed