SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Senior Member
    Join Date
    Mar 2004
    Location
    UK
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Automating VB Controls within a Excel document

    Hi,

    I would like to know the feasibility of automating VB controls such as Buttons, listbox,checkbox etc embedded within an excel document (programmed as macros). Is this possible? If so, what are the basic configurations I had to do...

    I already tried spying the controls after enabling the ActiveX and Visual Basic options but QTP recognizes them as WinObjects only...I also tried mapping the vb controls to windows standard controls...still it never worked...

    Can anyone help me in this?

  2. #2
    Senior Member
    Join Date
    May 2004
    Location
    Lakeland, FL
    Posts
    299
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    this might be possible through Excel automation ... it should also be possible via windows api (although much more difficult). If everything is mapped through a macro, you could just automate the macro executions .. Example

    <font class="small">Code:</font><hr /><pre>
    excApplication.Run "Book1!Macro1", "Test"

    Public Sub Macro1(TestString As String)

    MsgBox TestString

    End Sub
    </pre><hr />
    JFry

  3. #3
    Junior Member
    Join Date
    Feb 2002
    Location
    Pune,Maharashtra,India
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    Agree with JeremyDFry...

    However you could try something like this:

    dim Excelapp
    dim WkBook
    Set WkBook = app.workbooks.open("WorkbookUnderTest")

    I understand you could access every control .. from WkBook

    I have never tried it .. it should work.

  4. #4
    Senior Member
    Join Date
    Mar 2004
    Location
    UK
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    No..It actually doesnt work when trying to directly interact with the controls in the excel doc...i know the ways of calling the macros but the prob is the developer who created this document is no more with the company...people here know only to use the document and dont have any idea of which macro to call for what...as there are 100's of macors assigned...

    I actually want a solution to directly access the controls like listbox and buttons within the excel doc...if anyone has an concrete solution to this pls let me know..

  5. #5
    Member
    Join Date
    Dec 2007
    Location
    UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    I know this is an old thread, but does anyone know if there is a way of doing this? I have a spreadsheet full of OLE text boxes, buttons, etc and I want to automate the use of these, but I can't find an easy way of doing so.

    Each of these controls has a set of associated subroutines - is there any way perhaps to invoke these dynamically? E.g. "CommandButton1_Click"

    Thanks

  6. #6
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    You need something like below to make it work

    xlsApp.Run(xlsSheet.Shapes("Button 1").OnAction)

  7. #7
    Member
    Join Date
    Dec 2007
    Location
    UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    Thanks Tarun - if I understand correctly, the "onAction" property sets the name of the macro to run when the object is clicked. However there is no "macro" as such associated with the button and therefore onaction value is blank. In this example, you would expect the value to be "CommandButton1_Click" but Excel doesn't treat this as a macro unfortunately.

  8. #8
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    Why would that happen? I tried in a new excel sheet on my PC and everything is working fine

  9. #9
    Member
    Join Date
    Dec 2007
    Location
    UK
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    I'm not having much joy with this I'm afraid.

    I have tried the following code, in a QTP script. The spreadsheet in question simply contains one embedded command button, CommandButton1. When I right-click CommandButton1 (in design mode) and select "View Code", it jumps to the in-built function CommandButton1_Click. I would therefore expect "CommandButton1_Click" to be returned in the code below:

    SpreadsheetName = "c:\Book1.xls"
    Set objWorkBook = GetObject(SpreadsheetName)
    Set objSheet = objWorkBook.Sheets(1)
    x = objSheet.Shapes(1).Name
    y = objSheet.Shapes(1).onAction
    objWorkBook.Application.Run(objSheet.Shapes(1).OnA ction)

    When I step through the above code, x returns the value "CommandButton1", however y returns "". Tarun, are you saying if you were to do this you get the value "CommandButton1_Click" in the y variable?

    I am using Excel 2003 with QTP10.

    Thanks for your help.

  10. #10
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Automating VB Controls within a Excel document

    Can you attach the excel file you used?

 

 
Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.00%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 10:08 PM.

Copyright BetaSoft Inc.