Solved

Unknown Runtime error when launching .vbs command

Posted on 2016-10-06
18
54 Views
Last Modified: 2016-10-25
Hi,

I have a process that runs Excel in batch mode with a .vbs routine.  That routine takes a series of arguments (input spreadsheet, output spreadsheet, path, vba file, vba module) which automatically applies formatting to a spreadsheet.

This has worked flawlessly on another system and am trying to recreate that in a new environment.  The vbs module runs and executes the vba (.bas) routine but then dies with the infamous 'Unknown runtime error':

Windows Script Host
Script:  <path>
Line: 21
Char: 3
Error: Unknown runtime error
Code: 800A9C68
Source: Microsoft VBScript runtime error

The one-line batch file that runs the launches excel is:

"c:\users\ben\launch.vbs" "c:\users\ben\noclaims.xml" "c:\users\ben\noclaims.xml" "c:\users\ben\" "noclaims" "noclaims"  

I've attached the launch.vbs routine (after renaming it to launch.txt).  Line 21 is the objxl.Run command with the comment on the right 'Run VBA code.

Any suggestions on this one?  I can include the noclaims routine if needed.  

Thanks!

--Ben
Launch.txt
0
Comment
Question by:Ben Conner
  • 12
  • 5
18 Comments
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 500 total points
ID: 41833410
Add error handling to identify the failing step. Start with:

Option Explicit

Dim SourceWorkbook, DestinationWorkbook, ModulePath, CustomModule, MethodName
Dim ExcelApplication, ExcelWorkbook, ExcelVbComponents, ExcelVbModule 
Dim ContinueProcess

ContinueProcess = ValidateArguments
If Not ContinueProcess Then
  WScript.Echo "Could not validate parameters."  
  WScript.Quit 1
End If

ContinueProcess = InitalizeExcel
If Not ContinueProcess Then
  WScript.Echo "Could not initialize Microsoft Excel."  
  WScript.Quit 1
End If

ContinueProcess = LoadWorkbook
If Not ContinueProcess Then
  WScript.Echo "Could not load source workbook."  
  WScript.Quit 1
End If

ContinueProcess = ImportModules
If Not ContinueProcess Then
  WScript.Echo "Could not import modules."  
  WScript.Quit 1
End If

ContinueProcess = RunMethod
If Not ContinueProcess Then
  WScript.Echo "Could not run method."  
  WScript.Quit 1
End If

ContinueProcess = RemoveModules
If Not ContinueProcess Then
  WScript.Echo "Could not remove modules."  
  WScript.Quit 1
End If

ContinueProcess = SaveWorkbook
If Not ContinueProcess Then
  WScript.Echo "Could not save destination workbook."  
  WScript.Quit 1
End If

WScript.Echo "Done."
WScript.Quit 0

Public Function ValdiateArguments()
  
  On Error Resume Next

  Err.Clear
  ValdiateArguments = False
  SourceWorkbook = WScript.Arguments(0) 		'Full File path and Input file name 
  DestinationWorkbook = WScript.Arguments(1) 	'Full File path and Output file name 
  ModulePath = WScript.Arguments(2) 			'Full File path Location of .bas file
  CustomModule = WScript.Arguments(3) 			'VBA module name (without the .bas)  
  MethodName = WScript.Arguments(4) 			'VBA subroutine name to execute 
  'ToDo: Test for valid parameters.
  ValdiateArguments = True

End Function

Public Function InitalizeExcel() 
  
  On Error Resume Next
 
  Err.Clear
  Set ExcelApplication = CreateObject("Excel.Application")	'Start Excel 
  ExcelApplication.DisplayAlerts = wdAlertsNone			'Turn off error messages   
  InitalizeExcel = (Err.Number = 0)
  
End Function

Public Function LoadWorkbook() 
  
  On Error Resume Next
  
  Err.Clear
  Set ExcelWorkbook = ExcelApplication.Workbooks.Open(SourceWorkbook)	'Open the input file     
  LoadWorkbook = (Err.Number = 0)
  
End Function

Public Function ImportModules()
  
  On Error Resume Next
  
  Err.Clear
  Set ExcelVbComponents = objxl.ActiveWorkbook.VBProject.VBComponents     
  If CustomModule <> "" Then 
    ExcelVbComponents.Import ModulePath & "Common.bas"			'Import Common Routines
    ExcelVbComponents.Import ModulePath & CustomModule & ".bas"	'Import VBA Code  
  End If
  
  ImportModules = (Err.Number = 0)  
  
End Function

Public Function RunMethod()
  
  On Error Resume Next

  Err.Clear
  If CustomModule <> "" And MethodName <> "" Then 
    ExcelApplication.Run CustomModule & "." & MethodName	'Run VBA Code
  End If
  
  RunMethod = (Err.Number = 0)
  
End Function

