Macro lost its umph! (Office 97/Win 98)

[steve-eyer]

When doing mailing labels, my macros are a one time thing, and when I recall and run them again they have changed. Specifically calling out a label 5160, which are the common ones used in laser printers (30-up per sheet), the created macro works fine the first time through, but when called up a 2nd time, the label has completely switched to a very wide label that looks like a label for a dot matrix printer.

So the code is corrupt somewhere. How do I correct this? Reloading Win 98 and office 97 does nothing.

Steve

[kaplinb]

Does your macro attached to a global template or to the specific document formatted as 5160 sheet?

[steve-eyer]

I just use the make record new macro in the tools dropdown tab in Word 97, and I assumed it saved to normal.dot
I have done no customization to World and its parts, so it would go to the default location. I have no knowledge of VBA so I can't really help without specific guidance.

Steve

[Howard Kaikow]

Recorded macros will do that.

See my rants against recording macros at my URL below.

[jscher2000]

If you open the macros dialog (Tools, Macros, Macro...), highlight your macro and click Edit, you will find your insertion point in mid-macro in the Visual Basic Editor. If you highlight and paste your macro (from Sub MacroName to End Sub) into a post, we probably can give you further comments.

[steve-eyer]

Here is the lasest failure to "hold" the right mailing label format after Word is closed and reopened later.
I am taking a list of clients from Excel 97 and making mailing labels in Word 97.

Sub MJaddresses()
'
' MJaddresses Macro
' Macro recorded 12/07/01 by Steve Eyer
'
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:My DocumentsTC MJ Wholesale Name List.xls", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", SQLStatement:="", SQLStatement1:=""
Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels3"
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
ActiveDocument.PrintOut
CommandBars("Stop Recording").Visible = False
End Sub

Thanks for analyzing this for me. It is my major frustration on Office 97.
Steve

[jscher2000]

It looks as though the following line inserts whatever type of label is displayed in the Envelopes and Labels dialog:

Application.MailingLabel.CreateNewDocument Name:="", Address:="", AutoText _
:="ToolsCreateLabels3"

In the online help example for this command, it uses the label name in the Name:= parameter. Try changing the command to:

Application.MailingLabel.CreateNewDocument Name:="5160", Address:="", AutoText _
:="ToolsCreateLabels3"

Does it work? (Note: the name to use appears to be what's to the left of the hyphen in the label list behind the Options... button in the Envelopes and Labels dialog.)

[steve-eyer]

That solved the problem ! Thanks.

Is there any way to return the Macro section to its original (functional) condition? Reloading Office 97 and Win 98 didn't seem to help, but I always have trouble uninstalling completely, so I may have left the corruption in there.

Steve

[jscher2000]

I don't think you need to delete anything, just save your change to the macro so it sticks. To delete your macro, use the macros dialog.

Most likely your macro is saved in Normal.dot, your default global template. Depending on what else you have saved (e.g., other macros, AutoText entries, page setups, etc.), you might lose more than you want by deleting the entire template. To test, close Word, rename your Normal.dot to oldNormal.dot and restart Word, which will create a "blank" Normal.dot template.

[steve-eyer]

Dear J
The correct label number seemed to reappear when I reopened Word's macros. Perhaps I will just have to manually enter the label number as you suggested to get the macros to work the second time. I sure wish I could figure out how to cure the problem though. This computer is maxed out and I won't be able to upgrade to solve the problem, but I will continue to use it for the existing programs for a long time. Any ideas welcome.

Steve

[jscher2000]

I forgot to ask: why do you need to record a new macro each time? If it's only because the data file name changes and nothing else, we could tweak your macro to let you connect to the data source at run time, and keep the rest the same. If there are other reasons that it must be different, I agree, the best workaround is to pop in the label number before running the new macro.

[steve-eyer]

Dear J,
I use only 4 macros:
Excel file A
Excel file B
Excel file C
Excel file D
Each of these has its own macro
1. a mailing label
2. a label sized sticker for that product
3. another mailing label from a different Excel data base.
3. another label sized sticker for that type of product.

I always use the 5160 label because it is the commonest and the cheapest.
I would only need to record 4 macros total if the macro (or the 5160 data) weren't corrupted.
Since they lose their knowledge of which label (discussed and solved above) I was recording a new macro each and every time I wanted to make labels. Now with your suggestion I will manually enter the 5160 into the completed macro, and hope it will stay there. Then when it saves to normal.dot, I can pull it up the next time and it will still have the 5160 in the right place. In any case I can put it back in there before I run it. This is about as far to go with macros as I need to go with Office 97 and my tasks. Thanks for you help and if you ever discover how to repair this system, write me at steve@eyersworld.com

Thanks again for your help

Steve