Macro to Download, Open and Copy Excel Data from internet link to another excel file

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
Hi there,

I was wondering how to go about downloading an excel file from an IE Link (it's an inside lan link which only works on site so won't be able to share that with you).

I have pasted the link into the file so all the macro has to do is click the link and it will take you there.

I have tried recording a macro but it doesn't work, here is the macro:
<CODE> Sub FA_Download_Funds()
'
' FA_Download_Funds Macro
'
'
Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Windows("Download Funds.xlsx").Activate
End Sub

</CODE>

When I run this code, nothing happens it just takes me to the link.

I would like the macro to download the data (I click a link on the Internet Link) to 'Export to Excel', it then downloads a 'Read Only' Copy. I would then like the macro to copy this downloaded data to my current spreadsheet where the macro is.

Is this possible?

Thanks in advance of any help​
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
Hi there,

I got this macro to work for this

Range("A1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Windows("Download Funds.xlsx").Activate
Sheets("AccountDetails").Select
Range("A1").Select
ActiveSheet.Paste
Except it doesn't copy the data over from the file. It only copies what's copied in the clipboard.

How do I get it to copy the downloaded file? I have tried using 'Windows("AccountDetails_20141008073904(1).xls").Activate' but I got a run time error '9'.

Thanks in advance
 

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
Hi,

I've been searching online for a solution for this and I found this:

<CODE>Sub DownloadFile()
Dim myURL As String
myURL = "http://seb-fa-applications/FAApplicationManager/AccountMaintenance.aspx"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "P:\SEB Vals\Valuations Team\Reg Reporting\AccountDetails_20141008090126(1).xls", 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If

ThisWorkbook.FollowHyperlink "P:\SEB Vals\Valuations Team\Reg Reporting\AccountDetails_20141008090126(1).xls"

End Sub

</CODE>
<CODE>This works wonderfully except it downloads the url which I know is correct in the code but I need to go an extra step as on the URL you need to click 'export to excel' button to download the file.

Could anyone help please</CODE>
 

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
Can anyone help today please? I need to get the code to click a button and then copy the downloaded file?

Thanks

PS I think it's very unfair that there's a rule of not posting in other forums if this one is no help?? Shouldn't it be allowed if I don't get a response?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,423
Messages
5,528,681
Members
409,829
Latest member
CFreeamaz

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top