Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Template (Creating a Function in the template) | Excel Worksheet Functions | |||
I want to create a template like invoice template in excel. | Excel Programming | |||
Excel template to load automatically as the default template? | Excel Discussion (Misc queries) | |||
Using custom template with Excel.Workbook.SaveAs | Excel Programming | |||
Saveas on Template retains existing name! | Excel Programming |