GetSaveAsFileName questions
I'm taking User's data from a Form and copying it into a
copy of a sheet from the Addin which displays the form.
User clicks 'Save' button on the form, and the following
code runs:
Private Sub CbSave_Click()
Dim ws As Worksheet, wkbkname As String
Dim newbookname As String, sFileName As Variant
Application.ScreenUpdating = False
wkbkname = "G&H Project.xla"
With Workbooks
.Add (xlWBATWorksheet)
newbookname = ActiveWorkbook.Name
Workbooks(wkbkname).Sheets("Fax Template") _
.Copy Befo=ActiveWorkbook.Sheets(1)
Application.DisplayAlerts = False
For Each ws In Workbooks(newbookname) _
.Worksheets
With ws
If .Name < "Fax Template" Then
.Delete
End If
End With
Next
Application.DisplayAlerts = True
ActiveWorkbook.Unprotect Password:= "abc"
ActiveSheet.Unprotect Password:="abc"
End With
With Workbooks(newbookname). _
Worksheets("Fax Template")
'code to copy data from form into new sheet
'then code to effect the Save
sFileName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If sFileName < False Then
Workbooks(newbookname).SaveAs _
Filename:=sFileName
ActiveWorkbook.Close SaveChanges:=True
Else
ActiveWorkbook.Close SaveChanges:=False
End If
I would like to amend this code to incorporate as follows:
1. Point the Save to "C:\Temp"
2. Ensure a valid filename (*.xls) and, which must not be
newbookname
3. Let user quit the Save, and ensure the sheet is killed
and user returned to the Form
Can this be achieved, please?
Regards.
|