Jump to content
Sign in to follow this  
mcornbill

I think it's a problem with my $oExcel variable...?

Recommended Posts

mcornbill

Hi Guys

Have successfully been using the Excel functionality to read data from a spreadsheet and then copy into another application. However now that I've created a GUI for it and started putting it into functions I'm getting a compile error, can anybody show me where I'm going wrong? Here's the code:

#Include <Excel.au3>
#include <GUIConstantsEx.au3>

Opt("WinTitleMatchMode", 2)
Opt("GUIOnEventMode", 1) ; Change to OnEvent mode

Dim $count, $count1, $count2, $count3, $res1, $res2, $code, $disccode, $disccode2
$message = "Please select the purchase discount percentages you wish to import."

GuiCreate("Purchase Discount Code Import", 292, 300)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked") ; If 'X' clicked then close program

GUICtrlCreateLabel ("What is your screen resolution?", 30, 20)
GUICtrlCreateLabel ("Please select the purchase discount table", 30, 90)
GUICtrlCreateLabel ("you wish to import.", 30, 105)
GUICtrlCreateLabel ("How many discount codes (rows) are in", 30, 185)
GUICtrlCreateLabel ("the spreadsheet?", 30, 200)
GUICtrlCreateLabel ("© 2009 Mark Cornbill", 5, 286)
GUICtrlCreateLabel ("Version 1.0", 235, 286)

$res1 = GUICtrlCreateRadio("1280x768", 150, 40, 80, 20)
GUICtrlSetData($res1,"1280x768")
$res2 = GUICtrlCreateRadio("1024x768", 30, 40, 80, 20)
GUICtrlSetData($res2,"1024x768")
$count3 = GuiCtrlCreateInput("", 30, 220, 80, 20)
GUICtrlSetData($count3,"") ; Stores the row number entered
$Browse = GuiCtrlCreateButton("Browse", 30, 125, 80, 30)
GUICtrlSetOnEvent($Browse, "browse_file") ; When clicking browse then run the browse for a file function
$GO = GuiCtrlCreateButton("GO", 140, 215, 79, 30)
GUICtrlSetOnEvent($GO, "go") ; When clicking GO then run the import procedure

GUISetState(@SW_SHOW)

While 1
  Sleep(1000)  ; Idle GUI
WEnd

