Celia please...
Posted by Oeloel on August 07, 2000 10:11 PM
Thank you Celia for your valuable help. I now would like to know if you have come up with a different way of doing the procedure below. I got the same situation but it did not work with me, maybe I'm doing something wrong. Many thanks...
Can anyone supply some code that assigns numbers to workbooks in the same way as the MS Excel Invoice Template?
I use the following code(in a normal module) in some workbooks to assign a consecutive number each time a template is opened as a workbook, but I dont really like it.
It seems to me to be a bit indirect (I wrote it when first starting to learn VBA about three months ago and havent got round to working out a better way).
Although it works, would like something more streamlined if possible.
Copy the ref. no. on the active worksheet
Range("A1").Copy
'Open the template
Workbooks.Open filename:="C:Myfile.xlt", Editable:=True
Paste to the template the copied ref. no. + 1
With Range("A1")
.Value = "1"
.PasteSpecial Paste:=xlValues, Operation:=xlAdd
End With
Application.CutCopyMode = False
'Save and close the template
ActiveWorkbook.Close savechanges = True
Celia
Posted by Celia on August 09, 0100 1:09 AM
Re: Celia, something´s going wrong...
Oeloel
I would recommend that you read the Help file re creating and using Templates. To implement the macro previously sent :-
Create a template file (.xlt) and format a blank invoice (or whatever) on a worksheet in the file. In the sample macro provided, the file is called "Myfile.xlt" and the full path and file is "C:\Myfile.xlt". You can call it what you like and change the path to whatever you like just make sure to change the macro accordingly.
Put the macro in a normal module in the .xlt file. Cell A1 contains the sequential number - if necessary change this cell reference in the macro to fit your format. You can copy and paste the following to the module :-
Sub UpdateRefNbr()
Range("A1").Copy
Workbooks.Open Filename:="C:\Myfile.xlt", Editable:=True
With Range("A1")
.Value = "1"
.PasteSpecial Paste:=xlValues, Operation:=xlAdd
End With
Application.CutCopyMode = False
ActiveWorkbook.Close saveChanges:=True
End Sub
Put a button on the worksheet and assign the macro to it.
Save the file and close it.
To open the file as an .xls workbook, go to Windows Explorer and double click on the file. The first time you open an .xls workbook from the template, the ref. number will be blank. Input the ref, number you want and click the button to run the macro (which updates the template file). The next time you open an .xls workbook from the template, the next sequential ref. number will already be shown in cell A1.
Thats it. If you still cant get it going, let me know your E-mail address and I will mail you a workbook.
Celia
Posted by Celia on August 08, 0100 5:27 PM
Oeloel
I' m still using the same method. There were some typos in the code posted. Try this :-
Range("A1").Copy
Workbooks.Open Filename:="C:\Myfile.xlt", Editable:=True
With Range("A1")
.Value = "1"
.PasteSpecial Paste:=xlValues, Operation:=xlAdd
End With
Application.CutCopyMode = False
ActiveWorkbook.Close saveChanges:=True
Celia
Posted by Oeloel on August 08, 0100 9:02 PM
Re: Celia, something´s going wrong...
I tried your approach. I think something's missing. Could you tell me the whole coding, where do I put it and how do I make it run? Please.
