|
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
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Here is the situation:
I want to import a list of contacts that I want excel to look up data from the web page and provide personal information. And the website required login and password.
For example,
www.hotmail.com
I would like to retrieve all my contacts details into excel. HELP
Here is the situation:
I have a list of part numbers that I want excel to look up data from the web page and provide cost information. The specific page for each part number is static with the exception of the part number itself. The built in "From Web" feature currently pulls in 9 lines of information. This would be great if I can have it only display row 4
The part number webpage:
http://www.schneider-electric.us/pro...countryCode=us
Data from "From Web" inquiry:
Quote:
ATV71LD11N4Z
Altivar 71 Lift Drive, 460Vac, 15 HP
$2,733.60 List Price
Non-Stock Item: This item is not normally stocked in our distribution facility.
Qty.
Product Datasheet
For example:
Input part numbers by copying and pasting
Quote:
ATV71LD11N4Z
ATV71LD15N4Z
ATV71LD18N4Z
would be referenced from
Quote:
http://www.schneider-electric.us/products-services/product-detail/?event=productDetail&partNumber=ATV71LD11N4Z&countryCode=us
http://www.schneider-electric.us/products-services/product-detail/?event=productDetail&partNumber=ATV71LD15N4Z&countryCode=us
http://www.schneider-electric.us/products-services/product-detail/?event=productDetail&partNumber=ATV71LD18N4Z&countryCode=us
-----------------
Ideally, I would like to set it up either in a formula or a macro that would provide me with table: part number; part description; part cost
This is essentially a table with: the input data; row 2 from the web query; and row 4
Quote:
ATV71LD11N4Z
Altivar 71 Lift Drive, 460Vac, 15 HP
$2,733.60
ATV71LD15N4Z
Altivar 71 Lift Drive, 460Vac, 20 HP
$3,398.40
ATV71LD18N4Z
Altivar 71 Lift Drive, 460Vac, 25 HP
$4,089.60
Hello,
I run multiple Queries in MS access and then export the output individually to seperate excel work books for further analysis.
I need help in automating this process. Is it possible to have all this done by just clicking a botton.
I want the process to
1. run all the ms access queries
2. Import all the data in excel. Output of each query is imported in a seperate worksheet in the same workbook.
3. Save the workbook in a specified location. Since this is a periodic excercise i want the file names to be named after the date on which this process is run.
Is it possible to write a macro which can do all the above.
I have a very basic undertanding of macros.
Thanks in advance.
A.S.
Hello, well, I am planning automating a report from my company (i am using excel 2002...company standard...), the problem is the data source is in an excel 2.1 formatted file... i have been trying to "import external data" aka "query" the file content to another workbook... and the problem is... the query failed with message "external table is not in the expected format". I think the problem is with the source file that is in excel 2.1 format...
I am wondering if querying from excel 2.1 file is possible... and if it is not... is there any free program to batch convert excel 2.1 file to excel 97-2002 file...
i can of course, open and save the file in latest excel format... or do what i always do... open the file and pres ctrl-A , ctrl-C, change to the target, ctrl-V, but that would not be fun with a lot of report...
Hello. I have a strange problem with importing data from an Access Query to Excel. It is working fine as long as I do not use wildcards (like * or ?)as a criteria in my Access Query. If I use wildcards as a criteria my imported data table is blank.
Example:
I can import that:
SELECT PUB_ar_mstr.ar_cust, PUB_ar_mstr.ar_nbr, PUB_ar_mstr.ar_date, PUB_ar_mstr.ar_amt, PUB_ar_mstr.ar_curr, PUB_ar_mstr.ar_due_date
FROM PUB_ar_mstr
WHERE (((PUB_ar_mstr.ar_nbr) Like "33133") AND ((PUB_ar_mstr.ar_date)>=#5/1/2009#) AND ((PUB_ar_mstr.ar_cr_terms)>"0"));
I can NOT import that:
SELECT PUB_ar_mstr.ar_cust, PUB_ar_mstr.ar_nbr, PUB_ar_mstr.ar_date, PUB_ar_mstr.ar_amt, PUB_ar_mstr.ar_curr, PUB_ar_mstr.ar_due_date
FROM PUB_ar_mstr
WHERE (((PUB_ar_mstr.ar_nbr) Like "3313?") AND ((PUB_ar_mstr.ar_date)>=#5/1/2009#) AND ((PUB_ar_mstr.ar_cr_terms)>"0"));
The only difference between these two SQL-Codes is the "?" instead of 3.
Why is the import to Excel affected by that??????????
i am trying to import data from many websites into excel.
the problem involves football teams and how many corners they took in each match. (don't ask!)
each webage has a predictable pattern for the address, so i have worked out that only a number changes in the address by 1 to get the next page or match.
eg
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
is the first match.
i am interested in gathering the two team names
SC Heerenveen
Excelsior
and the number of corners each ("Hoekschoppen")
4
5
i am trying to get these 4 bits of data into a row in excel, then underneath data from the next match:
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
the number in the middle of the address decreases by 1 each time, so the first few would be
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
is there a way i can automate this process?
(i have a list of all 306 addresses / matches in excel)
any help / hints appreciated
thanks in advance.
Hi,
I'm quite familiar using the data import wizard and its VBA to retrieve data from other excel sources but in this case, I'm looking for a solution for the following:
Normally you retrieve data and do not alter this data because once refreshed, the previous values (from source) will be shown again (depending on any changes in the source).
In this case I'd like to retrieve data, change data and have the changed data sent back to the source. I haven't found anything regarding this on the internet. Basically I'm hoping to find a solution to retrieve from source AND send back to source.
Hello everyone,
I am new to excel. Can somebody please help me out with following query.
I have some calculations written in one of the columns say B as simple text:-
(36 / (36 + 76 + 161 + 235 + 261 + 267)) * 100
(76 / (36 + 76 + 161 + 235 + 261 + 267)) * 100
Now at column C i want to add a formula same as written in Column c with K prefixed with every digit where K is a column. I mean C column shud have formula as
=(K36 / (K36 + K76 + K161 + K235 + K261 + K267)) * 100
=(K76 / (K36 + K76 + K161 + K235 + K261 + K267)) * 100.
i just want to reduce my manual effort. Can somebody please help me out with this.
Thanks a lot,
Shruti
This just seems like an easy EXCEL question, but I cannot figure it out!
http://scoweb.sco.ca.gov/UCP/NoticeD...yRecID=9098419 is the website I am trying to pull date - I receive a message saying "The Web query returned no data ..." when I select the YELLOW arrows to select either of the following tables :
Business Contact Information:
GENERAL MILLS INC.
TREVOR V GUNDERSON
NUMBER 1 GENERAL MILLS BLVD
MINNEAPOLIS, MN 55426
(763) 764-5324
Type of Property: VENDOR PAYMENTS
Amount Reported: $687.58
Shares Reported: 0.0000
Date Reported: 10/25/2007
Date of Last Contact: 6/7/2004
Reported Owner Name(s): UNITED WAY OF THE BA Y AREA
Reported Address: 50 CALIFORNIA ST STE 200, CA 94111
Any help?
Hi,
I have some data in a range in Cells A1:B7. What I'm trying to do is do a vlookup to return a value based on an input, put through the inputbox. However the Excel VBA Editor does not like the line of my code with the actual VLOOKUP function, but to me there is nothing wrong with it. Please can anyone help and tell me where I am going wrong. Here is the code.....
Sub Testing()
Dim Pricelist As Range
Dim Price As Double
Dim PartNum As Variant
Sheets("Prices").Activate
Set Pricelist = Sheets("Prices").Range("A2:B7")
PartNum = InputBox("Enter Part No ")
Price = WorksheetFunction.VLookup(PartNum, Range("Pricelist"), 2, False)
MsgBox PartNum & " Costs " & Price
End Sub
Many thanks
|
|
