Jump to content

_Excel_BookOpen Returns wrong Workbook


Recommended Posts

I had two Workbooks (U:\Book1.xlsx and U:\Book2.xlsx) open 

I ran this code...

#include <Excel.au3>

$application = _Excel_Open()
If @error Then
    Msgbox(0, "Application Error!", "No error should be here")
EndIf

$wbook = _Excel_BookOpen($application, "U:\Book1.xlsx")
If @error Then
    MsgBox(0,"Error", "Can't open workbook! Error: " & @error)
Else
    Msgbox(0, "Got Workbook", "Workbook name: " & $wbook.name)
EndIf

And got no error, but the MsgBox said "Workbook name: Book2.xlsx". I checked in the Visual Basic editor and both Workbooks were in the same application, so if this is me or if it's a bug.

(This wasn't my original code, but I simplified it and retested so I could be sure it wasn't my own stupidity and so anybody else could run it)

 

AutoIt Version: 3.3.14.5

Windows 7 Enterprise

MS Excel for Office 365 ProPlus (Version 1808)

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites

Try _Excel_BookAttach like this:

#include <Excel.au3>
Local $oWorkbook, $sWorkbook1 = "U:\Book1.xlsx", $sWorkbook2 = "U:\Book2.xlsx"
Local $oExcel = _Excel_Open()
If @error Then
    Msgbox(0, "Application Error!", "No error should be here")
EndIf

$oWorkbook = _Excel_BookAttach($sWorkbook1)
If @error Then
    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook1)
    If @error Then
        MsgBox(0,"Error", "Can't open workbook! Error: " & @error)
    EndIf
EndIf
If IsObj($oWorkbook) Then Msgbox(0, "Got Workbook", "Workbook name: " & $oWorkbook.name)

 

Edited by Subz
Link to post
Share on other sites

@FrancescoDiMuro, just checked, no properties set there.

@Subz I hadn't thought of that... just tried and it does the same thing :(

Edit: It does hit the error code and try to BookOpen instead of BookAttach

Edited by seadoggie01

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites

@Subz That kinda fixed it... it matters which file I open first. If I open Book2 before Book1, I get Book1. I'm trying to look into this more with a second computer on Win10 now

Edit: It happens on both computers. It's not related to the OS I think. Both use the same version of AutoIt though.

Edited by seadoggie01

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites
#include <Excel.au3>
Local $oWorkbook
Local $sTargetWorkbook = "U:\Book4.xlsx"
Local $sOtherWorkook = "U:\Book3.xlsx"
ShellExecute($sTargetWorkbook)
Sleep(5000)
ShellExecute($sOtherWorkook)
Sleep(5000)
Local $oExcel = _Excel_Open()
If @error Then
    Msgbox(0, "Application Error!", "No error should be here")
EndIf

$oWorkbook = _Excel_BookAttach($oExcel, $sTargetWorkbook)
If @error Then
    ConsoleWrite("Can't attach. Opening." & @CRLF)
    $oWorkbook = _Excel_BookOpen($oExcel, $sTargetWorkbook)
    If @error Then
        MsgBox(0,"Error", "Can't open workbook! Error: " & @error)
    EndIf
EndIf
If IsObj($oWorkbook) Then Msgbox(0, "Got Workbook", "Found: " & $oWorkbook.name & @CRLF & "Target: " & $sTargetWorkbook)

^  I get the $sOtherWorkbook instead of the $sTargetWorkbook. Change the 5 seconds if excel launches quickly for you (Me work computer big heap junk:D)

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites
19 minutes ago, Subz said:

Sorry I had another brain fade (not enough sleep):

$oWorkbook = _Excel_BookAttach($oExcel, $sTargetWorkbook)

Should have been

$oWorkbook = _Excel_BookAttach($sTargetWorkbook)

 

Thank you!!! That works now :D I'm not sure why BookOpen wouldn't work, but at least there is another option.

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...