Kaimberex

Excel UDF Question

24 posts in this topic

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. 

 

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Which version of Office do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
1 minute ago, water said:

Which version of Office do you run?

Office 2010

Share this post


Link to post
Share on other sites
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)

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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)

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by Kaimberex
typos

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by Kaimberex
typos

Share this post


Link to post
Share on other sites
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)

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

It works with AutoIt 3.3.12.0.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now