word running in vbscript scheduled task cannot find macro

Posted on 2008-10-30
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"
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
  • 4
  • 2
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22849251
Change: "sigpath"

to: "!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 then saved it as  Is the attachedtemplate the correct way to make the macro available? Is there something else I should be doing?
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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  Such as the line below. "C:\Documents and Settings\Application Data\Microsoft\Templates\!sigpath"

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

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 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"
objdoc.SaveAs("C:\Inetpub\wwwroot\internal\sigpath\COFSIG_20081114.doc"), wdFormatDoc
Set objWord = Nothing

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Logon script fails 23 45
VBScript on Html 15 47
reboot server with scheduled time and week base 4 36
Excel 2007 Macro to Change Column Formatting 3 35
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

831 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