Excel UDF Question

[Kaimberex]

Okay I have a snippet of code that is working on the first try but If I try the function a second time I get an error. Not sure what I am doing wrong. Probably something simple! Any assistance would be appreciated!

Local $oExcel = _Excel_Open()
    Local $sFilePath = @ScriptDir & "\ComputerName.xls"
    Local $workbook = _Excel_BookOpen($oExcel, $sFilePath)

Local $aArray2D[1][5] = [[$Serial, $ComputerName, $Domain, $Username, $Password]]
    _Excel_RangeInsert($workbook.activesheet, "A2:E2", $xlShiftDown)
    _Excel_RangeWrite($workbook, $workbook.Activesheet, $aArray2D, "A2")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    _Excel_BookSave($workbook)
    Sleep(250)

    _Excel_Close($oExcel,False,True)

 

This simple function takes inputs from a GUI that I made and inserts the data into an already existing workbook. 

but once it runs through one time if I try to do it a second time with new data I receive the below error:

 

"C:\PROGRA~2\AutoIt3\Include\Excel.au3" (227) : ==> Variable must be of type "Object".:
$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR

 

Not sure what I am missing. I am using the latest version of autoit. 

 

[water]

It's hard to tell where the error occurs because you do not do any error checking in your script.
Please check the help file to see how it should be done.

[Kaimberex]

14 minutes ago, water said:

It's hard to tell where the error occurs because you do not do any error checking in your script.
Please check the help file to see how it should be done.

I believe the script was running too fast. I increased the sleep() timer to 2000ms and now the function runs as expected.

[water]

Which version of Office do you run?

[Kaimberex]

1 minute ago, water said:

Which version of Office do you run?

Office 2010

[Kaimberex]

16 minutes ago, Kaimberex said:

I believe the script was running too fast. I increased the sleep() timer to 2000ms and now the function runs as expected.

I kept having issues after the second time running it wouldn't close the previous instance and write the new data. I put some small pauses in there and it seems to work now.

 

Local $oExcel = _Excel_Open();False,False,Default,Default,True)
    Local $sFilePath = @ScriptDir & "\ComputerName.xls"
    Local $workbook = _Excel_BookOpen($oExcel, $sFilePath)
    Local $aArray2D[1][5] = [[$Serial, $ComputerName, $Domain, $Username, $Password]]
    _Excel_RangeInsert($workbook.activesheet, "A2:E2", $xlShiftDown)
    Sleep(500)
    _Excel_RangeWrite($workbook, $workbook.Activesheet, $aArray2D, "A2")
    
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Sleep(500)
    _Excel_BookSave($workbook)
    Sleep(500)
    _Excel_BookClose($workbook)
    Sleep(500)
    _Excel_Close($oExcel)

 

[water]

Compared to the original code you added a call of _Excel_BookClose.
AFAIK Excel functions are synchronous so there should be no need to call Sleep.

I will check tomorrow in my office.

[water]

I made a working script from your code snippet and it worked without any probelm with AutoIt 3.3.12.0 and 3.3.15.0 with Office 2010 on Windows 7.

