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

Unknown Runtime error when launching .vbs command

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
Ben Conner
Asked:
Ben Conner
  • 12
  • 5
2 Solutions
 
ste5anSenior DeveloperCommented:
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
 
Ben ConnerCTO, SAS developerAuthor Commented:
Thanks, will do that on Tuesday.  Had 4 days off.  Yes!

--Ben
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
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
Industry Leaders: 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!

 
Ben ConnerCTO, SAS developerAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
Correct the typo.
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
Sorry, I'm not as conversant with vbs code as you are.  What should it be?

--Ben
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
Oh!  Never mind; I see it now.  Sorry.
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
I just copied your code. Where do you declare wdAlertsNone?
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Ben ConnerCTO, SAS developerAuthor Commented:
Oh.  Looks like it is a value of 0.  Should I replace that with 0 and see if it works?

--Ben
0
 
ste5anSenior DeveloperCommented:
Sure.
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
Asked my coworker to run it with that tweak.  Will let you know what happens.

Thanks!

--Ben
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
She ran it Friday.  No change in result.

--Ben
0
 
Ben ConnerCTO, SAS developerAuthor Commented:
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
 
Ben ConnerCTO, SAS developerAuthor Commented:
Found the root cause of the error I had asked about.
0

Featured Post

Industry Leaders: 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!

  • 12
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now