Static Macro Variable

[Mike Wood]

I have a macro that I run once per document. The macro pulls in a
text report file and changes the font and orientation, then asks the
directory I want to save the new document in and then the document
name.

These reports are downloaded from a mainframe and then saved as
archive reports for users to refer to at a later date. I usually am
processing 200-300 reports at a time every 3-4 months.

This is a highly repetitive task which is why I created the macro.
On my previous computer, using Word 2000, I was able to keep the
directory name and the filename from instance to instance; only
changing when I need to change the output directory or the filename.

On my new computer running Word 2002 I have been unable to get the
variables to be static.

What am I doing wrong?

Static Sub report()
'
' report Macro
' Macro recorded 10/30/2003 by Mike Wood
'
Static dirname As String
Static filname As String

Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
With ActiveDocument.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientLandscape
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(1)
.RightMargin = InchesToPoints(1)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(11)
.PageHeight = InchesToPoints(8.5)
.FirstPageTray = wdPrinterDefaultBin
.OtherPagesTray = wdPrinterDefaultBin
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
.TwoPagesOnOne = False
.GutterPos = wdGutterPosLeft
End With
Selection.WholeStory
Selection.font.Name = "Courier New"
'Selection.font.Name = "Courier New"
Selection.font.Size = 8

dirname = InputBox("Please supply directory name for save", "Enter
Directory Name")
ChangeFileOpenDirectory ("K:\FINSRV\BILLING\reports\" & dirname)
filname = InputBox("Please supply file name for save", "Enter
Document Name")
ActiveDocument.SaveAs filename:=filname, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

End Sub

 

[DA]

Hi Mike

There's nothing really wrong with your code, but I
suspect the way you've designed the execution of your
macro is at fault here.

What I assume is happening is that you've got your code
inside of each document, saving the doc and then closing
it.

The variables declared in your static sub routine are
only stored in memory while the program is running. This
means that once the document closes, so does your program
and then you've lost your static vars.

Include your routine into a template and add it to your
startup directory, then call the routine when needed.
There's a few different ways to do this, but in any case,
make sure you keep the document or template that contains
your static routine, running.

Hope that helps,
Dennis

-----Original Message-----
I have a macro that I run once per document. The macro pulls in a
text report file and changes the font and orientation, then asks the
directory I want to save the new document in and then the document
name.

These reports are downloaded from a mainframe and then saved as
archive reports for users to refer to at a later date. I usually am
processing 200-300 reports at a time every 3-4 months.

This is a highly repetitive task which is why I created the macro.
On my previous computer, using Word 2000, I was able to keep the
directory name and the filename from instance to instance; only
changing when I need to change the output directory or the filename.

On my new computer running Word 2002 I have been unable to get the
variables to be static.

What am I doing wrong?

Static Sub report()
'
' report Macro
' Macro recorded 10/30/2003 by Mike Wood
'
Static dirname As String
Static filname As String

Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
With ActiveDocument.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientLandscape
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(1)
.RightMargin = InchesToPoints(1)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(11)
.PageHeight = InchesToPoints(8.5)
.FirstPageTray = wdPrinterDefaultBin
.OtherPagesTray = wdPrinterDefaultBin
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
.TwoPagesOnOne = False
.GutterPos = wdGutterPosLeft
End With
Selection.WholeStory
Selection.font.Name = "Courier New"
'Selection.font.Name = "Courier New"
Selection.font.Size = 8

dirname = InputBox("Please supply directory name for save", "Enter
Directory Name")
ChangeFileOpenDirectory

("K:\FINSRV\BILLING\reports\" & dirname)

 

[Word Heretic]

G'day (e-mail address removed) (Mike Wood),

Devs usually achieve this effect through writing the last used to the
registry and re-obtaining each time. Its fairly simple, use GetSetting
and SaveSetting.

Steve Hudson - Word Heretic
Want a hyperlinked index? S/W R&D? See WordHeretic.com

steve from wordheretic.com (Email replies require payment)


Mike Wood reckoned:

 

[Mike Wood]

This macro is in my normal.dot

Hi Mike

There's nothing really wrong with your code, but I
suspect the way you've designed the execution of your
macro is at fault here.

What I assume is happening is that you've got your code
inside of each document, saving the doc and then closing
it.

The variables declared in your static sub routine are
only stored in memory while the program is running. This
means that once the document closes, so does your program
and then you've lost your static vars.

Include your routine into a template and add it to your
startup directory, then call the routine when needed.
There's a few different ways to do this, but in any case,
make sure you keep the document or template that contains
your static routine, running.

Hope that helps,
Dennis



("K:\FINSRV\BILLING\reports\" & dirname)

 

[DA]

Mike,

I'd say that your static values are staying, but the
problem is that you are prompting for a new value every
time, thereby wiping the variables filname and dirname if
the user doesn't enter a new value.

Include a check to prompt for the values only if they are
empty, or a better alternative would be to include the
variables as the default prompt. For example:

dirname = InputBox("Please supply directory name for
save", "EnterDirectory Name", dirname)

Hope that helps,

Dennis