-
Feb 18th, 2018, 02:27 PM
#1
Thread Starter
Hyperactive Member
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
-
Feb 19th, 2018, 11:56 PM
#2
Thread Starter
Hyperactive Member
Re: Open a webpage in Chrome and download files and save them on a share drive folder
Can someone help with the above code.
-
Feb 20th, 2018, 02:13 AM
#3
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
-
Mar 1st, 2018, 12:44 AM
#4
Thread Starter
Hyperactive Member
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
-
Mar 1st, 2018, 03:38 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|