ADJH

_Excel_BookOpen not returning anything

4 posts in this topic

Hi All,

I never thought I would be this person, but after hours of googling and forum searching I just can't find the answer to the following issue (please note I am a newbie working with someone else's script).

The script in question opens up a menu that when specific buttons are pressed, either an Excel workbook or directory folder is opened. This was working well on previous versions of Windows and AutoIT, however recent PC changes seems to have disrupted things a bit.

To be clear, the GUI opens and works. The buttons can be pressed, and the GUI closes afterwards, but the desired Excel workbook does not open, nor are there any error messages. The filepath that is entered is correct and exists.

The code is as follows:

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=ico\ADlogo.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
; Includes the GuiConstants (required for GUI function usage)
#include <GuiConstants.au3>
#include <Excel.au3>
#include <Word.au3>

; GUI Creation
GuiCreate(" AD Quick Links", 400, 320)
; Button1
$button1 = GUICtrlCreateButton("Open Projects & Proposals Register", 50, 20, 300, 30)

GUISetState(@SW_SHOW)
Func Function1()
    _Excel_BookOpen(_Excel_Open, "R:\Project Register-New Project & Proposal Numbers.xls")
; Hides the GUI while the function is running
;GUISetState(@SW_HIDE)
Exit
; ================
; The script you would like to perform for Button1 goes here
; ================
EndFunc

While 1
$guimsg = GuiGetMsg()
Select
Case $guimsg = $GUI_EVENT_CLOSE
Exit ; closes the GUI
Case $guimsg = $button1
function1(); runs Button1 function

EndSelect
WEnd

 

Apologies if the above looks likes a mess. Any assistance as to why the workbook is not opening up would be greatly appreciated.

Regards,

Justin

Share this post


Link to post
Share on other sites



Try:

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=ico\ADlogo.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
; Includes the GuiConstants (required for GUI function usage)
#include <GuiConstants.au3>
#include <Excel.au3>
#include <Word.au3>

; GUI Creation
GuiCreate(" AD Quick Links", 400, 320)
; Button1
$button1 = GUICtrlCreateButton("Open Projects & Proposals Register", 50, 20, 300, 30)

GUISetState(@SW_SHOW)

While 1
$guimsg = GuiGetMsg()
Select
Case $guimsg = $GUI_EVENT_CLOSE
Exit ; closes the GUI
Case $guimsg = $button1
function1(); runs Button1 function

EndSelect
WEnd

Func Function1()
    Local $oExcel = _Excel_Open()
        If @error Then Exit
    Local $oWorkBook = _Excel_BookOpen($oExcel, "R:\Project Register-New Project & Proposal Numbers.xls")
    ; Hides the GUI while the function is running
    ;GUISetState(@SW_HIDE)
    Exit
    ; ================
    ; The script you would like to perform for Button1 goes here
    ; ================
EndFunc

 

Share this post


Link to post
Share on other sites

Thank you!

That worked perfectly. I will now apply that to all of the other buttons on the list that I left off the example.

I noticed you added this:

While 1
$guimsg = GuiGetMsg()
Select
Case $guimsg = $GUI_EVENT_CLOSE
Exit ; closes the GUI
Case $guimsg = $button1
function1(); runs Button1 function

EndSelect
WEnd

Func Function1()
    Local $oExcel = _Excel_Open()
        If @error Then Exit
    Local $oWorkBook = _Excel_BookOpen($oExcel, "R:\Project Register-New Project & Proposal Numbers.xls")

 

May I ask what the above is doing? It looks like you're pointing out the fact that it's a file on the local PC. Is that correct?

 

Thank you again. My whole office thanks you in fact.

Share this post


Link to post
Share on other sites

Really just moved the function from the GUI code just to make it cleaner.

If you have multiple buttons for different spreadsheets I would suggest using one function for example

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=ico\ADlogo.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
; Includes the GuiConstants (required for GUI function usage)
#include <GuiConstants.au3>
#include <Excel.au3>
#include <Word.au3>

;~ Create Excel Application Object
Global $oExcel = _Excel_Open()
    If @error Then Exit

;~ GUI Creation
GuiCreate(" AD Quick Links", 400, 320)

;~ Button1 Control
$button1 = GUICtrlCreateButton("Open Projects & Proposals Register", 50, 20, 300, 30)
$button2 = GUICtrlCreateButton("Open Another Excel Spreadsheet", 50, 70, 300, 30)

GUISetState(@SW_SHOW)

While 1
    $iMSG = GuiGetMsg()
    Select
        Case $iMSG = $GUI_EVENT_CLOSE
            Exit ;~ Closes the GUI
        Case $iMSG = $button1
            _XLS_BOOKOPEN('R:\Project Register-New Project & Proposal Numbers.xls'); Opens Project Register-New Project & Proposal Numbers.xls
        Case $iMSG = $button2
            _XLS_BOOKOPEN('R:\FileName2.xls');~ Opens FileName2.xls
    EndSelect
WEnd

;~ USAGE: _XLS_BOOKOPEN('Excel Spreadsheet Name')
Func _XLS_BOOKOPEN($sXLS_BOOKOPEN)
    Local $oWorkBook = _Excel_BookOpen($oExcel, $sXLS_BOOKOPEN)
    ; Hides the GUI while the function is running
    ;GUISetState(@SW_HIDE)
    Exit
    ; ================
    ; The script you would like to perform for Button1 goes here
    ; ================
EndFunc

 

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