Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Scraping Data from Web Into Excel Using VBA

I have a list of several hundred URLs in Excel. The URLs are all from the same site and have the same format and structure, but the specific data on the page varies. There are 3 specific pieces of information from each resulting web page that I would like to capture in an Excel worksheet. I am hopeful that there is a way to do this using VB, but I have not been able to get it to work.

I have successfully used a Web Query for one of the URLs from my list, identified the appropriate table (table 9), imported the data from that URL into Excel, identified the 3 fields I would like to copy (A2, A3, B5) and pasted that data into a new "results" worksheet. But now I need an automated solution that will look up the next URL in the list, run the Web Query and copy the contents of the three cells into the next row of the results worksheet, then move on to the next URL, etc.

Does anyone have any pointers how to do this? Or perhaps a better way to accomplish this than using VB? Thanks for your help!


Post your answer or comment

comments powered by Disqus
Can anyone suggest a method to import data from tables in Access and Import
them into Excel using VBA. I need the data in Excel so I can perform a
Fourier Analysis on them

Thanks

I'm writing data from arrays into Excel using VBA. I'm writing the array HandlersShort which has dimensions (1 to 619) to the range cells(3,4) to cells(621,4). The problem that I have is that it writes to the range but it fills the range with the first entry in the array. In other parts of the code it writes arrays just fine. Any ideas?

With RDosewkbk.Worksheets("Results by Stream")
If MaxRailB = True Then
Range(.Cells(xFirstrow%, izone * 3 + 1), .Cells(xLastRow%, 1 + izone * 3)).Value = HandlersShort
end if
end with

Thanks

I am trying to import data from an online database created by my predecessor.
I am successfully using the "Import data from web" dialogue except that it
will only download the first 20 records. The website has dropdown boxes
allowing me to change the number of records to display but the import still
only picks up the default number. I have accessed the online database (in
Zoho Creator) and changed the default to the maximum setting of 200 and I can
then download the first 200 records but not the rest. The problem is that I
can view the rest of the records in the Excel dialogue box but Excel won't
download them. Any ideas on how to download the rest?

Hello to all,
I'm trying to import a data from a website using Microsoft Excel however the link is too long & when I do import data from web & I past the link in there, it says that it's too long and it won't let me. I know it's possible but just don't no how to do it, anyone can help ? Thanks guys.

I am trying to write data from access to excel using ADO.net.
The code snippets I have tried are not tying together well.


	VB:
	
 ImportProducts() 
    Dim cnnConn As ADODB.Connection 
    Dim rstRecordset As ADODB.Recordset 
    Dim cmdCommand As ADODB.Command 
    Dim intColIndex As Integer 
    Dim TargetRange As Range 
     
    [B]Is this statement right ?[/B] 
    Set TargetRange = Application.Worksheets("FPS").Range("A2") 
     ' open the connection
    Set cnnConn = New ADODB.Connection 
    With cnnConn 
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" 
        .Open "C:salesitems.mdb" 
    End With 
     
     ' set the command text
    Set cmdCommand = New ADODB.Command 
    Set cmdCommand.ActiveConnection = cnnConn 
    With cmdCommand 
        .CommandText = "SELECT * FROM tblProduct" 
        .CommandType = adCmdText 
        .Execute 
    End With 
     ' Getting Close With This
     ' open the recordset
    Set rstRecordset = New ADODB.Recordset 
    Set rstRecordset.ActiveConnection = cnnConn 
     
    [B]I tried This - Didn 't work[/B]
     ' TargetRange.CopyFromRecordset rstRecordset
     
    [B]Also tried this -Didn 't work[/B]
    For intColIndex = 0 To rstRecordset.Fields.Count - 1 ' the field names
        TargetRange.Offset(0, intColIndex).Value = rstRecordset.Fields(intColIndex).Name 
    Next 
    TargetRange.Offset(1, 0).CopyFromRecordset rstRecordset ' the recordset data
     
     ' Close the connection and clean up.
    cnnConn.Close 
    Set cmdCommand = Nothing 
    Set rstRecordset = Nothing 
    Set cnnConn = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I read the article "Code to get data from webpage into Excel" while
