sendkeys, separate instance, view source

[craig.penny]

Hello all,

I'm having problems trying to automate a process of downloading workbooks. What I do manually is that I open a webpage then click a button on the page which directs me to another page where I click another button (this happens a couple more times) and finally get the window that asks if I want to open, save, or cancel the Excel download and everything works fine. When I try to automate the process using sendkeys "{TAB}" and sendkeys "~" the workbook always open as a separate instance of Excel and I can't get the code (from the original) to recognize the newly opened workbook.

My questions are:

How can I force a workbook to open in the same instance as an already open workbook?

or

How can I refer to the new instance (I need to select a particular sheet in the new workbook in the new instance)?

or

What am I doing wrong with sendkeys?

or

I know that sendkeys is poor programming practice but I don't know how to use the information that comes up when I right click and select "view source". Is someone patient enough to explain it to me?


Thanks a ton for even reading this much. I'd be more than happy if someone could just point me in the right direction. I've been searching the internet for two days with no luck (although I have found some neat code...).

 

[craig.penny]

I'm completely open to wild guesses and suggestions to look somewhere else.

Anyone?

Thanks!

 

[craig.penny]

Hey MVPs,

Please tell me if I'm not asking my questions in the proper format or what I need to do to make my posts more understandable. I must be asking stupid questions because I haven't had a response to my last several attempts.

I wouldn't have posted this here but I don't know where else to do it.

This is in no way intended as snarky, it is a sincere request.

 

[Norie]

Craig

I think we need more information and clarification.

You mentioned downloading but also multiple instances of Excel.

Actually when I think about it you might have no way to download to an existing instance of Excel, really depends what sort of download you are talking about.

If it's a 'straight' file download it might be possible to use code to download the file without all the sendkeys stuff, or even accessing the actual page.

You'd need the specific URL for the file for that though.

If the download is being done is some other way, eg generated with code on the webpage via an http request you might not be able to do much.

Only way to find out is with more information.

 

[craig.penny]

Thanks Norie, I appreciate the response. I also am the first to admit that I'm no expert and the way I do things with VBA is probably not the best way to go about it so suggestions for complete overhauls is not unexpected.

I never quite know how much info to throw out there or how much code to show but here goes...

This is what I have at the top of the module:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal Y As Long) As Long
Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, _
                  ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
    Private Const MOUSEEVENTF_LEFTDOWN = &H2
    Private Const MOUSEEVENTF_LEFTUP = &H4
    Private Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
    Private Const MOUSEEVENTF_RIGHTUP As Long = &H10

This is what I'm trying to use to navigate to the right page:

Sub QuickSix(Optional CallerID As String)
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim s4 As String
Dim s5 As String
Dim s6 As String
Dim s7 As String
Dim s8 As String
Dim sURL As String
Dim Sleeper As Integer
Sleeper = 2
Static iE As Object
Dim i As Integer
If CallerID = "Button6" Or CallerID = "Button11" Then
  s1 = "[URL]https://sorry[/URL], this part is proprietary"
  s2 = CStr(Cells(Selection.Row, 7))
  s3 = "&CCNUM="
  s4 = CStr(Cells(Selection.Row, 8))
  s5 = "&CID=LPEXT&DOCST=A&UIC="
  s6 = CStr(Cells(Selection.Row, 3))
  s7 = "&EDATE="
  s8 = CStr(Format(Cells(Selection.Row, 3), "m/d/yyyy"))
  
  sURL = s1 & s2 & s3 & s4 & s5 & s6 & s7 & s8
  
  Set iE = CreateObject("InternetExplorer.Application")
  
  iE.Navigate sURL
  iE.Visible = True
  Sleep 4000 * Sleeper
  For i = 1 To 16
      Sleep 250 * Sleeper
      Application.SendKeys "{TAB}", True
  Next i

  Sleep 250 * Sleeper
  If CallerID = "Button11" Then
    Application.SendKeys "{TAB}", True
    Sleep 250 * Sleeper
  End If
  Application.SendKeys "~", True

  Sleep 4000
  For i = 1 To 25
      Sleep 250 * Sleeper
      Application.SendKeys "{TAB}", True
  Next i
  Sleep 250 * Sleeper
  Application.SendKeys "~", True
  For i = 1 To 5
      Sleep 250 * Sleeper
      Application.SendKeys "{TAB}", True
  Next i
  Sleep 250 * Sleeper
  Application.SendKeys "~", True
  Sleep 4000 * Sleeper
  For i = 1 To 2
      Sleep 250 * Sleeper
      Application.SendKeys "{TAB}", True
  Next i
  Sleep 250 * Sleeper
  Application.SendKeys "~", True
  Sleep 4000 * Sleeper
'  For i = 1 To 2
'      Sleep 250
'      Application.SendKeys "{TAB}", True
'  Next i
  Sleep 250
  Application.SendKeys "~", True
  Sleep 4000 * Sleeper
  For i = 1 To 2
      Sleep 250 * Sleeper
      Application.SendKeys "{TAB}", True
  Next i
  Sleep 250 * Sleeper
  Application.SendKeys "~", True
  Sleep 4000 * Sleeper
  For i = 1 To 2
      Sleep 250 * Sleeper
      Application.SendKeys "{TAB}", True
  Next i
  Sleep 250 * Sleeper
  Application.SendKeys "~", True
  'Call QuickMenuForm.QuickComEight_Click
  
End If
If CallerID = "Button5" Then
  iE.Quit
  Set iE = Nothing
End If
Sleep 1000 * Sleeper
'SetCursorPos 1000, 300
'
'mouse_event MOUSEEVENTF_LEFTDOWN, 0&, 0&, 0&, 0&
'mouse_event MOUSEEVENTF_LEFTUP, 0&, 0&, 0&, 0&

End Sub

The thing is, this actually works and gets the exact file I need but for some reason it opens as a separate instance. I found this code:

Sub IsExcelRunning()
    strComputer = "."
    
    Set objWMI = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    
    Set proc = objWMI.ExecQuery _
    ("Select * from Win32_Process Where Name = 'Excel.exe'")
    
    If proc.Count = 0 Then
        MsgBox "Excel.exe is not running."
    Else
        MsgBox proc.Count & " instances of Excel.exe is running."
    End If
    
End Sub

Which I had hoped to use to find a way to reference the new instance but I don't know how to modify it to give me a name. Works great for counting the instances though

The other thing is that when I use just the first part of this to navigate to the page and then manually click buttons to open the new Excel file, it comes up in the same instance as I already have open and I have no problem copying the new file... but I really need to automate this because I have ~few thousands files to open, copy, paste, and close.

I know using sendkeys is a last resort but I don't know how else to do it. The parts where I'm sending "~" (ENTER) is when I'm selecting a button that opens a new page. The problem there is that after the first URL, none of the pages have a "https://...." that I can see. Does it have something to do with the secure nature of the data? It's government stuff. Or is it that I just don't know where to look?

At the bottom of the second chunk of code you can see I was trying to send down and up left mouse clicks in the hopes that it would duplicate what I did manually but I had no luck with that either.

Anyways, thanks a ton for reading all this!