Func browse_file() ; Loads up a file browser dialogue box 
    $FilePath = FileOpenDialog($message, "\", "All Excel Files (*.xls;*.xlsx)", 1 )
    
    If @error Then
        MsgBox(4096,"","No file chosen")
        Exit
    Else
        $FilePath = StringReplace($FilePath, "|", @CRLF)
    EndIf

    $oExcel = _ExcelBookOpen($FilePath)
EndFunc

Func go($count3, $code) ; Run the import process
    $count = 2
    $count2 = 2
    
    For $count1 = $count2 to $count3            
        WinActivate("Pinnacle by Pinewood", "")
        WinWait("Pinnacle by Pinewood", "")
        Send("!C")
    Sleep(500)
    $code = _ExcelReadCell($oExcel, $count1, 1)
    ControlSend("Pinnacle by Pinewood", "", "", $code)
    Send("!S")
    Sleep(1500)
Next

Sleep(1000)
Send("!A")
Sleep(1000)
Send("{TAB 11}")

For $count1 = $count2 to $count3
            $disccode = _ExcelReadCell($oExcel, $count1, 2)
            $disccode2 = _ExcelReadCell($oExcel, $count1, 3)
            ControlSend("Pinnacle by Pinewood", "", "", $disccode)
            Send("{TAB}")
            ControlSend("Pinnacle by Pinewood", "", "", $disccode2)
            Send("{TAB 3}")
            If $count = $count3 Then
                Send("!S")
                ExitLoop
            ElseIf $count = 28 or $count = 55 or $count = 82  or $count = 109  or $count = 136  or $count = 163  or $count = 190  or $count = 217 or $count = 244  or $count = 271  or $count = 298  or $count = 325  or $count = 352  Then
                Send("!S")
                Sleep(1000)
                MouseClick("left", 980, 680)
                Sleep(1000)
                Send("!A")
                Sleep(1000)
                Send("{TAB 11}")
            EndIf
            $count = $count + 1
Next    

MsgBox(4096,"",$count-1 & " discount codes and percentages inserted.")  
EndFunc

Func CLOSEClicked() ; Close the program
  Exit
EndFunc

And it's failing here:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Docs\Purchase Discounts.au3"  
C:\Docs\Purchase Discounts.au3 (60) : ==> Variable used without being declared.:
$code = _ExcelReadCell($oExcel, $count1, 1)
$code = _ExcelReadCell(^ ERROR
>Exit code: 1   Time: 21.721

I can't see that I haven't declared the variable "$code" so is it something to do with the "$oExcel" command.

P.S. I've never been too sure if I have to pass the variables I have done in the func go line, is that correct?

Thanks in advance for any help.

Mark

Share this post


Link to post
Share on other sites
Skruge

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Docs\Purchase Discounts.au3"

C:\Docs\Purchase Discounts.au3 (60) : ==> Variable used without being declared.:

$code = _ExcelReadCell($oExcel, $count1, 1)

$code = _ExcelReadCell(^ ERROR

I can't see that I haven't declared the variable "$code" so is it something to do with the "$oExcel" command.

Take a look at where the error is shown.

The carat is pointing at $oExcel, so that's where you're being stopped.


[font="Tahoma"]"Tougher than the toughies and smarter than the smarties"[/font]

Share this post


Link to post
Share on other sites
mcornbill

Hi Skruge

I realise that it's the $oExcel part that's causing the issue, however I'm none the wiser with how to fix it!

If it put all that code out of a function call then it works?

Share this post


Link to post
Share on other sites
Skruge

Declaring variables is highly recommended, and Opt("MustDeclareVars", 1) help by enforcing that.

You never declared $oExcel, so when it's used in browse_file(), it is declared in the local scope and is unavailable to the rest of your code.

You should add "Global $oExcel" near the top.

Also, I would start with the Go button disabled and enable it after selecting a file.


[font="Tahoma"]"Tougher than the toughies and smarter than the smarties"[/font]

Share this post


Link to post
Share on other sites
mcornbill

Declaring variables is highly recommended, and Opt("MustDeclareVars", 1) help by enforcing that.

You never declared $oExcel, so when it's used in browse_file(), it is declared in the local scope and is unavailable to the rest of your code.

You should add "Global $oExcel" near the top.

Also, I would start with the Go button disabled and enable it after selecting a file.

Hi Skruge

Thanks for that. By declaring every variable globally the program now runs. Now a new problem has arose by the fact that it's looping 11 times regardless of whatever figure the user enters as the amount of rows ($count3). New code is as follows:

#Include <Excel.au3>
#include <GUIConstantsEx.au3>

Opt("WinTitleMatchMode", 2)
Opt("GUIOnEventMode", 1); Change to OnEvent mode
Opt("MustDeclareVars", 1)

Dim $count, $count1, $count2, $count3, $res1, $res2, $code, $disccode, $disccode2, $message, $Browse, $GO, $FilePath, $oExcel
$message = "Please select the purchase discount percentages you wish to import."

GuiCreate("Purchase Discount Code Import", 292, 300)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked"); If 'X' clicked then close program

GUICtrlCreateLabel ("What is your screen resolution?", 30, 20)
GUICtrlCreateLabel ("Please select the purchase discount table", 30, 90)
GUICtrlCreateLabel ("you wish to import.", 30, 105)
GUICtrlCreateLabel ("How many discount codes (rows) are in", 30, 185)
GUICtrlCreateLabel ("the spreadsheet?", 30, 200)
GUICtrlCreateLabel ("© 2009 Mark Cornbill", 5, 286)
GUICtrlCreateLabel ("Version 1.0", 235, 286)

$res1 = GUICtrlCreateRadio("1280x1024", 150, 40, 80, 20)
GUICtrlSetData($res1,"1280x1024")
$res2 = GUICtrlCreateRadio("1024x768", 30, 40, 80, 20)
GUICtrlSetData($res2,"1024x768")
$count3 = GuiCtrlCreateInput("", 30, 220, 80, 20)
GUICtrlSetData($count3,""); Stores the row number entered
$Browse = GuiCtrlCreateButton("Browse", 30, 125, 80, 30)
GUICtrlSetOnEvent($Browse, "browse_file"); When clicking browse then run the browse for a file function
$GO = GuiCtrlCreateButton("GO", 140, 215, 79, 30)
GUICtrlSetOnEvent($GO, "go"); When clicking GO then run the import procedure

GUISetState(@SW_SHOW)

While 1
  Sleep(1000) ; Idle GUI
WEnd

Func browse_file(); Loads up a file browser dialogue box 
    $FilePath = FileOpenDialog($message, "\", "All Excel Files (*.xls;*.xlsx)", 1 )
    
    If @error Then
        MsgBox(4096,"","No file chosen")
        Exit
    Else
        $FilePath = StringReplace($FilePath, "|", @CRLF)
    EndIf

    $oExcel = _ExcelBookOpen($FilePath)
EndFunc

Func go($count3); Run the import process
    
    $count = 2
    $count2 = 2
            
    For $count1 = $count2 to $count3            
        WinActivate("Pinnacle by Pinewood", "")
        WinWait("Pinnacle by Pinewood", "")
        Send("!C")
    Sleep(500)
    $code = _ExcelReadCell($oExcel, $count1, 1)
    ControlSend("Pinnacle by Pinewood", "", "", $code)
    Send("!S")
    Sleep(1500)
Next

Sleep(1000)
Send("!A")
Sleep(1000)
Send("{TAB 11}")

For $count1 = $count2 to $count3
            $disccode = _ExcelReadCell($oExcel, $count1, 2)
            $disccode2 = _ExcelReadCell($oExcel, $count1, 3)
            ControlSend("Pinnacle by Pinewood", "", "", $disccode)
            Send("{TAB}")
            ControlSend("Pinnacle by Pinewood", "", "", $disccode2)
            Send("{TAB 3}")
            If $count = $count3 Then
                Send("!S")
                ExitLoop
            ElseIf $count = 28 or $count = 55 or $count = 82  or $count = 109  or $count = 136  or $count = 163  or $count = 190  or $count = 217 or $count = 244  or $count = 271  or $count = 298  or $count = 325  or $count = 352  Then
                Send("!S")
                Sleep(1000)
                MouseClick("left", 980, 680)
                Sleep(1000)
                Send("!A")
                Sleep(1000)
                Send("{TAB 11}")
            EndIf
            $count = $count + 1
Next    

MsgBox(4096,"",$count-1 & " discount codes and percentages inserted.")  
EndFunc

Func CLOSEClicked(); Close the program
  Exit
EndFunc

I entered "11" as the last row number for $count3 which means it should loop through from rows 2 to 11 in the Excel spreadsheet, which is 10 codes. It's looping through to the 12th row for some reason regardless of what number I enter for count3 now?!

Oooh it's driving me mad now!

Edited by mcornbill

Share this post


Link to post
Share on other sites
Skruge

I entered "11" as the last row number for $count3 which means it should loop through from rows 2 to 11 in the Excel spreadsheet, which is 10 codes. It's looping through to the 12th row for some reason regardless of what number I enter for count3 now?!

Oooh it's driving me mad now!

No worries as long as you're learning and progressing...

Since Go() is called in event mode, you can't pass any parameters to it.

Also, $count3 is the control ID, not the contents... Use GuiCtrlRead($count3) to get the value you need.


[font="Tahoma"]"Tougher than the toughies and smarter than the smarties"[/font]

Share this post


Link to post
Share on other sites
mcornbill

Hi Guys

Been on holiday for a few weeks so am a bit rusty on where we got to on this. I no longer have a problem with the script looping 12 times everytime, now I have a problem with the excel read cell command not actually reading any values.

In the GO function it reads the cell contents from the previously opened spreadsheet, and then should enter it into another box in another application (Pinnacle). This was working fine before I went on holiday and the problem was just that it was only looping 12 times. Now it loops but it is not inserting any data from the spreadsheet.

I had a fully working simplified script of this without a fancy GUI which used to work fine, this now doesn't seem to read any values from the spreadsheet? Have there been any changes with the whole Excel suite of tools that could have caused this? New code is as follows:

#Include <Excel.au3>
#include <GUIConstantsEx.au3>

Opt("WinTitleMatchMode", 2)
Opt("GUIOnEventMode", 1) ; Change to OnEvent mode
Opt("MustDeclareVars", 1)

Dim $count, $count1, $count2, $count3, $res1, $res2, $code, $disccode, $disccode2, $message, $Browse, $GO, $FilePath, $oExcel
$message = "Please select the purchase discount percentages you wish to import."

GuiCreate("Purchase Discount Code Import", 292, 300)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked") ; If 'X' clicked then close program

GUICtrlCreateLabel ("What is your screen resolution?", 30, 20)
GUICtrlCreateLabel ("Please select the purchase discount table", 30, 90)
GUICtrlCreateLabel ("you wish to import.", 30, 105)
GUICtrlCreateLabel ("How many discount codes (rows) are in", 30, 185)
GUICtrlCreateLabel ("the spreadsheet?", 30, 200)
GUICtrlCreateLabel ("© 2009 Mark Cornbill", 5, 286)
GUICtrlCreateLabel ("Version 1.0", 235, 286)

$res1 = GUICtrlCreateRadio("1280x1024", 150, 40, 80, 20)
GUICtrlSetData($res1,"1280x1024")
$res2 = GUICtrlCreateRadio("1024x768", 30, 40, 80, 20)
GUICtrlSetData($res2,"1024x768")
$count3 = GuiCtrlCreateInput("", 30, 220, 80, 20)
GUICtrlRead($count3) ; Stores the row number entered
$Browse = GuiCtrlCreateButton("Browse", 30, 125, 80, 30)
GUICtrlSetOnEvent($Browse, "browse_file") ; When clicking browse then run the browse for a file function
$GO = GuiCtrlCreateButton("GO", 140, 215, 79, 30)
GUICtrlSetOnEvent($GO, "go") ; When clicking GO then run the import procedure

GUISetState(@SW_SHOW)

While 1
  Sleep(1000)  ; Idle GUI
WEnd

Func browse_file() ; Loads up a file browser dialogue box 
    $FilePath = FileOpenDialog($message, "\", "All Excel Files (*.xls;*.xlsx)", 1 )
    
    If @error Then
        MsgBox(4096,"","No file chosen")
        Exit
    Else
        $FilePath = StringReplace($FilePath, "|", @CRLF)
    EndIf

    $oExcel = _ExcelBookOpen($FilePath)
EndFunc

Func go() ; Run the import process
    
    $count = 2
    $count2 = 2
            
    For $count1 = $count2 to $count3        
        WinActivate("Pinnacle by Pinewood", "")
        WinWait("Pinnacle by Pinewood", "")
        Send("!C")
        Sleep(500)
        $code = _ExcelReadCell($oExcel, $count1, 1)
        ControlSend("Pinnacle by Pinewood", "", "", $code)
        Send("!S")
        Sleep(1500)
Next

Sleep(1000)
Send("!A")
Sleep(1000)
Send("{TAB 11}")

For $count1 = $count2 to $count3
            $disccode = _ExcelReadCell($oExcel, $count1, 2)
            $disccode2 = _ExcelReadCell($oExcel, $count1, 3)
            ControlSend("Pinnacle by Pinewood", "", "", $disccode)
            Send("{TAB}")
            ControlSend("Pinnacle by Pinewood", "", "", $disccode2)
            Send("{TAB 3}")
            If $count = $count3 Then
                Send("!S")
                ExitLoop
            ElseIf $count = 28 or $count = 55 or $count = 82  or $count = 109  or $count = 136  or $count = 163  or $count = 190  or $count = 217 or $count = 244  or $count = 271  or $count = 298  or $count = 325  or $count = 352  Then
                Send("!S")
                Sleep(1000)
                MouseClick("left", 980, 680)
                Sleep(1000)
                Send("!A")
                Sleep(1000)
                Send("{TAB 11}")
            EndIf
            $count = $count + 1
Next    

MsgBox(4096,"",$count-1 & " discount codes and percentages inserted.")  
EndFunc

Func CLOSEClicked() ; Close the program
  Exit
EndFunc

Share this post


Link to post
Share on other sites
MrMitchell

Are you still having trouble?

In the following:

Func go() ; Run the import process
    
    $count = 2
    $count2 = 2
            
    For $count1 = $count2 to $count3        
        WinActivate("Pinnacle by Pinewood", "")
        WinWait("Pinnacle by Pinewood", "")
        Send("!C")
        Sleep(500)
        $code = _ExcelReadCell($oExcel, $count1, 1)
        ControlSend("Pinnacle by Pinewood", "", "", $code)
        Send("!S")
        Sleep(1500)
Next

I wonder if your For loop does anything because I don't see where $count3 was defined, only declared. The only place you have _ExcelReadCell() is in those two For loops that count from $count2 to $count3.

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
Sign in to follow this  

×