I was searching to solve my problem.

Actually is quite similar to "donbowyer" 's problem. The code is
:

Sub TheSub()
'Microsoft Internet Controls
'Microsoft HTML Object Library

Dim myIE As InternetExplorer
Dim myDoc As MSHTML.HTMLDocument
Dim myImg As MSHTML.HTMLImg
Dim mySiteUrl As String
Dim E As MSHTML.HTMLGenericElement

MyDay = Format(Now, "YYYYMMDD")

mySiteUrl = "http://tv.pathfinder.gr/" 'URLhp
Set myIE = New InternetExplorer

With myIE
' .Visible = True
.Navigate mySiteUrl
Do Until .readyState = READYSTATE_COMPLETE: Loop
.Document.all.searchDt.Value = mDay
.Document.all.searchChannel.Value = "mega"
.Document.all.searchType.Value "0"
.Document.all.doSearch.Click
Do Until .readyState = READYSTATE_COMPLETE: Loop

Set myDoc = .Document

With myDoc
ActiveSheet.Cells(1, "A").Value = .body.innerText
End With

End With

myIE.Quit
Set myIE = Nothing
Set myDoc = Nothing
End Sub

The purpose is to go to the startpage : http://tv.pathfinder.gr/" ,
put 3 criteria programmatically (mDay, "mega", "0"), do the
Search (doSearch.Click) and after the page has loaded with the results
to get the results (the table with the TV program) in the active sheet.
The problem is that I get data from the first page not from the page
with the results (second page). Any suggestions would be most welcome.

Charalampos

I have been given the task of learning VBA. i am slowly starting to get my head around it. the first task i have been given is to get the data from a txt file. a csv. and place it into a excel spreadsheet using a macro in excel. now i have managed actually moving the data from the txt file into excel but the twist comes where i need to seperate all the data seperated by the commas into seperate columns. can anyone give me an example or a starting point so i can figure it out myself.

Any help is much appreciated

Hey,

I have managed to be able to import data from a webpage using the built-in function in Excel, but I want to go one step further and automatically import data from several pages...

Is there anyway to automatically import data from several pages, like

www.test.com/test1.htm
www.test.com/test2.htm
or
www.test.com/test.asp?id=1
www.test.com/test.asp?id=2
etc...

either straight into excel or some other program that allows me to import it into excel afterwards...

Hello Everyone!!

May someone help me out. I'm trying to get external data from a website by copying data and I don't want to copy it from the same webpage 51 times. I'm trying to get all the data from the online webpage by pasting it into one sheet in the excel worksheet. I'm using excel 2010 version.

Here is the link of the site I'm trying to get the data from.

http://sports.yahoo.com/nfl/stats/by...ld_category=DT

What I want to do here is I want all the stats for DE position from year 2008 season to 2010 season and the timeframe is from week1-17. (17 weeks * 3 seasons data) = 51 times

I don't want to keep hitting data->from web from the excel worksheet and punching in the site url to get the data 51 times and would like to save time.

Anyone's help will be greatly appreciated!! Thanks!!

I am using VBA in MS Excel to create tables in MS Access and se
relationships between the tables as I have large amounts of relate
data. Now in order to do that, I need to know how to list the names o
the tables and fields in the tables in the database. This would help m
to choose tables and further choose fields in those tables to se
relationships between them. How do I do that? Please help.

Peace,

Shivbo

--
shivbo
-----------------------------------------------------------------------
shivboy's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=55160

I am using VBA in MS Excel to create tables in MS Access and set relationships between the tables as I have large amounts of related data. Now in order to do that, I need to know how to list the names of the tables and fields in the tables in the database. This would help me to choose tables and further choose fields in those tables to set relationships between them. How do I do that? Please help.

Peace,

Shivboy

Hi,

I have written code to import data from Access to Excel using VB code since
I wanted values to be filtered on a value. I picked up this code from MS site
which used ADODB for connection. I found that this code was not working
properly despite choosing proper references.
Is there a simple way I can get data from Access to excel 2003 filtred on a
value which is stored in adjacent cell. (If I use get external data in Excel,
it dumps the entire table).
Appeciate your response.

