• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

word running in vbscript scheduled task cannot find macro

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
 
dteday=day(date)
dteMonth=month(date)
dteyear=year(date)
Rdate=dteyear & right(cstr(dtemonth + 100),2) & right(cstr(dteday + 100),2) 
 
' make date part of file name for the week old file 
 
odate=date()-7
oyear=year(odate)
omonth=month(odate)
oday=day(odate)
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)
 
fileN=objexcel.cells(loopcount,1).Value
fileAdd=objexcel.cells(loopcount,2).Value
fileHead=objexcel.cells(loopcount,3).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)
    myfile.delete()
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)
objWord.quit
Set objWord = Nothing
'wscript.echo "File done ", filetoday,"To ",fileAdd
'**************************  send the email
result=SendMail(fileOut,fileAdd,fileHead)
 
' now delete old file from last week
If (FSO.FileExists(fileOld)) then
    set myfile= FSO.getfile(fileOld)
    myfile.delete()
End If
 
loopCount = loopCount + 1
Loop
 
objExcel.Workbooks.Close
set objExcel = nothing
 
 
Wscript.Echo "Run Complete"
wscript.quit
  
''*********************
Function SendMail(sigfile,sigto,sigsub)
 
   ' this part works fine so omitted
 
end function

Open in new window

0
BethWoodhouse
Asked:
BethWoodhouse
  • 4
  • 2
2 Solutions
 
Patrick MatthewsCommented:
Change:

 objword.run "sigpath"

to:

 objword.run "sigpath.dot!sigpath"
0
 
BethWoodhouseAuthor Commented:
Thanks for that.  I will not be able to try it out until Monday but I will let you if that solves my problem.
0
 
BethWoodhouseAuthor Commented:
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?
Beth
0
Technology Partners: 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!

 
BethWoodhouseAuthor Commented:
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...
0
 
Patrick MatthewsCommented:
BethWoodhouse,

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

Regards,

Patrick
0
 
BethWoodhouseAuthor Commented:
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
objdoc.close
 objWord.quit
Set objWord = Nothing
wscript.quit
0

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now