Running Word Macro from Access XP

[Gal Zilberman]

Hi All
I'm trying to export a report into word and then run some VB code on it from
Access.
My code exports the report, changes the font to Arial and Adds my company's
letter head to it, everything works fine on the first run, but then the code
crashes the following times (Error 462).
What am I not closing?

thanks

Gal

here's the code (for the Word changes only)

Function UpdateWordFormat(strFileName As String)
On Error GoTo Err_UpdateWordFormat
' Vars
Dim wdApp As Word.Application
Dim wdDcmnt As Word.Document
Dim strTemplate As String
' Init
strTemplate = "C:\Letterhead.dot"
' Word Macro Code
' Create a Microsoft Word object.
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
' Open the Document to which you exported the data to.
Set wdDcmnt = wdApp.Documents.Open(strFileName)
' Change font to Arial
wdDcmnt.Range.WholeStory
With wdDcmnt.Range
.Font.Name = "Arial"
End With
' Save the Document in case of Errors
With wdDcmnt
.Save
End With
' Add Letterhead - Error occurs here
Set myLetter = wdDcmnt.CreateLetterContent( _
DateFormat:="", IncludeHeaderFooter:=True, PageDesign:= _
strTemplate, LetterStyle:=wdFullBlock, _
Letterhead:=False, LetterheadLocation:=wdLetterTop, LetterheadSize:=
_
CentimetersToPoints(0), RecipientName:="", RecipientAddress:="", _
Salutation:="", SalutationType:=wdSalutationOther,
RecipientReference:="" _
, MailingInstructions:="", AttentionLine:="", Subject:="",
CCList:="", _
ReturnAddress:="", SenderName:="", Closing:="", SenderCompany:="", _
SenderJobTitle:="", SenderInitials:="", EnclosureNumber:=0,
InfoBlock:= _
False, RecipientCode:="", RecipientGender:=wdGenderNeutral, _
ReturnAddressShortForm:="", SenderCity:="", SenderCode:="",
SenderGender _
:=wdGenderNeutral, SenderReference:="")
wdDcmnt.RunLetterWizard LetterContent:=myLetter, WizardMode:=False
' Save the Document
With wdDcmnt
.Save
End With

Exit_UpdateWordFormat:
Set myLetter = Nothing
Set wdDcmnt = Nothing
Set wdApp = Nothing
Exit Function

Err_UpdateWordFormat:
MsgBox CStr(Err) & " " & Err.Description
wdDcmnt.Close
wdApp.Quit
Resume Exit_UpdateWordFormat

End Function

 

[solex]

Gal,

A couple of things:

(1) You do not need to use the CreateObject function if you are using early
binding. Therefore either Dim wdApp As Object and use the CreateObject
function or Dim wdApp As Word.Application and then Set wdApp = New
Word.Application.
(2) I would call the following functions wdDcmnt.Close and wdApp.Quit
before you set them to nothing in your exit handler "Exit_UpdateWordFormat:"

 

[Gal Zilberman]

Thanks for the tips, I've corrected my code and removed the CreateObject but
it still works only once.
I copied the close and quit commands, but that didn't seem to help as well,
the reason I don't want Word to close is that I want the user to see the
document.

I appreciate your help

Gal

 

[solex]

Gal,

I am not familiar with the function calls you are making to word in
particular the LetterContent, but I have noticed that you do not dimension
the letter content object. For instance there is not declaration of the
sort: Dim myLetter As Word.LetterContent

Also I am assuming that you have a reference in your project set to the
Microsoft Word Object Library #.#

As far as I can tell the code with the addition of the above and the changes
I suggested earlier (with the exception of #2 since you want to keep the
document open) is syntatically correct. You could (a) ask this question in
the wordbasic group or (b) record the macro in word and see if you can see
any differences.

Regards,
Dan

 

[Gal Zilberman]

Hi Dan
Thank you for your help, I'm checking the Word newsgroup as well. I just
can't understand why would the code run once and then create an error on the
following runs (until the database is opened again).
The myLetter object is a late addition, trying to pinpoint the error, so
unfortunately it's not it as well, thanks for your time anyway

Gal

 

[Geoff]

Gal,

Is the report you export a simple table or does it contain grouping?
There might be another approach if it's a fairly simple table.

Geoff

 

[solex]

Gal,
A few more thoughts, (1) is the same file being opened when you get the
error? (2) Is the template file some how being locked in the
CreateLetterContent call. A quick check would be to run the code but make
sure you close and quit word before running again.

Dan

 

[Gal Zilberman]

Hi Geoff
The Report is built with 2 sub reports so it's a bit complex, I'm now trying
a different method by replacing bookmarked text, but I'm not sure it'll work
I'll be happy to try any idea.
thanks

Gal

 

[Gal Zilberman]

Hi Dan
Good questions, but I always close word before trying again so it's not it,
though if word is actually still opened the code will work without error
until I exit from word.
So now the code works once per DB activation, until Word is closed.

Thanks

Gal

 

[Gal Zilberman]

Hi
Just tried a different approach to the idea.
I'm now opening a new Word document with the template I wanted ( by this
discarding the problem that occurred when adding the letterhead), and then
copying the text from the exported RTF file into it while changing the text
formatting into the new document's formatting.
This works out really nice and it even makes the new document look good.
Thanks for all your help, I would still like to try new methods for this
export though.

Gal

 

[Gal Zilberman]

Hi Darryl
Here is my code, modified for your needs, I hope this is what you needed

Gal

Sub ChangeTxtToWord(strDir, strFileName As String)
On Error GoTo Err_ChangeTextToWord
' Vars
Dim wdApp As Word.Application
Dim wdTxt As Word.Document
' Create a Microsoft Word object.
Set wdApp = New Word.Application
' Make it Visible
wdApp.Visible = True
' Open the Document which you want to convert
Set wdTxt = wdApp.Documents.Open(strDir + strFileName + ".txt")
' Save the Document
wdTxt.SaveAs (strDir + strFileName + ".doc")
' Close the Document
With wdTxt
.Close
End With

Exit_ChangeTextToWord:
Set wdTxt = Nothing
Set wdApp = Nothing
Exit Sub

Err_ChangeTextToWord:
MsgBox CStr(Err) & " " & Err.Description
wdApp.Quit
Resume Exit_ChangeTextToWord

End Sub