Thanks,

I am trying to import data from RightFax into Excel. Any ideas?

Thank-you!

Hi

I export data from Pastel into Excel and combine accounts from various entities into one set of books via excel.
My problem is i have approximately 50 - 70 different sub sections which must total up at the bottom.
Unfortunately the descriptions for each line in teh section are not the same so i cant use Sub Total, is there a way i can mark or lable the cell or row of cells so that in my final total at teh bottom i can say for eg total up all cells in the column that labled total or something???

I really appreciate any help i can get.

thanks

john

. I had a similar query and everything works the way u explain above, however i am trying to retrieve Income statement, balance sheet and cash flow data. Now my problem is : The data that it retrieves is Quarterly data and not annual data, although i have written a macro for Annual data . Below is the code :
How do i solve this problem...plz help

At the same time if i replace the stockSymbol variable with a ticker like WMT (Walmart) then it works fine...
Public Sub GetHistoricalStockPrices1(ByVal StockSymbol As String)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/is?s=" & StockSymbol & "+Income+Statement&annual",
Destination _
:=Range("$c$3"))
.Name = "is?s=A+Income+Statement&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/bs?s=" & StockSymbol & "+Balance+Sheet&annual", Destination:=
_
Range("$I$3"))
.Name = "bs?s=B+Balance+Sheet&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/cf?s=" & StockSymbol & "+Cash+Flow&annual", Destination:= _
Range("$O$3"))
.Name = "cf?s=C+Cash+Flow&annual"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub
Sub RunFromButtonPress1()
GetHistoricalStockPrices1 (ActiveSheet.Range("a1"))
End Sub


Hi everyone,

I need your assistance! Could you please provide me a code or guide me how to execute / run the query in access then export the report into Excel using vba Excel? Is this possible? Codes only for running / refreshing the query are also sufficient.

Thanks in advance!

Hello All,

I need help getting data from a website into Excel with VBA.

I need to query the following website, "http://www.wunderground.com/history" and paste a large number of data points into Excel. My initial set of queries would be for 105 different locations and 3 years worth of data, so about 115,000 requests. After that, I would update the data approximately once a month totalling roughly 3,000 requests.

For example, I want to query location: 63146, Date: May 16, 2008. Then I want to paste the data from 8 of the columns in the hourly observations chart near the bottom of the page.

It is simple enough to loop through an array of locations and a date range, but getting the data from the web is where I am at a loss. I know a little VBA and have worked on a few projects, but I'm no expert. The searches I have done don't seem to address this issue; perhaps I don't know the best terminology to find the relevant help.

I know I have to view the source code of the site and work from there, but I can't even get VBA to open Internet Explorer and go to the URL. I am using Office 2003 and IE 7.

Much thanks to anyone that knows enough about this to help me get this going.

Jeremiah

I am trying to parse some data from some HTML web pages using VBA for Excel
(2002) but cannot
remember the function to open the web pages and assign the source info to a
variable in VBA. Does anyone know how to do this?

Thanks

Hy guys.

What I need to do is extract data from web page, the easiest way is with web query. But when I stated to learn how to do it, most help is with 'fixed' web pages. Here I have a page, where you enter a company's name and it gives you results (as a phone book). From that page (with results) I need to copy data to my excel worksheet.
I want to do a macro, where you enter a company's name into inputbox, click the button and internet explorer page opens with resulting page. So far I made it ok. But for web query I don't know.

