View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Stuart[_21_] Stuart[_21_] is offline
external usenet poster
 
Posts: 154
Default 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.