#1
|
|||
|
|||
Help with Serial No. Macro
Hi everyone. This is my first post and I hope someone can help me.
I want to create a macro in my Word document that automatically prints Serial No’s when I type them into input boxes. The Serial No. needs to have the format yyyy/0000 where yyyy represents the year and the four zeros a four digit number, for example 2011/0001 or 2011/0010 or 2012/0100. I have obtained the following code and tried to alter it but I’m having problems getting it to do what I want it to. Is there any way it can be modified to give me what I’m after? Here is the code: Sub macSSerialNo() ' macSSerialNo Macro ' The variables dimensioned below are used in both the Input Boxes to capture the copy numbers ' and the page numbers to be printed Dim strLMessage, strHMessage, strTitle As String Dim intDefault, intLowNum, intHighNum, intSerialNum As Integer 'The following lines set up variables used in the display of input boxes strTitle = "Print multiple Serial numbers" strLMessage = "Enter LOW Serial number" strHMessage = "Enter HIGH Serial number" 'The following lines reset variables intDefault = 0 intLowNum = 0 intHighNum = 0 intSerialNum = 0 'The following lines capture inputs from input boxes intLowNum = InputBox(strLMessage, strTitle) If intLowNum = "" Then GoTo WayOut End If intHighNum = InputBox(strHMessage, strTitle) If intHighNum = "" Then GoTo WayOut End If intSerialNum = intLowNum 'The following DO statement is the beginning of the LOOP that increments the inSerialNum variable Do 'This block finds "Serial No:, moves however many spaces to the right and then deletes existing Serial 'No Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "Serial No. : 2011/" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Selection.MoveRight Unit:=wdCharacter, Count:=3 Selection.Delete Unit:=wdCharacter, Count:=2 Selection.TypeText Text:=intSerialNum 'Prints on screen new Serial No intSerialNum = intSerialNum + 1 'Increment Serial number by 1 Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _ wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _ Collate:=False, Background:=True, PrintToFile:=False 'Once the SerialNum gets too high, the LOOP UNTIL redirects programme follow onto 'End Sub (End of Macro) Loop Until intSerialNum > intHighNum WayOut: End Sub There are two reasons why the macro isn’t working the way I want it to. The first reason is because: I cannot get it to work without having the year somewhere in the document and also in this part of the code: With Selection.Find .Text = "Serial No. : 2011/" Is there any way I could use some sort of wild card here instead of having to have 2011 in the document and changing it to say 2012 when I require a year change? The second reason is to do with getting 0001, 0010, 0100 and 1000 without messing around with the code. I keep having to edit the code as explained below. In order to get 0001 I am using the following code: Selection.MoveRight Unit:=wdCharacter, Count:=4 Selection.Delete Unit:=wdCharacter, Count:=2 The document has to have 0000 in it before I type the serial number into the input box so that the last zero becomes a 1 In order to get 0010 I am using the following code: Selection.MoveRight Unit:=wdCharacter, Count:=3 Selection.Delete Unit:=wdCharacter, Count:=2 The document has to have 0000 in it before I type the serial number into the input box so that the last two zeros become 10 In order to get 0100 I am using the following code: Selection.MoveRight Unit:=wdCharacter, Count:=2 Selection.Delete Unit:=wdCharacter, Count:=2 The document has to have 00 in it before I type the serial number into the input box. The code adds on the last two digits to become 0100 In order to get 1000 I am using the following code: Selection.MoveRight Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=2 The document has to have a 0 in it before I type the serial number into the input box. The 0 gets replaced by whatever digit I enter followed by the other digits I enter so I do end up with a four digit serial number. So that’s it. Basically I want a macro that will give me 2011/0001 or 2011/0010 etc etc. Any help with this would be very much appreciated |
#2
|
||||
|
||||
Hi pikerman,
See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi macropod
Many thanks for your help with my problem. I have run the demo a few times and get the numbers exactly like I want them. I have noticed though that if I only want to print one serial number, it prints it twice and also the next time I run the demo, it prints the last page to be printed again. Perhaps I’m doing something wrong. Also, I notice that the demo is a Word Macro Enabled Document. I have already typed up the documents I want the serial numbers to appear in as ordinary Word documents. Can these still be used without changing them to macro enabled? The documents contain the words “Serial No.” so how do I get the 2011/0001 to appear after it? Once again, any help on this would be appreciated. |
#4
|
||||
|
||||
Hi pikerman,
To fix the duplicate printing, I've changed the line: ActiveDocument.PrintOut to: If i > iStart Then ActiveDocument.PrintOut I'm not sure what you mean by "the next time I run the demo, it prints the last page to be printed again". In my testing, it was the first certificate in the sequence that was duplicated. The above change fixes that. As for the document being macro-enabled (docm format), that's a Word 2007 thing. Since you're using Word 2003, you can use the .doc format. However, the document still contains a macro and you'll still get the 'enable macros' prompt when you open it. See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help needed using the serial number date with sumifs - whole office is stumped | FraserKitchell | Excel | 3 | 01-06-2010 12:24 PM |