saveas and excel template

[Mike]

I've created an excel template and now want to propose the filename as
a combination of different cell values.
I used the Sub SaveAs() in word and it worked perfectly. Now in excel
it's not working.

This is what I used in my latest attempt (found on one of the groups):

Sub filesaveas()

mname = Range("b6").Value
Application.Dialogs(xlDialogSaveAs).Show (mname)

End Sub

It works if I run it from the VBA editor, but as soon as I'm back to
my template, it's not working.
I'm using Excel 2003.

Any help appreciated

 

[Jim Cone]

What does "not working" mean?
Please don't post questions with that phrase in it....

However, the dialog displays for me, with and without the ( ) around mname.
I did declare mname as a variant.
The issue probably is cell B6 on your template sheet does not have
a complete file path in it. That would look something like this...
"C:\Documents and Settings\user\My Documents\Old Excel Files"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Mike" <[email protected]>
wrote in message
I've created an excel template and now want to propose the filename as
a combination of different cell values.
I used the Sub SaveAs() in word and it worked perfectly. Now in excel
it's not working.

This is what I used in my latest attempt (found on one of the groups):

Sub filesaveas()

mname = Range("b6").Value
Application.Dialogs(xlDialogSaveAs).Show (mname)

End Sub

It works if I run it from the VBA editor, but as soon as I'm back to
my template, it's not working.
I'm using Excel 2003.

Any help appreciated

 

[Incidental]

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

 

[Mike]

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.

 

[Mike]

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