saveas and excel template
 

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

saveas and excel template
 

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"
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

saveas and excel template
 

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

saveas and excel template
 

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.

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