Solved

word running in vbscript scheduled task cannot find macro

Posted on 2008-10-30
6
676 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help me. 3 54
copy the content of word to outlook 3 60
Child Form in front 4 46
Set email body to html using vbscript 6 41
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.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

770 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