Solved

Unknown Runtime error when launching .vbs command

Posted on 2016-10-06
18
70 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
18 Comments
 
LVL 34

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 34

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
 
LVL 34

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 34

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 34

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Changing a few Outlook Options can help keep you organized!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

623 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