saveas and excel template
On Jun 13, 10:34 am, Mike wrote:
On Jun 12, 6:16 pm, Incidental wrote:
Hi Mike
not sure if this is what is causing your problem but i have tried the
code below in a worksheet change event and it is working, it didn't
however work unless i indicate which sheet to check cell B6
Option Explicit
Dim mname As String
Sub filesaveas()
mname = Sheets(3).Range("b6").Value
Application.Dialogs(xlDialogSaveAs).Show (mname)
End Sub
hope this helps
S
I've checked the procedures of the workbook and couldn't find the
saveas procedure. So I picked what looked closest to what I wanted to
do which is the beforesave procedure.
The save as dialog now shows my combination of cells like I wanted,
but unfortunately, when I hit save in the dialog, the dialog pops up
again. Any ideas?
Here's the code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim pFileName As Variant
Dim docTitle As String
Dim docNumber As String
Dim revision As String
docNumber = Range("g5").Value
docTitle = Range("b6").Value
revision = Range("h5").Value
pFileName = docNumber & "-" & revision & "-" & docTitle
With Application
.Dialogs(xlDialogSaveAs).Show (pFileName)
End With
And thanks for giving me ideas.- Hide quoted text -
- Show quoted text -
I've searched a long time now and finally found a hint to make the
second save as dialog go away.
I post this in case some one else can make use of it, too.
I've read in another post that the woorkbook_beforesave is calling a
save as dialog box. If I understand it correct (I really don't know
much about VBA), I call a saveas inside a save routine. So with the
Cancel=True in the beginning, the first save as is cancelled, leaving
me with just what I want.
Here's my code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Dim pFileName As Variant
Dim docTitle As String
Dim docNumber As String
Dim revision As String
docNumber = Range("g5").Value
docTitle = Range("b6").Value
revision = Range("h5").Value
pFileName = docNumber & "-" & revision & "-" & docTitle
With Application
.Dialogs(xlDialogSaveWorkbook).Show (pFileName)
.ThisWorkbook.Saved = True
End With
Application.EnableEvents = True
End Sub
|