Results 1 to 5 of 5

Thread: Open a webpage in Chrome and download files and save them on a share drive folder

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Open a webpage in Chrome and download files and save them on a share drive folder

    I have a Webpage (which is built using apache aurora, more of an web based application), on this webpage the user has to navigate to a reporting tab on left hand side navigation panel and then he has to click on the hyperlinks which are named as "Download" and when the user clicks on the hyperlink the save as box appears to save the .csv, .xlsx or .pdf files and there are multiple such download hyperlinks on the page. I am trying to build a macro which will open the chrome navigate to the URL and then search for Download Text and click and save each link file into specified share drive path. below is what i have got from my search but this is for Internet explorer and given that my webpage doesn't work with the internet explorer, I need this to work with Google Chrome.

    Code:
    Sub downloadfiles()
    Dim URL As String
    Dim ieApp As Object
    Dim ieDoc As Object
    Dim ieForm As Object
    Dim ieObj As Object
    Dim objColl As Collection
    
    URL = "http://www.myportal.ts"
    
    Set ieApp = CreateObject("InternetExplorer.Application")
    ieApp.Visible = True
    ieApp.Navigate URL
    
    While ieApp.Busy
        'wait...
    Wend
    
    Set ieDoc = ieApp.Document
    For Each ele In ieApp.Document.getElementsByTagname("download")
    
        If ele.innerHTML = "CSV" Then
            DoEvents
            ele.Click
        
        End If
    Next
    
    ieApp.Quit
    End Sub

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Open a webpage in Chrome and download files and save them on a share drive folder

    Can someone help with the above code.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Open a webpage in Chrome and download files and save them on a share drive folder

    afaik the only way to automate chrome is to use selenium wrapper, which should work with any browser, but i have never trued to used it myself
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2009
    Posts
    295

    Re: Open a webpage in Chrome and download files and save them on a share drive folder

    Ok. but since selenium wrapper is not available in windows by default, i won't be able to get it on my office machine. Can we use something like send keys first to find the text on screen then get its screen coordinates and then send a mouse click to the same and save the file. below is what I have got from my search but the declarations show as Red in excel 2013 (my home pc) and they look fine in excel 2010 (office pc) but doesn't work. Can you have a look.

    Code in Module when declaration for Screen Coordinates:
    Code:
    Option Explicit
    Private Type RECT
      Left                  As Long
      Top                   As Long
      Right                 As Long
      Bottom                As Long
    End Type
    
    Private Declare Function GetDC Lib "user32" ( _
      ByVal hwnd As Long) As Long
    Private Declare Function ReleaseDC Lib "user32" ( _
      ByVal hwnd As Long, ByVal hDC As Long) As Long
    Private Declare Function GetSystemMetrics Lib "user32.dll" ( _
      ByVal nIndex As Long) As Long
    Private Declare Function GetDeviceCaps Lib "gdi32" ( _
      ByVal hDC As Long, ByVal nIndex As Long) As Long
    Private Declare Function GetCurrentThreadId Lib "kernel32" ( _
      ) As Long
    
    Private Type POINTAPI
        x As Long
        y As Long
    End Type
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Function ScreenDPI(bVert As Boolean) As Long
      'in most cases this simply returns 96
      Static lDPI&(1), lDC&
      If lDPI(0) = 0 Then
        lDC = GetDC(0)
        lDPI(0) = GetDeviceCaps(lDC, 88&)    'horz
        lDPI(1) = GetDeviceCaps(lDC, 90&)    'vert
        lDC = ReleaseDC(0, lDC)
      End If
      ScreenDPI = lDPI(Abs(bVert))
    End Function
    
    Private Function PTtoPX(Points As Single, bVert As Boolean) As Long
      PTtoPX = Points * ScreenDPI(bVert) / 72
    End Function
    
    Sub GetRangeRect(ByVal rng As Range, ByRef rc As RECT)
      Dim wnd               As Window
      
      'requires additional code to verify the range is visible
      'etc.
      
      Set wnd = rng.Parent.Parent.Windows(1)
      With rng
        rc.Left = PTtoPX(.Left * wnd.Zoom / 100, 0) _
                  + wnd.PointsToScreenPixelsX(0)
        rc.Top = PTtoPX(.Top * wnd.Zoom / 100, 1) _
                 + wnd.PointsToScreenPixelsY(0)
        rc.Right = PTtoPX(.Width * wnd.Zoom / 100, 0) _
                   + rc.Left
        rc.Bottom = PTtoPX(.Height * wnd.Zoom / 100, 1) _
                    + rc.Top
      End With
    End Sub

    Module 2 for Mouse Click declaration :

    Code:
    Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
    Public 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)
    Public Const MOUSEEVENTF_LEFTDOWN = &H2
    Public Const MOUSEEVENTF_LEFTUP = &H4
    Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
    Public Const MOUSEEVENTF_RIGHTUP As Long = &H10
    
    Private Sub SingleClick()
      SetCursorPos 100, 100 'x and y position
      mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
      mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    End Sub
    
    Private Sub DoubleClick()
      'Double click as a quick series of two clicks
      SetCursorPos 100, 100 'x and y position
      mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
      mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
      mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
      mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
    End Sub
    
    Private Sub RightClick()
      'Right click
      SetCursorPos 200, 200 'x and y position
      mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
      mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
    End Sub

    Module 3 Macro to get screen coordinate and macro to open chrome search text and click on it.

    Code:
    Sub GetScreenCoordinateXY()
    Dim rc As RECT
    On Error GoTo done
    Call GetRangeRect(ActiveCell, rc)
    x = rc.Left
    y = rc.Top
    done:
    End Sub
    
    Sub OpenChromeandSearchtextandclick()
    
        Dim chromePath As String
        Dim search_string As String
        search_string = "Download"
        search_string = Replace(search_string, " ", "+")
    
    chromePath = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
    
    
        Shell (chromePath & " -url http://SSTR/#q=" & search_string)
        
        Application.Run "GetScreenCoordinateXY"
        Application.Run "SingleClick"
    
    End Sub

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Open a webpage in Chrome and download files and save them on a share drive folder

    declarations show as Red in excel
    try re-typing one to see if it also shows in RED

    EDIT:
    forget the above, i tested just one of the api functions, just copied from here and pasted it into excel 2013, it worked correctly

    my best guess is that your excel 2013 (unlike mine) is 64 bit, therefore you need 64 bit APIs
    you can check on your 2010 excel to see if it is 32 bit, you can test if each api returns a valid result when called,, then try to figure what else can go wrong, some of the API may work differently with different windows versions

    i have no idea if the rest of the code would work, as i uninstalled chrome, several years ago (after finding it was doing stuff even when i had not opened the program)
    Last edited by westconn1; Mar 1st, 2018 at 05:13 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width