Solved

word running in vbscript scheduled task cannot find macro

Posted on 2008-10-30
6
677 Views
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
 
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
Comment
Question by:BethWoodhouse
  • 4
  • 2
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22849251
Change:

 objword.run "sigpath"

to:

 objword.run "sigpath.dot!sigpath"
0
 

Author Comment

by:BethWoodhouse
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.
0
 

Author Comment

by:BethWoodhouse
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?
Beth
0
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

by:BethWoodhouse
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...
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 22907316
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
 

Accepted Solution

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

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