Vba Auto Increment File Name Changer
We're developing an invoice template for Word, which contains an invoice number in a field at the top of the page.
You may have to force a recalculation using F9 or the VBA command Sheet1.Range('A1').Calculate (assuming you put the formula in cell A1). Share improve this answer answered Aug 16 '11 at 7:49. Each name would refer to ='filename.xlsmA Weekly Sheet Name'!$1:$65536 e.g.: ='filename.xlsm18-Feb-2011'!$1:$65536 (The 65536 is a throwback to Excel 2003 to include the entire sheet.
We would like the invoice number to auto-increment (from the last invoice number), and we would like the filename to be based on the invoice number.
They'll all be saved in the same directory.
Excel Vba Increment Value +1
SteveSteveApr 20, 2011 Re: Automatically increment revision number if file already exists This macro assumes the directory you want to save to is the current directory of the.
2 Answers
I found this which I think will solve the problem:
Use an Autonew macro to add a sequential number to a document and save it with that number.
In the template from which you create the document, insert a bookmark named Order in the location where you want the sequential number to appear and create an AutoNew macro in the template, as follows:
If you don't need to display the number in the document, but just want to save it with a sequential number, there is no need to create the bookmark in the template and you should then delete the second last line of the code.
DavidPostill♦
Not the answer you're looking for? Browse other questions tagged microsoft-wordmicrosoft-word-2007macros or ask your own question.
Excel Vba Increment Cell Value
http://www.rogersaccesslibrary.com/forum/topic395.html
and there's a more complex one of mine, which allows for the next number to be used to be 'seeded' at any time at:
http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23839&webtag=ws-msdevapps
Unlike Roger's mine is not intended to guarantee sequential numbers, however, but only ordered numbers and operates in the same way as an autonumber in that if the insertion of a record is aborted the number will not be re-used for the next record.
I've also amended Roger's to allow numbers to be seeded, by changing his GetProductID() function in the product_2 form's module as follows:
Private Function GetProductID()
Dim lngProductID As Long
Dim lngSeedID As Long
lngProductID = Nz(Dmax('ProductID', 'Product'), 0) + 1
lngSeedID = Nz(Dlookup('Seed', 'Seeds'), 0)
If lngSeedID <= lngProductID Then
GetProductID = lngProductID
Else
GetProductID = lngSeedID
End If
End Function
This requires the addition of a one-row table, Seeds, to the database, with a single column, Seed, of long integer number data type. Enter a row with a zero value to start with. The number you wish to 'seed' is entered into this table, for which a simple form can be designed, setting its AllowAdditions and AllowDeletions properties to False (No) so that only the one existing row can be edited. To open this form I've added a button to the Product2 form with the following code in its Click event procedure:
DoCmd.OpenForm 'frmSeeds', WindowMode:=acDialog
Me.ProductID.DefaultValue = '' & GetProductID & ''
This amendment to Roger's solution makes mine pretty much redundant. There could be some situations where mine is more bullet-proof, principally that the user will see the new number when they begin to insert the new record and it will not change, whereas with Roger's, if there is a conflict the number will change for any user who is not the first to save the record with the new number; but by and large Roger's solution, amended as above to allow seeding, will be fine and is a lot simpler to implement.