Public Function RemoveModules()
  
  On Error Resume Next

  Err.Clear
  If CustomModule <> "" Then 
    Set ExcelVbModule = ExcelApplication.ActiveWorkbook.VBProject.ExcelVbComponentsponents(CustomModule) 
    ExcelWorkbook.VBProject.ExcelVbComponentsponents.Remove ExcelVbModule
    Set ExcelVbModule = ExcelApplication.ActiveWorkbook.VBProject.ExcelVbComponentsponents("Common") 
    ExcelWorkbook.VBProject.ExcelVbComponentsponents.Remove ExcelVbModule
  End If
  
  RemoveModules = (Err.Number = 0)
  
End Function

Public Function SaveWorkbook()
  
  On Error Resume Next

  Err.Clear  
  If SourceWorkbook = DestinationWorkbook Then 
    ExcelApplication.ActiveWorkbook.Save 
  Else
    ExcelApplication.ActiveWorkbook.SaveAs DestinationWorkbook, 51	' 56=xlExcel8, 51=xlsx format 
  End If
  
  SaveWorkbook = (Err.Number = 0)
  
End Function

Public Function FinalizeExcel()

  On Error Resume Next

  Err.Clear  
  ExcelApplication.Workbooks.Open(DestinationWorkbook).Close 
  ExcelApplication.Quit
  set ExcelApplication = nothing
  set ExcelWorkbook = nothing
  FinalizeExcel = (Err.Number = 0)
  
End Function

Open in new window

0
 

Author Comment

by:Ben Conner
ID: 41835167
Thanks, will do that on Tuesday.  Had 4 days off.  Yes!

--Ben
0
 

Author Comment

by:Ben Conner
ID: 41842076
I asked the coworker running the job to kick this off yesterday for debugging.  Don't think that happened.  Will check again today.

--Ben
0
 

Author Comment

by:Ben Conner
ID: 41842521
Was finally able to get to the workstation to run the test code.  I called it test.vbs.  It didn't like the 'ValidateArguments' parameter.
Line 7
Char 1
Error: Variable is undefined: 'ValidateArguments'
Code: 800A01F4
Source: Microsoft VBScript runtime error

--Ben
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41842587
Correct the typo.
0
 

Author Comment

by:Ben Conner
ID: 41842640
Sorry, I'm not as conversant with vbs code as you are.  What should it be?

--Ben
0
 

Author Comment

by:Ben Conner
ID: 41842644
Oh!  Never mind; I see it now.  Sorry.
0
 

Author Comment

by:Ben Conner
ID: 41843665
Results of the diagnostic run throws the error that it couldn't execute Excel.  

When I run the original code though, Excel does execute and it runs the .bas routine to completion.  Then throws the error.  ?

--Ben
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 32

Expert Comment

by:ste5an
ID: 41843696
I just copied your code. Where do you declare wdAlertsNone?
0
 

Author Comment

by:Ben Conner
ID: 41843760
In the code:
objxl.DisplayAlerts = wdAlertsNone    'Turn off error messages

the wdAlertsNone is a predefined value supplied by MS.  It is often seen in statements like:

Application.DisplayAlerts = wdAlertsNone

Or am I missing the question?  I didn't write the code and am not familiar with several statements in it.  My main exposure is in formatting with .bas routines.

--Ben
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41843804
Or am I missing the question?

It may be predefined in Excel, but not in a VBScript file. It's the same as

ExcelApplication.ActiveWorkbook.SaveAs DestinationWorkbook, 51	' 56=xlExcel8, 51=xlsx format 

Open in new window

0
 

Author Comment

by:Ben Conner
ID: 41843877
Oh.  Looks like it is a value of 0.  Should I replace that with 0 and see if it works?

--Ben
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41843966
Sure.
0
 

Author Comment

by:Ben Conner
ID: 41844142
Asked my coworker to run it with that tweak.  Will let you know what happens.

Thanks!

--Ben
0
 

Author Comment

by:Ben Conner
ID: 41845916
She ran it Friday.  No change in result.

--Ben
0
 

Accepted Solution

by:
Ben Conner earned 0 total points
ID: 41852411
I stumbled across the root cause of the error this morning.

The launch routine has an input and output file and applies a .bas formatting routine.  The intent was to be able to do formatting of spreadsheets in an automated control.

This .bas routine was different: its intent was to spin off new spreadsheets from the input file.  Once done, it didn't care about the input file.  So in the .bas routine, I closed the input file and returned control back to the launch routine.  Bad idea.... the launch code assumes the input file is still open and when it isn't, the result was what I saw.

While I might be able to modify launch.vbs to account for this, the path of least resistance was to comment out the close statements in the .bas code.  Problem solved.

Thanks for all the help!

--Ben
0
 

Author Closing Comment

by:Ben Conner
ID: 41858251
Found the root cause of the error I had asked about.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now