Log to Excel

[JimmyHartington]

Hi

I have an old script from David van Driessche used in the old PowerSwitch days.
It is VB-script. See below.

It does not work anymore.
It loads fine on the script element. I can set the required properties.
But get this error

Code: Select all

Error executing script: code <0>, description <Type mismatch: 'initializeMicrosoftExcel'>, source position <51>, source text <>

Excel 2013 is installed.

Code: Select all

'==================================================================================================
' This is an example script that shows how to access information from a job and log it into an 
' excel spreadsheet
'
' ----------------------------------------------------------------
' Author:David van Driessche, Gradual Software
' Last changed: August 21, 2006
' Copyright (c) 2006 - Gradual Software
' ==================================================================================================

' ------------------------------------------------------------------------------------
' Gets a handle to Microsoft Excel
'
' Returns:   An object representing Microsoft Excel
' ------------------------------------------------------------------------------------
Public Function initializeMicraosoftExcel()
	Set initializeMicrosoftExcel = CreateObject( "Excel.Application" )
End Function


' ------------------------------------------------------------------------------------
' Finalize Microsoft Excel, quits the application and releases the reference
'
' inExcelApplication: The object representing Microsoft Excel
' ------------------------------------------------------------------------------------
Public Function finalizeMicrosoftExcel( ByRef ioExcelApplication )
	ioExcelApplication.Quit
	Set ioExcelApplication = Nothing
End Function


' ------------------------------------------------------------------------------------
' Open a given workbook (identified by a full path)
'
' inExcelApplication: The object representing Microsoft Excel
' inWorkbookPath:     The full path to the workbook
' ------------------------------------------------------------------------------------
Public Function openWorkbook( ByVal inExcelApplication, ByVal inWorkbookPath )
	Set openWorkbook = inExcelApplication.Workbooks.Open( inWorkbookPath )
End Function


' -------------------------------------------------------------------------------------
' logJobInformation
' 
' Logs all information for the current job in the given spreadsheet
' --------------------------------------------------------------------------------------
Sub logJobInformation( s, job, inSpreadsheetPath )

	' Get hold of Microsoft Excel and get the correct sheet
	Set theExcel = initializeMicrosoftExcel()
	Set theWorkbook = openWorkbook( theExcel, inSpreadsheetPath )
	Set theSpreadSheet = theWorkbook.WorkSheets("Log")
	Set thePagecount = s.getPropertyValue("page_quantity_prop")

	' Find the first free row
	theRow = 1
	Do Until (theSpreadSheet.Cells(theRow,1).Value = "")
		theRow = theRow + 1
	Loop

	' Now log various details about this job
	theSpreadSheet.Cells(theRow,1).Value = job.getName()
	theSpreadSheet.Cells(theRow,2).Value = thePagecount

	' Close down the spreadsheet and Microsoft Excel after logging this job
	theWorkbook.close True
	finalizeMicrosoftExcel( theExcel )

	' Note in the PowerSWITCH log that we did our thing
	s.log 1, "Logged: " & job.getName()

End Sub


' ------------------------------------------------------------------------------------------------
' jobArrived
' 
' Script entry point that is called for each new job that enters the input folder for this script
' ------------------------------------------------------------------------------------------------
Function jobArrived(s, job)

	' Get the information about the logfile we need to update
	Dim theSpreadsheetPath
	theSpreadsheetPath = s.getPropertyValue( "propSpreadsheetPath" )
	
	' Log the information for this job in the spreadsheet
	Call logJobInformation( s, job, theSpreadsheetPath )

	' Always send just the original job on to the output folder
	job.sendToSingle job.getPath()

End Function

Does anybody have an idea to make this work?
Thanks.

[freddyp]

There is a typo in the script:

Code: Select all

Public Function initializeMicraosoftExcel()
	Set initializeMicrosoftExcel = CreateObject( "Excel.Application" )
End Function

vs

Code: Select all

Set theExcel = initializeMicrosoftExcel()

In other words, the initialize function is not executed and there is no valid Excel object.

[JimmyHartington]

Hi Freddy

That work for some of it.

Now I get another error on the part of the string, which says how many pages are in the document.

Code: Select all

Error executing script: code <0>, description <Object required: '[string: "20"]'>, source position <54>, source text <>

20 is in this case the number of pages from the pdf. The value comes from a variable in Switch.

[avasthisamar]

Thanks for the solution.

[JimmyHartington]

Now I get another error on the part of the string, which says how many pages are in the document.

Code: Select all

Error executing script: code <0>, description <Object required: '[string: "20"]'>, source position <54>, source text <>

20 is in this case the number of pages from the pdf. The value comes from a variable in Switch.

I found the error.
Line 54:

Code: Select all

set thePagecount = s.getPropertyValue("page_quantity_prop")

If I remove the set then it works as expected.

I have modified the script to include the a field for date as well. Should be defined as a variable in Switch, so you can control the formatting yourself.

Here is the script package with a blank Excel-file to send the logs to.
https://d.pr/f/3RFTCX

[kiran sahu]

On the off chance that I evacuate the set, at that point it functions true to form. I have adjusted the content to incorporate the a field for date too. Ought to be characterised as a variable in Switch, so you can control [programming languag the arranging yourself.