word running in vbscript scheduled task cannot find macro

Posted on 2008-10-30
Medium Priority
Last Modified: 2012-05-05
I have a vbscript job running as a scheduled task on a server.  It is creating  a word object, opening a file and then running/trying to run a macro.  If I am logged on to the server, it all works fine but if I am not running a session on the server, the scheduled job does not find the necessary macro.  Where should I put this macro so that it will be found by the job?
I have created a template containing just my macro and tried putting it in many places. I have tried the AttachedTemplate action to invoke the template.
Some code is attached.
'sample code
set FSO = CreateObject("Scripting.FileSystemObject")
' build todays date
Rdate=dteyear & right(cstr(dtemonth + 100),2) & right(cstr(dteday + 100),2) 
' make date part of file name for the week old file 
OLDdt=oyear & right(cstr(omonth + 100),2) & right(cstr(oday + 100),2)
Dim loopCount, objExcel, workbook
dim fileN, fileAdd, fileHead
Set objExcel = CreateObject("Excel.Application")
'Open our XLS file
Set workbook = objExcel.Workbooks.Open("C:\Sigpath\sigpath.xls")
'We want to skip the header row, and then the blank row below
loopCount = 2 ' ignore row 1 headings
' now loop though files, convert document with word and then send using email addresses provided
Do while not isempty(objExcel.Cells(loopCount, 1).Value)
'wscript.echo fileN & fileAdd & FileHead
FileToday=fileN & "_" & Rdate & ".txt"
FileOld="C:\SigPathdone\" & fileN & "_" & OLDdt & ".doc"
FileOut=fileN & "_" & Rdate & ".doc"
' now delete current output file if it exists 
If (FSO.FileExists(fileOut)) then
    set myfile= FSO.getfile(fileOut)
End If
' edit the file
dim objWord,objdoc
set objWord = CreateObject("Word.Application")
objword.Visible = True
set objdoc = objWord.Documents.Open ("C:\sigpath\" & fileToday, wdReadOnly)
objword.Activedocument.attachedTemplate = "C:\sigpath.dot"
 objword.run "sigpath"
objdoc.SaveAs("C:\sigpathdone\" & fileOut)
objdoc.close (wdOriginalFormat)
Set objWord = Nothing
'wscript.echo "File done ", filetoday,"To ",fileAdd
'**************************  send the email
' now delete old file from last week
If (FSO.FileExists(fileOld)) then
    set myfile= FSO.getfile(fileOld)
End If
loopCount = loopCount + 1
set objExcel = nothing
Wscript.Echo "Run Complete"
Function SendMail(sigfile,sigto,sigsub)
   ' this part works fine so omitted
end function

Open in new window

Question by:BethWoodhouse
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22849251

 objword.run "sigpath"


 objword.run "sigpath.dot!sigpath"

Author Comment

ID: 22850413
Thanks for that.  I will not be able to try it out until Monday but I will let you if that solves my problem.

Author Comment

ID: 22870915
This still did not work.  I think it must be my lack of understanding about templates with word.  I put the maco in normal.dot then saved it as sigpath.dot.  Is the attachedtemplate the correct way to make the macro available? Is there something else I should be doing?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 22900141
The macro only seems to run when I am logged on the ther server where this job runs as a scheduled task.   It appears that it does not find the macro otherwise.  I thought that I had it running reliably by using the full path to the macro in normal.dot.  Such as the line below.  
objword.run "C:\Documents and Settings\Application Data\Microsoft\Templates\Normal.dot!sigpath"

However it has failed again at 5am when nobody is working......
Help please...
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 22907316

I suggest you use the 'request attention' link to ask for help in getting more experts.  I am out of ideas on this one.



Accepted Solution

BethWoodhouse earned 0 total points
ID: 22958842
I have reduced this job to be a simple script which works when run as a script but hangs when run as a scheduled task without the user logged on to the server.

.....................code below.................................
set objWord = CreateObject("Word.Application")
set objdoc = objWord.Documents.Open ("Z:\COFSIG_20081114.txt")
objdoc.attachedtemplate = "C:\Sigpath\sigpath.dot"
objword.run "Sigpath.dot!SigPath"
objdoc.SaveAs("C:\Inetpub\wwwroot\internal\sigpath\COFSIG_20081114.doc"), wdFormatDoc
Set objWord = Nothing

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question