Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    2

    Alternative to Sendkeys in Excel Macro/VBA

    Hi all! I've tried avoiding using sendkeys in any macro, however my recent project needs my macro to "press enter" once a certain command button in a web-tool is activated at a certain pre-determined time. Is there another way of doing this without using sendkeys? The macro i wrote works fine when the timedelay is short. However, when i schedule the timedelay to "press enter" after a few hours the sendkeys command fails.

    Here is the sample of my code:
    *note: Cell "C10" in sheet "Setup" is where the user enters the desired time for the backup

    Sub AutomateBackUp()
    Dim alertTime
    'Check scheduled time
    x = (Now - Range("Setup!C10")) * -1
    If x < 0 Then
    Msg = "The scheduled time/date entered has already expired" & vbCr & "Do you want to run backup now?"
    Style = vbYesNoCancel
    Title = "Warning"
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then Call login Else Exit Sub
    End If

    Msg = "Schedule backup at the indicated time??"
    Style = vbYesNoCancel
    Title = "Run Command"
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
    alertTime = Range("Setup!C10")
    Application.OnTime alertTime, "backup"
    End If

    End Sub

    This part works fine. On to the backup sub:
    *note:Cell "C28" in sheet "Setup" contains the link to the webtool


    Sub backup()
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer
    Excel.Application.WindowState = xlMaximized
    Sheet1.Activate
    ie.Navigate Range("Setup!C28").Value

    'makes sure that the page is fully loaded before proceeding
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop

    ie.Visible = True

    '***this is where the macro fails when scheduled to run in a few hours time
    'tab through the other command buttons until the target command button is highlighted.

    SendKeys "{TAB}", True
    SendKeys "{TAB}", True
    SendKeys "{TAB}", True
    SendKeys "{TAB}", True 'target command button at this point

    SendKeys "{ENTER}", True 'press enter


    End Sub

    *again, the macro works fine when i set the schedule after a few minutes. However, it fails to cycle through the tab or fails to "press enter" when i schedule it to run after a few hours.

    Any help on this matter will be greatly appreciated. Thanks!

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    You are using the InternetExplorer object direct access. This gives you access to the IE Object Model. So you can directly call objects in a web document. There isn't great documentation on how to do this so it takes trial and error to get the syntax correct.

    Make sure you have the reference to "Microsoft Internet Controls" defined in your VB project.

    You can view the Object reference using the Object Browser {F2}. View Class WebBrowser
    Member of SHDocVw
    WebBrowser Control

    To get the object names in your web document use "View Source" then get the class names for the Enter button and any other controls you want to activate from your code.

    This works with Internet Explorer. Your also using IE so it should work.

    Notice in this example it has a Frame 'compliance1'. If you do not have to contend with frames you would leave the frame part out. You'll have to "View Source" to get your control names, Form Name, Doc name, and doc structure of your html document to call the objects in your code. The controls I'm interacting with in this code are the textbox ".search_textbox" and button ".search_button" to enter a value and click the button.

    Code:
    Sub SearchCompliance()
      Dim shWin As New SHDocVw.ShellWindows
      Dim IE As SHDocVw.InternetExplorer
      Dim codeArray As Variant
      
      For Each IE In shWin
        Set Doc = IE.Document
        If TypeOf Doc Is HTMLDocument Then
            DocTitle = Doc.Title
            If DocTitle = "Compliance" Then
                bCompliance = True
                Exit For
            End If
        End If
      Next
      If Not bCompliance Then
        MsgBox "Please activate the Compliance Webpage you wish to Search."
        Exit Sub
      End If
      
    strSearch = Cells(5, 10)
     
      ' Activate the IE Browser Window
      IE.Visible = True
           
      ' Send code string then Click the Search Button
      With IE.Document.frames("compliance1").Document.form1
        .search_textbox.Value = strSearch
        .search_button.Click
      End With
    End Sub
    ~

    Bill

  3. #3
    Join Date
    May 2008
    Posts
    2

    Smile

    Thanks for the advice savbill! I will try this out. It may take some trial and error to get it going but I can definitely see how this method is much more stable than using sendkeys. I will post updates once i get it done

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •