If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Alternative to Sendkeys in Excel Macro/VBA

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On