themajestic08 Posted January 17, 2008 Share Posted January 17, 2008 Hi folks Does anyone have any sample programs where an excel spreadsheet is used as the datasource, copies values row by row and drops them into specified gui text boxes? I've completed what I want to do on web pages using a database connection - same idea just using different datasource and destination app. I've tried using some of the other scripts on the site but have had no joy. ;Connection string, tell me if I'm wrong: $DSN = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\yada\Test.xls;DefaultDir=C:\yada;"; ;Create a connection and recordset $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ($DSN) $adoRs = ObjCreate ("ADODB.Recordset") ;run the exe run(test.exe) Say the name of the text box in the gui is edit1, what command should I use to get a handle on this to put the first excel cell value into? Any help is appreciated Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 17, 2008 Share Posted January 17, 2008 (edited) Hi folks Does anyone have any sample programs where an excel spreadsheet is used as the datasource, copies values row by row and drops them into specified gui text boxes? I've completed what I want to do on web pages using a database connection - same idea just using different datasource and destination app. I've tried using some of the other scripts on the site but have had no joy. ;Connection string, tell me if I'm wrong: $DSN = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\yada\Test.xls;DefaultDir=C:\yada;"; ;Create a connection and recordset $adoCon = ObjCreate ("ADODB.Connection") $adoCon.Open ($DSN) $adoRs = ObjCreate ("ADODB.Recordset") ;run the exe run(test.exe) Say the name of the text box in the gui is edit1, what command should I use to get a handle on this to put the first excel cell value into? Any help is appreciated You should look into Locodarwin's ExcelCOM_UDF.au3 UDF. Your life will be a simpler place... P.S. That would look like (cell number changed to highlight example): #include <ExcelCOM_UDF.au3> $sFilePath = "C:\yada\Test.xls" $sWinTitle = "Your App's Window Title" $oExcel = _ExcelBookOpen($sFilePath) $Data = _ExcelReadCell($oExcel, 5, 8) ; Reads R5C8 or "H5" ControlSetText($sWinTitle, "", "Edit1", $Data) _ExcelBookClose($oExcel) Edited January 17, 2008 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
themajestic08 Posted January 18, 2008 Author Share Posted January 18, 2008 You should look into Locodarwin's ExcelCOM_UDF.au3 UDF. Your life will be a simpler place... P.S. That would look like (cell number changed to highlight example): #include <ExcelCOM_UDF.au3> $sFilePath = "C:\yada\Test.xls" $sWinTitle = "Your App's Window Title" $oExcel = _ExcelBookOpen($sFilePath) $Data = _ExcelReadCell($oExcel, 5, 8) ; Reads R5C8 or "H5" ControlSetText($sWinTitle, "", "Edit1", $Data) _ExcelBookClose($oExcel) Thanks mate What I was going to do was just declare a variable name for each one of the cells, then run a for loop matching each variable to the specific text/combo box etc I'll throw your code in and see how I go Cheers Link to comment Share on other sites More sharing options...
themajestic08 Posted January 18, 2008 Author Share Posted January 18, 2008 Here's my code, any ideas why I get "The requested action with this object has failed for this part $RecordSet.Open("POP", _ $Connection, _ $adOpenStatic, _ $adLockReadOnly) ;Code Global $Connection ;ADODB connection object Global $RecordSet ;ADODB recordset object Global $ExitFlag ;Error flag ;---- CursorTypeEnum Values ---- Const $adOpenForwardOnly = 0 Const $adOpenKeyset = 1 Const $adOpenDynamic = 2 Const $adOpenStatic = 3 ;---- LockTypeEnum Values ---- Const $adLockReadOnly = 1 Const $adLockPessimistic = 2 Const $adLockOptimistic = 3 Const $adLockBatchOptimistic = 4 $ExitFlag=0 ;Error flag ;Create ADODB connection: $Connection=ObjCreate("ADODB.Connection") ;Create ADODB Recordset $RecordSet=ObjCreate("ADODB.Recordset") ;Set ADODB error handler $DSN = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\POP.xls;DefaultDir=C:\;"; $Err=ObjEvent("AutoIt.Error","ODBCJET_ErroHandler") ;Open connection with ODBC system datasource: $Connection.Open($DSN) If $ExitFlag=1 then return 0 $RecordSet.Open("POP", _ $Connection, _ $adOpenStatic, _ $adLockReadOnly) $RecordSet.MoveFirst For $i = 0 to $Recordset.RecordCount step +1 $Last = $RecordSet.Fields("A").Value ;Just using the A column in the spreadsheet MsgBox(0, "DataGrid", $Last) ;assign values from recordset to variables to be used during page population process ;$JointApp = $RecordSet.Fields("JointApplication").Value $RecordSet.MoveNext Next $RecordSet.Close Link to comment Share on other sites More sharing options...
themajestic08 Posted January 18, 2008 Author Share Posted January 18, 2008 Sorry forgot to say that I did use #include <ExcelCOM_UDF.au3> and #include<GUIConstants.au3> Link to comment Share on other sites More sharing options...
Paulie Posted January 18, 2008 Share Posted January 18, 2008 (edited) Sorry forgot to say that I did use #include <ExcelCOM_UDF.au3>...Well, I imagine doing so would solve your problem. Why re-invent the wheel? Edited January 18, 2008 by Paulie Link to comment Share on other sites More sharing options...
themajestic08 Posted January 18, 2008 Author Share Posted January 18, 2008 Well, I imagine doing so would solve your problem. Why re-invent the wheel?Thanks for the constructive help.On another matter, anyone ever have problems trying to get a handle on objects in applications developed in .NET?When I roll the Window Info tool over the text box I want to set the value to it gives me a class name like this:WindowsForms10.EDIT.app.0.378734a7I tried putting that value in ControlSetText($sWinTitle, "", "WindowsForms10.EDIT.app.0.378734a7", "test") but it doesn't populate.I tried doing a test app that would enter a value in the Type Name or Select From List txt box on windows Address Bookrun("C:\Program Files\Outlook Express\wab.exe")$sWinTitle = "Address Book"ControlSetText($sWinTitle, "", "Edit1", "test")but it didn't work, any ideas Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 18, 2008 Share Posted January 18, 2008 You need to be testing the results of you ObjCreate() functions before going on, and there are glaring errors in this code. In addition, including functions you never call won't impart any magic to broken code, so ExcelCOM_UDF.au3 doesn't help if you don't use it: Please put your code in ScitTE and run Tidy (Ctrl-t) on it before posting, and put it in {code}{/code} or {autoit}{/autoit}tags (with square brackets vice curly braces): expandcollapse popup;---- CursorTypeEnum Values ---- Const $adOpenForwardOnly = 0 Const $adOpenKeyset = 1 Const $adOpenDynamic = 2 Const $adOpenStatic = 3 ;---- LockTypeEnum Values ---- Const $adLockReadOnly = 1 Const $adLockPessimistic = 2 Const $adLockOptimistic = 3 Const $adLockBatchOptimistic = 4 ; Globals Global $Connection ;ADODB connection object Global $RecordSet ;ADODB recordset object Global $ExitFlag ;Error flag Global $DSN = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\POP.xls;DefaultDir=C:\;" Global $ExitFlag = 0 ;Error flag ;Set ADODB error handler ; Where is function ODBCJET_ErroHandler (sic) declared? $Err = ObjEvent("AutoIt.Error", "ODBCJET_ErroHandler") ;Create ADODB connection: $Connection = ObjCreate("ADODB.Connection") If IsObj($Connection) Then ConsoleWrite("Debug: Created object $Connection = " & ObjName($Connection) & @LF) Else Exit 1 EndIf ;Create ADODB Recordset $RecordSet = ObjCreate("ADODB.Recordset") If IsObj($RecordSet) Then ConsoleWrite("Debug: Created object $RecordSet = " & ObjName($RecordSet) & @LF) Else Exit 2 EndIf ;Open connection with ODBC system datasource: $Connection.Open($DSN) ; In the line of code below: What sets $ExitFlag? Then Return from what? ;If $ExitFlag = 1 Then Return 0 ; Open record set $RecordSet.Open("POP", _ $Connection, _ $adOpenStatic, _ $adLockReadOnly) $RecordSet.MoveFirst For $i = 0 To $RecordSet.RecordCount $Last = $RecordSet.Fields("A" ).Value ;Just using the A column in the spreadsheet MsgBox(0, "DataGrid", $Last) ;assign values from recordset to variables to be used during page population process ;$JointApp = $RecordSet.Fields("JointApplication").Value $RecordSet.MoveNext Next $RecordSet.Close Func OnAutoItExit() If (@exitMethod = 1) And (@exitCode <> 0) Then Switch @exitCode Case 1 $sMsg = "Debug: Error, failed to create $Connection object." Case 2 $sMsg = "Debug: Error, failed to create $RecordSet object." EndSwitch MsgBox(16, "Fatal Error", $sMsg & " Exiting...") Exit EndIf EndFunc ;==>OnAutoItExit If you have an "ODBCJET_ErroHandler" function, does it show any errors? Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
themajestic08 Posted January 18, 2008 Author Share Posted January 18, 2008 Honestly mate, I'm a total newbie and was working off a script someone else had given a half a**ed attempt at so I'm just trying to muddle through. I don't think I'll be able to use autoIT due to the trouble it has getting a handle on objects in .NET developed applications If the only way is by sending keystrokes and mouse clicks etc it kinda defeats the purpose Link to comment Share on other sites More sharing options...
ptrex Posted January 19, 2008 Share Posted January 19, 2008 @allmaybe this can get all going.Reading Excel using SQLRegarsds,ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New Link to comment Share on other sites More sharing options...
seandisanti Posted January 30, 2008 Share Posted January 30, 2008 (edited) Honestly mate, I'm a total newbie and was working off a script someone else had given a half a**ed attempt at so I'm just trying to muddle through. I don't think I'll be able to use autoIT due to the trouble it has getting a handle on objects in .NET developed applications If the only way is by sending keystrokes and mouse clicks etc it kinda defeats the purposeWhat application are you trying to interact with? if you can post a link to it, someone may be able to help you manage your controls. Also for excel, I typically just use the excel objects visible through COM. Example, for your initial request of populating a combo box based on a spreadsheet: $myex = objCreate("Excel.Application","");creates new excel object that is not visible to the user $mywb = $myex.workbooks.open("c:\book.xls",False,True); opens the specified workbook as read only $myws = $mywb.ActiveSheet;creates a reference to the active sheet in that book $x = 2; defines an iterator to step through the list $tmp = '';creates an empty string we're going to use to populate the combo box after filling it up While $myws.range("a" & $x).formula <> "";declares an empty cell in the A column as our exit condition $tmp = $tmp & "|" & $myws.range("a" & $x).formula & "|";adds the value in the current row's 'A' cell to our string $x ++;increments the iterator Wend;repeats loop unless exit condition is met $tmp = StringLeft(StringRight($tmp,StringLen($tmp)-1),stringlen($tmp)-2);chops the leading and trailing pipes from our string $myex.quit;this closes the hidden excel application we created in line 1 VERY IMPORTANT ;at this point you'd have a good string that you can use with GUICTRLSetData() to populate your combo box I've never really been one for re-inventing the wheel myself, that's why i use the functions (as in this example) visible within the vba object browser to control office applications instead of udf's. From excel, you can press alt+f11 to access the vba editor, then press F2 to see the object browser which will list each object along with it's types, and all of it's methods and members. I have done a LOT of MS Office automation through COM and just as much work writing for officel through VBA when an external solution was not allowed, so if you have any questions about office objects or automating office please feel free to send them along via PM or IM (my IM information is in my user info) **P.S.** don't give up so easily, nothing worth having is very easily attained -including programing experience; but a lot of people have made this one of the easiest and most powerful languages to pick up and if you walk away without giving it a good effort you're going to be missing out Edited January 30, 2008 by cameronsdad 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