Basic Web Query in Excel - Import Data from the Web into Excel

Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any website, as long as the data is contained within an html table, and put that data into a worksheet in Excel. This particular macro does not have auto refresh settings turned on and does not include and custom formatting options. This is the most basic version of a web query that accesses data from html tables and provides a good base from which to build a more complicated macro that can better serve your data import needs in Excel.

This macro, as it currently is, will import the stock price and other similar data for Google. That information will be up to date for the second after you import the data but a refresh rate has not been setup for this macro.

To use this macro you only need to adjust three different sections. Everything else is optional and does not need to be changed but has been left in the macro in case you want to change some of the easier to understand features of web query macros. Where you see this url http://finance.yahoo.com/q?s=goog, replace that with the url of the web page from which you want to import data or information. Where you see $A$1 after where is says Destination and Range, replace that range reference with the cell where you want the data to start importing into Excel. Lastly, replace the numbers within the quotation marks from this line .WebTables = "1,2" to represent the number of the table from which you want to import data. This is a little confusing but this parameter needs to know what number table to pull in. That means that if there are 10 html tables within the web page and you want to import data that comes from the 5th table on the webpage, you would put a 5 between the quotation marks. Alternatively, if the table has a name, you can put the name of the table. But, if you do that, make sure that you wrap each table's name with an extra 2 pairs of quotation marks - the final result would look something like this .WebTables = """table1"",""table2""".

Where to install the macro:  Module

Excel Macro to Perform a Basic Web Query in Excel and Import Data from the Web into Excel

Sub Basic_Web_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
 "URL;http://finance.yahoo.com/q?s=goog", Destination:=Range("$A$1"))
 .Name = "q?s=goog_2"
 .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 = "1,2"
 .WebPreFormattedTextToColumns = True
 .WebConsecutiveDelimitersAsOne = True
 .WebSingleBlockTextImport = False
 .WebDisableDateRecognition = False
 .WebDisableRedirections = False
 .Refresh BackgroundQuery:=False
 End With

 End Sub

---