#include <Excel.au3>
Global $Serial = @HOUR & ":" & @MIN & ":" & @SEC, $ComputerName = "Test", $Domain = "MyDomain", $Username = @UserName, $Password = "Secret"
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $sFilePath = @ScriptDir & "\ComputerName.xls"
Local $workbook = _Excel_BookOpen($oExcel, $sFilePath)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error opening to workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $aArray2D[1][5] = [[$Serial, $ComputerName, $Domain, $Username, $Password]]
_Excel_RangeInsert($workbook.activesheet, "A2:E2", $xlShiftDown)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error inserting to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_RangeWrite($workbook, $workbook.Activesheet, $aArray2D, "A2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSave($workbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookClose($workbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error closing workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)

 

[Kaimberex]

6 hours ago, water said:

I made a working script from your code snippet and it worked without any probelm with AutoIt 3.3.12.0 and 3.3.15.0 with Office 2010 on Windows 7.

#include <Excel.au3>
Global $Serial = @HOUR & ":" & @MIN & ":" & @SEC, $ComputerName = "Test", $Domain = "MyDomain", $Username = @UserName, $Password = "Secret"
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $sFilePath = @ScriptDir & "\ComputerName.xls"
Local $workbook = _Excel_BookOpen($oExcel, $sFilePath)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error opening to workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $aArray2D[1][5] = [[$Serial, $ComputerName, $Domain, $Username, $Password]]
_Excel_RangeInsert($workbook.activesheet, "A2:E2", $xlShiftDown)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error inserting to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_RangeWrite($workbook, $workbook.Activesheet, $aArray2D, "A2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSave($workbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookClose($workbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error closing workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)

 

that seemed to work by adding in more error checking. I removed the sleep calls from the original snippet and updated the error checking and that works. I think my original issue was I was closing excel without closing the book and it was still hung up in memory. After adding the excel book close function it works after repeated attempts. 

Only difference is I am on Autoit Version 3.3.14.2 not sure if that makes a difference. 

 

Edited August 9, 2016 by Kaimberex
typos

[water]

Glad the problem could be solved
The version of AutoIt shouldn't make a difference.
 

[Kaimberex]

I would like everything to run behind the scenes without making the excel visible during the data entry; however when I make it not visible and save it, when I go to open it in windows explorer the document will not show the workbook it is still not visible. 

[water]

Either set the Workbook to visible again before saving or do not set the workbook to invisible but the whole Excel application with _Excel_Open.

Edited August 9, 2016 by water

[Kaimberex]

33 minutes ago, water said:

Either set the Workbook to visisble again before saving or do not set the workbook to invisible but the whole Excel application with _Excel_Open.

its always the simple things.

 

I used 

Local $oExcel = _Excel_Open(False)

to make it not visible and I also used

Local $workbook = _Excel_BookOpen($oExcel, $sFilePath,Default,True,Default,Default,Default)

and it works the first time but a second try causes it to fail. No error from the script theres just an error on line 227 in Excel.au3

$oExcel.Windows($oWorkbook.Name).Visible = $bVisible

This is the console output:

"C:\PROGRA~2\AutoIt3\Include\Excel.au3" (227) : ==> Variable must be of type "Object".:
$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR

The second set of data is never entered into the workbook and excel is hung in memory. If I close Excel from memory and then run script again it works the first try but any other attempts cause it to fail with the issues above.

 

Its not the end of the world if the users see the workbook opening I was just hoping to make it invisible to the technician who will be using this application. 

If I just use 

Local $oExcel = _Excel_Open()

and 

Local $workbook = _Excel_BookOpen($oExcel, $sFilePath)

Everything works as intended. Like I said not the end of the world. 

Edited August 9, 2016 by Kaimberex
typos

[Kaimberex]

8 minutes ago, Kaimberex said:

its always the simple things.

 

I used 

Local $oExcel = _Excel_Open(False)

to make it not visible and I also used

Local $workbook = _Excel_BookOpen($oExcel, $sFilePath,Default,True,Default,Default,Default)

and it works the first time but a second try causes it to fail. No error from the script theres just an error on line 227 in Excel.au3

$oExcel.Windows($oWorkbook.Name).Visible = $bVisible

This is the console output:

"C:\PROGRA~2\AutoIt3\Include\Excel.au3" (227) : ==> Variable must be of type "Object".:
$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR

The second set of data is never entered into the workbook and excel is hung in memory. If I close Excel from memory and then run script again it works the first try but any other attempts cause it to fail with the issues above.

 

Its not the end of the world if the users see the workbook opening I was just hoping to make it invisible to the technician who will be using this application. 

If I just use 

Local $oExcel = _Excel_Open()

and 

Local $workbook = _Excel_BookOpen($oExcel, $sFilePath)

Everything works as intended. Like I said not the end of the world. 

think its working now as I did not use any additional parameters on _ExcelBookOpen

 

Local $oExcel = _Excel_Open(False) ;this makes it invisible
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $sFilePath = @ScriptDir & "\ComputerName.xls"
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error opening to workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $workbook = _Excel_BookOpen($oExcel, $sFilePath);no need to use additional parameters, parameters are using default values.
    Local $aArray2D[1][5] = [[$Serial, $ComputerName, $Domain, $Username, $Password]]
    _Excel_RangeInsert($workbook.activesheet, "A2:E2", $xlShiftDown)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error inserting to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_RangeWrite($workbook, $workbook.Activesheet, $aArray2D, "A2")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_BookSave($workbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_BookClose($workbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error closing workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)

 

[water]

I think I know what is going on:
When the application is set to invisible then there are no windows that can be set to invisible with _Excel_BookOpen.
Think I need to add a line to the documentation of _Excel_BookOpen or modify the function.

[Kaimberex]

15 minutes ago, water said:

I think I know what is going on:
When the application is set to invisible then there are no windows that can be set to invisible with _Excel_BookOpen.
Think I need to add a line to the documentation of _Excel_BookOpen or modify the function.

Yeah if I don't use the invisible parameter on the _Excel_BookOpen function I just use the invisible parameter on _ExcelOpen(False) it works as intended. and lets me open the workbook in windows explorer to verify the data was entered and the workbook is visible. The function also works a second time. 

Local $oExcel = _Excel_Open(False) ;this makes it invisible
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $sFilePath = @ScriptDir & "\ComputerName.xls"
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error opening to workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $workbook = _Excel_BookOpen($oExcel, $sFilePath);no need to use additional parameters, parameters are using default values.
    Local $aArray2D[1][5] = [[$Serial, $ComputerName, $Domain, $Username, $Password]]
    _Excel_RangeInsert($workbook.activesheet, "A2:E2", $xlShiftDown)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error inserting to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_RangeWrite($workbook, $workbook.Activesheet, $aArray2D, "A2")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_BookSave($workbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_BookClose($workbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Test", "Error closing workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)

 

[Kaimberex]

Let me know if I should open a new thread for this but it is still regarding the ExcelUDF.

I am trying to use the _Excel_RangeFind() function and it is returning an error. for now I am just using the example from the help file. 

 

Console returns this error on the Example Script

 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Find all occurrences of value "37000" (partial match)
; *****************************************************************************
Local $aResult = _Excel_RangeFind($oWorkbook, "37000")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

Console Output:

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

 

[water]

That's a bug in Autoit 3.3.14.2 or later.
You need to modify the UDF and remove those statements in function _Excel_RangeFind causing errors.

[Kaimberex]

Just now, water said:

That's a bug in Autoit 3.3.14.2 or later.
You need to modify the UDF and remove those statements in function _Excel_RangeFind causing errors.

Thanks I literally just found your response in another thread. I will try updating my autoit version. 

[water]

It works with AutoIt 3.3.12.0.