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

[noreendeasy]

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​

 

[noreendeasy]

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]

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]

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?