Importing from online data source to a master spreadsheet

**ezzy85** · 04-01-2010, 01:10 AM

I have a master workbook with different sheets with different economic statistics. At the moment, I would go to a government website, download the latest data, copy/paste the latest row into my master spreadsheet. I do this 20 times for different statistics and it is very inefficient. Does anyone know of some alternatives to this? I was thinking of making a loop to go through my master sheet and match the headings with the government sheets and then paste the last row, however ill have issues with matching the correct date.

Does anyone have any tips to get me started on something like this?
Thanks

**royUK** · 04-01-2010, 01:30 AM

Have you tried the Import External Data method from the Data menu?

**ezzy85** · 04-01-2010, 01:37 AM

That only imports from a server or a database. The files that I have are just spreadsheets that i download every month.

**ecoanalyst** · 07-17-2010, 08:46 PM

Originally Posted by ezzy85

That only imports from a server or a database. The files that I have are just spreadsheets that i download every month.

Any luck?

I am using queries and they are more effective but some sites are discontinuing HTML eco releases - making queries inefficient...

**ezzy85** · 07-18-2010, 01:17 AM

unfortunately no. Do you have any sample queries that youre using? Maybe I could adapt them for my sites.

**foxguy** · 07-18-2010, 11:14 PM

Hi ezzy85;
It's been several years since I used this. But I used to download federal interest rates into a sheet with this code. It doesn't work now. I get an error saying it couldn't download, but the code should still work.
I remember that sURL had to start with "URL;" to let the query know that it was a URL

Please Login or Register  to view this content.

**Amar Thakkar** · 08-21-2011, 01:50 PM

Hi,
This is my first post on this website.
I have a big task at hand and I don't know how to write macros.

I want to download quarterly financial data for 100s of companies from a particular website.
Here is the link for data for 1 company.

http://www.equitymaster.com/research...arterly-Result

I want to download the table that appears on the webpage.
I have codes of many companies with me .. like INFY in the above URL ..
How do I automate this process?
Please help..

**Amar Thakkar** · 08-23-2011, 01:24 PM

Bump no response

somebody please help me on this

**Amar Thakkar** · 08-23-2011, 01:41 PM

Originally Posted by Amar Thakkar

Bump no response

somebody please help me on this

Found this code somewhere .. not able to run it though

' Module : Module1
' DateTime :
' Author : chijanzen
' Purpose :
'---------------------------------------------------------------------------------------
'Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Sub Initialize()
'link stored in A3
Call DownloadFile(Range("A3"))
End Sub
Sub DownloadFile(sURL As String)
Dim IE As New InternetExplorer
Dim oDoc As New MSHTML.HTMLDocument
Dim i As Integer
IE.navigate sURL
Do While IE.ReadyState <> READYSTATE_COMPLETE
Application.StatusBar = "WEB sit connecting......"
DoEvents
Loop
Set oDoc = IE.Document
For i = 0 To oDoc.Links.Length
On Error Resume Next
' Remove If for ALL downloads
If UCase(Right(oDoc.Links(i).href, 3)) = "ZIP" Then
'href:
Call savefile(oDoc.Links(i).href)
End If
Next i
Set oDoc = Nothing
Set IE = Nothing
Application.StatusBar = False
End Sub
Sub savefile(sURL As String)
f = GetFileName(sURL)
DoEvents
Application.StatusBar = f & "Downloading..."
returnValue = URLDownloadToFile(0, sURL, ThisWorkbook.Path & "\" & f, 0, 0)
End Sub
Function GetFileName(sText As String) As String
Dim pos As Integer
Do
GetFileName = Mid(sText, pos + 1)
pos = InStr(pos + 1, sText, "/")
Loop While pos > 0
End Function