Can anyone help me?
My code is bellow (since it is not an american page, for the ones that will test the code, you should try a search with company's name "ESISTEMI")

Much thanks in advance, Borut


	VB:
	
 
Sub Button1_Click() 
    Dim ie As Object 
    Dim osh As Worksheet 
    Set osh = ActiveSheet 
    vprasalnik 
    Application.ScreenUpdating = False 
    Set ie = CreateObject("InternetExplorer.application") 
    On Error Resume Next 
    ie.Navigate "[URL]http://www.bizi.si/[/URL]" 
    ie.Visible = True 
    Do Until ie.Document.ReadyState = "complete" 
        DoEvents 
    Loop 
    ie.Document.all.Item("ctl00$ContentPlaceHolderLeft$ucSearchCommon$tbSearchWhat").Value = firma 
    ie.Document.getElementById("ctl00_ContentPlaceHolderLeft_ucSearchCommon_btnSearch").Click 
    Do Until ie.Document.ReadyState = "complete" 
        DoEvents 
    Loop 
     
     '--> here i need web query code
     
End Sub 
 
Sub vprasalnik() 
    thesentence = InputBox("Type Text Please", "Using the VBA Input Box") 
    firma = thesentence 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to use VBA to manage invoice generation via an online service. The service is set up in such a way that the process cannot be made completely automatic, but I am trying to reduce it to as few manual steps as possible. Hence I am opening the Web Browser control in an Excel userform to streamline and guide the user's process.

The invoice is generated as a Tab-delimited TXT file. I don't want to save the file to disk, and selecting 'Open' opens the file as a web page, rather than in Notepad or Excel.

One way or another this data needs to be transferred to a hidden Excel worksheet for further processing. So I am trying to devise a way to copy the data from the web page and paste it onto the Excel worksheet.

Here is the relevant code from within the Web Browser's class module:


	VB:
	
 
    Application.Wait Now + TimeValue("00:00:01") 
    SendKeys "^a^c", False 
    Application.Wait Now + TimeValue("00:00:01") 
    ActiveWorkbook.Sheets("Sheet3").Paste Destination:=ActiveWorkbook.Sheets("Sheet3").Range("A1") 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The "^a^c" (control-A, control-C) part works correctly; if I interrupt the code at that point I can open Notepad and directly paste the invoice data.

But pasting into Excel fails in a way that has me baffled. Instead of the invoice it pastes the previous contents of the clipboard; for instance the invoice number that I copy and paste in an earlier step. I can even run the code as written above, see the invoice number pasted into Excel, and directly open Notepad, type Control-V, and have the actual invoice pasted into Notepad! It is as if Excel's paste function accesses different clipboard data from other applications.

So my brief question is: How do I get the data into Excel? The longer question is: Is there some step I have missed that specifies to Excel what clipboard data to paste?

Hi all,

I am trying to copy specific information from a web site to Excel using VBA. I have tried a couple of methods found on the internet to no avail. The type of copy proceedure that I tried was execCommand "copy". When I wrote the code ie.document.execCommand "copy". It just wants to copy the whole page. I just need on piece of the page. If a form name is needed, I think it is form1. I cant put the entire source code in here, due to this being a private site. Thanks in advance for your help.

hi,

i am currently tracking reports which are on a webpage and I was wondering if it is possible to copy the data which i get from the webpage to excel worksheet. I am using excel in tracking these data from the web. I have attached a sample files which are zipped. Is this possible in excel? I appreciate any response

Regards,

Stoey

On our intranet we have a function that does a fairly complex search for
"things". I won't bother you with what our "things" are, but one "thing" can
be described as one row in Excel (like Id, Date and Description). The result
of the search is normally just presented in the web browser as a set of rows
but occasionally the users want to
1. get the results into Excel
2. run a predefined macro that controls PowerPoint (using the aforementioned
data) and thereby create a more visual description of our "things" used in
company presentations.

I can generate an Excel file from my ASP code. The problem is the macro I
must apply to the data. For now the users must have the Excel file with the
macro, copy the data from my ASP-generated Excel file into it and then run
the macro. I would like to generate an Excel file with the data AND the
macro!

I looked at saving my Excel macro file in HTML and thought I could take the
code and put it in an ASP file, but that does not seem to be feasible.

What to do?

Regards,
Jonas B

Hello, I'm hoping someone can point me in the right direction. I want to import data from a SQL database into excel using VBA and ADO. I am comfortable writing code in VBA but am unfamiliar with ADO and writing SQL queries. I'm not really sure where to start. Any help would be greatly appreciated.

Thanks,
Jason


No luck finding an answer? You could always try Google.