namdog

Possible _Excel_BookOpen bug

5 posts in this topic

#1 ·  Posted

I'm wondering if there's a known bug with the _Excel_BookOpen function?  I'm using Excel to automate reports.  In short, this program will read through spreadsheets, copy the data from those opened then paste to a master workbook for a remote site.  There are 5 remote sites right now that we need to run these reports on.  I have a function that will read the name of the report, then dump the data into the master workbook.sheets("report name"), concatenate, copy that data to the Users sheet, remove all duplicates and sort, then copy to the Master List sheet in that workbook.  Essentially showing who at that site has access using embedded Excel formulas.  There could be anywhere from 5-20 reports per site.

Regardless of how I set this up, I'm running into the same error:

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

This is pointing back to the _Excel_BookOpen function, however, I cannot find a way around this.  Initially, I thought this may be due to the workbook I was attempting to open, but it is not.  I tested this theory by calling the same function with the same parameters multiple times and received the exact same output.  It will run through for the first 3 function calls, but when it gets to Site4, it fails 3 reports in.  Memory usage and processor usage are normal and are not spiking and nomenclatures are correct.  I am also clearing the clipboard in between calls.  I have included a snippet of the code, but due to company policy, I cannot include any of the excel documents.  I know this is long winded, but any suggestions would be greatly appreciated.

reports.au3

Share this post


Link to post
Share on other sites



#2 ·  Posted

There is a pinned thread at the top of the General Help and Support forum. It contains fixed versions of some Excel functions. 


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

#3 ·  Posted

The updates seem to have taken care of the initial problem.  However now, it's failing when calling the _Excel_RangeRead during the 4th function call, even when using the same workbook.  Any suggestions?

 

someprogram_Test.au3" (289) : ==> The requested action with this object has failed.:
$numList = _Excel_RangeRead($mWorkbook, $mWorkbook.Sheets("Users"))
$numList = _Excel_RangeRead($mWorkbook, $mWorkbook^ ERROR

Share this post


Link to post
Share on other sites

#4 ·  Posted

I ended up finding a workaround though it's not preferable.  I had to compile each function into a separate .exe files.  Doing this and having the initial script calling these separate .exe files appears to work.  It is a little slower, but it works.  I'll keep a look out for future Excel updates.  Thanks again for your help.

Share this post


Link to post
Share on other sites

#5 ·  Posted

You could add a COM error handler to your script and check the detailed error Information you get.


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

  • Similar Content

    • SorryButImaNewbie
      By SorryButImaNewbie
      Hello dear users!
      I have a bit of a problem with my excel script I try to make. My goal is that my script would read through an excelfile's columns and rows, identifiying key words, then I would like to reorganize the rows and colums in the a specified "way", "format" (we get different kind of excels in and i wish to produce a uniform excels from them, the data is the same, so I know that there is an XY column somewhere, I just have to find it, and put it in the right place)
      Now I can read values from cells (or range of cells) but only if I know the exact file name and path.
      Can I make autoit to somehow attach to the already opened excel? This way I would be able to make the script, so the user just open the excel which he/she wants to format, clicks on go, and viola.
      Instead now I'm thinking adding 2 textboxes to my GUI so the user can write down the filename and file path, which isnt that ideal.
      My original idea, was to make the script find the "borders" of the filled out excel form, copy it away to the right, delete the original data, reorganize it the way it should be (to the area which it cleared), and thats it. The user can then quickly check if everything is okey, and he/she can delete the copied original data set if its needed.
       
      So in short my problem is, how to connect my script to an excel thats presumaby already opened, without knowing the filename and path?
      Thank you for your help! (and sorry if there is already a post about this I havent found it)