VBA code to download Google Drive URL and copy, paste to Excel

[Legacy 316613]

Hi All,

I am VERY new to VBA and need help. I use the internet for virtually every macro I try to create but cannot seem to find a solution to my problem.

This is my first post so please excuse the lengthy request.

I need to regularly download two files which are maintained on our company's Google Drive.

I have managed to identify the Google Docs URL for these sheets which allow me to export the files when the
hyper-link is clicked. It does so in a .xls format for each file.

My issue is that when I click each hyper-link it opens the browser and performs the download however I cannot find a
way within the VBA macro to "return" back to VBA to then perform the second file download.

For sake of clarity, here is what I want to achieve.

1). I have a worksheet with only two cells populated.
2). Cell A1 has the google docs URL for the first file and Cell A2 for the second file.
3). I have a simple VBA code as follows:
Range ("A1").Hyperlinks(1).Follow and on next line Range ("A2").Hyperlinks(1).Follow

4). When the hyper-link is activated for the 1st file, it opens the Chrome browser and downloads the first file

5). However I cannot seem to "return" back to VBA from the browser window to perform the download of the second file.

My current fix is a manual process to click the Excel worksheet and then run the second file download process.

Can anyone help me automate this process within VBA.

Once I can achieve this, then I need to open the downloads folder, open the excel files and copy, paste the data to another workbook to update the data.

This step I believe is easy as I gather there is much information on the internet to assist me.

Any help will be much appreciated!

 

[marc j]

Hello,
I am by far not an expert in VBA, but have you considered using a "data connection"...If you record a macro, then select the "DATA" tab in the middle of the ribbon, then select from WEB, you can then place the url in the input box. The resulting code will look like below. The data is imported into the spreadsheet automatically so this may help with your next step.
I am not sure if it will help you or not, but I was able to use this to download multiple pages from the web.

Regards,
Marc

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://docs.google.com/spreadsheets/d/aaaovElDh_NaE3QcYDCYr4QUjDOz9IBOaMs/edit#gid=0" _
, Destination:=Range("$D$5"))
.CommandType = 0
.Name = "edit#gid=0"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With