Problem with Excel Macro using WINWORD.exe

Posted by M. Kilkelly on June 04, 2001 12:41 PM

I have an auto_open macro that I use to automatically format the printer setup once a worksheet is opened. I am creating an instance of the Word.application object to use the PrivateProfileString function to write the print configurations to the printer's .ini file. This macro is used as part of a system that automatically foramts and prints Excel files whenever they are uploaded to a certain directory.

The problem that I'm having is that whenever this macro is run, a WINWORD.exe process is created but it is not recycled when Excel is closed. If ten documents are uploaded, then I have ten instances of WINWORD.exe running, which is tieing up a lot of resources and giving me "out of memory" errors.

Does anyone know of a way force the macro to terminate the WINWORD processes after the marco is run? Thanks.

Posted by Eric on June 04, 2001 1:18 PM

Sorry to step on your post, but HELP

I suddenly find I cannot start new posts, so I hope someone notices this one and reposts it for me, thanks (and my appologies M. Killkelly).
Column (A) has 1400 rows of data. I want the average of every 7 rows (A1:A7, A8:A15, etc.).
So far what I've thought of is to put in a column (B) that has the count from 1 to 1400 by sevens (1,8,15,22,...) and copy down =average(a1:a7) from C1 to C1400. Then use =index(c1:c1400,b1) in column (D) and copy down. Is there an easier way?

Posted by M. Kilkelly on June 04, 2001 1:21 PM

I neglected to post the code I'm using. The following function creates the instance of the Word.Application object and is used to modify the printer's .ini file.

Function SetIniSetting(Filename As String, Section As String, key As String, keyValue) As Boolean
Dim wd As Word.Application
SetIniSetting = False
'CREATE NEW WORD APPLICATION OBJECT
Set wd = New Word.Application
On Error Resume Next
wd.System.PrivateProfileString(Filename, Section, key) = CStr(keyValue)
On Error GoTo 0
Set wd = Nothing
SetIniSetting = True
End Function

Posted by Dax on June 04, 2001 1:30 PM

Hi,

I take it you are using CreateObject("Word.Application") or Set wdapp=New Word.Application to create an instance of Word. You could either use the same instance of Word for each document (there is a post by me in response to a question from Michelle) regarding this (this would also be quicker as you wouldn't be opening a whole new application for each document) or you could use something like this: - (assumes you have set a reference to the Word object library)

Set Wdapp=New Word.Application
Do you processing
Wdapp.Quit
Set Wdapp=Nothing

This will both quit Word and destroy the object reference so any memory associated with that object is released.

HTH,
Dax.

Posted by Barrie Davidson on June 04, 2001 2:01 PM

Re: Sorry to step on your post, but HELP

Eric, have a look at
17835.html
and let me know if you can adapt this.

Barrie

Posted by M. Kilkelly on June 04, 2001 3:04 PM

Thanks Dax. Worked like a charm.

-Michael

Posted by Eric on June 05, 2001 7:27 AM

Thanks, very enlightening

Barrie,
Thanks for pointing me at your earlier post. I tried fishing through all of the posts earlier and got a little overwhelmed. I was looking for a 1-column solution (see the repost Aladin did for me above this post- I feel like a jerk writing all over M. Killkelly's post) but showing me that INDIRECT command has opened up a lot of options for me, thank you.
Eric