Save template , then as workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have a template that has a macro to increase a filed number by one, to be used as an i.d number. This works fine, but i am now trying to save the template as soon as this is done, so that the next user gets the template, with the number advanced by one to avoid duplicates. Then when the "save as" is brought up in the book they are working on, a particular cell value, I2, is used as the file name and it is saved as a worbook in a particular path.

Make sense??
Any ideas??

Thanks
 
Example from Patrick Molloy

http://groups.google.com/groups?hl=...1&[email protected]


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Andrew said:
Hi
I have a template that has a macro to increase a filed number by one, to be used as an i.d number. This works fine, but i am
Click to expand...
now trying to save the template as soon as this is done, so that the next user gets the template, with the number advanced by
one to avoid duplicates. Then when the "save as" is brought up in the book they are working on, a particular cell value, I2, is
used as the file name and it is saved as a worbook in a particular path.
 
Hi Ro
Thanks for the links. The second option looks like it is what i am after, however i am having a few problems
Am i right in saying that this is activated by a button click on the sheet
Is it possible you can tell me what each of the refferences in the script relate to so i can alter to my sheet, and how to link it to the button?
Not really up to speed in the less basic side of excel, as you can probably tell

Thanks
 
Hi

I tested it myself also(I never used it)
Save a text file(use Notepad) with the name InvoiceNumber to the path below.
You must place the number 0 in the file before you save it
Copy the code below in module

Const csINVOICEFILE As String _
= "C:\InvoiceNumber.txt"

Sub cmdNewNumber()
Dim ff As Long
Dim Invoice As Long

' reads current number
ff = FreeFile
Open csINVOICEFILE For Input As ff
Input #ff, Invoice
Close ff

' increments it
Invoice = Invoice + 1

' Add the number in A1 of the first sheet
Sheets(1).Range("A1") = Invoice

' saves it
ff = FreeFile
Open csINVOICEFILE For Output As ff
Print #ff, Invoice
Close
End Sub

See the Excel help for using buttons and assign a macro to it
 
Back
Top