mcornbill Posted April 7, 2009 Share Posted April 7, 2009 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: expandcollapse popup#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 Link to comment Share on other sites More sharing options...
Skruge Posted April 7, 2009 Share Posted April 7, 2009 >"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(^ ERRORI 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] Link to comment Share on other sites More sharing options...
mcornbill Posted April 8, 2009 Author Share Posted April 8, 2009 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? Link to comment Share on other sites More sharing options...
Skruge Posted April 8, 2009 Share Posted April 8, 2009 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] Link to comment Share on other sites More sharing options...
mcornbill Posted April 8, 2009 Author Share Posted April 8, 2009 (edited) 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: expandcollapse popup#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 April 8, 2009 by mcornbill Link to comment Share on other sites More sharing options...
Skruge Posted April 8, 2009 Share Posted April 8, 2009 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] Link to comment Share on other sites More sharing options...
mcornbill Posted April 30, 2009 Author Share Posted April 30, 2009 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: expandcollapse popup#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 Link to comment Share on other sites More sharing options...
MrMitchell Posted May 14, 2009 Share Posted May 14, 2009 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now