Jump to content

Excel Q's


Recommended Posts

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

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...

:D

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 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

You should look into Locodarwin's ExcelCOM_UDF.au3 UDF. Your life will be a simpler place...

:D

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

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

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.378734a7

I 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 Book

run("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

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):

;---- 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?

:D

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

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

  • 2 weeks later...

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

What 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 by cameronsdad
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...