Macro Code To Extract From Web Pages
Can anybody post me some links to automating data extraction from Websites using VBA and IE. Not something I have ever bothered with before. Basically interested in using the controls and pulling tables.
View Complete Thread with Replies
Related Forum Messages:
Designing A Macro To Copy Data From Web Pages Into Spreadsheet
I have a folder full of uniformed web pages ( name and design) and I was wondering if it's possible to create a macro that will open up each page, copy specific data from each page, and paste it into an Excel 2002 spreadsheet. Unfortunately, my skills in VBA are very limited at best and I'm not sure if this is doable or is it something I'll have to go into each page and copy the data.
Collect The Data From Other Web Pages
I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages?
Web Queries For Multiple Pages?
My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )
I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.
Hyperlink To Varying Web Pages
I have a spreadsheet that has different product codes that I want to link to their corresponding web pages.
At the moment I individually input the hyperlink into each cell using this common address http://www.ace-sales.co.uk/Catalog.aspx?search=25617 it is just the 5 digit code at the end that changes each time
Can I put a common hyperlink in all the product code cells, then just put the product code in the cell and then the hyperlink automatically links to products corresponding page?
Open Web Page Macro Code
I am currently using the following code in order to open a web page by clicking a button:
Private Sub Timetable18_Click()
NewWindow = True
However, when this is executed it returns the following error:
Run-time error '-2146697210 (800c0006)':
Method 'FollowHyperlink' of object '_Workbook' failed
Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.
Web Form Submission Macro Code
Filling in a webform using the code below, works fine until I add a bit to set a Value to a field name that comes from a Radio Button. I'm presuming its because it doesn't actually select the Radio Button (as seen when I step through, you can see all the other values/fields update). Updates the page with all the other values correctly updated except for changing the Park Status.
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
.PageAction.Value = "Enter"
.SelectedCallID.Value = TicketNo
.lastactionby.Value = UserName
.ActionType.Value = ActionType
.Description.Value = Description...................
Macro Code To Log Onto Web Page Form
Im using information from oracle to do applications on excel. As im nothing related to IT department and I'm working on a BIG company, i did it by myself, i dont have support.
What i did is to investigate about oracleas (portal), then from my excel application i create the URL of the search i need, query it and paste on the document the table i need. I did this from months and worked great.
But now portal have been upgraded and need to do login before use it. I have some amateur knowledge about some HTML, PHP, CSS but i dont get how to fill the username and password for this new portal.
I will copy part of the code from portal that i think is the key:
<!-- Comment out script For old browsers
window.onload = setFocus;
<TABLE WIDTH="90%" BORDER="0" CELLPADDING="0" CELLSPACING="0">
Extract Web Data From Part Of Page
I would like to extract some data from a web page, but only part of it. The website is:
and I have a spreadsheet with many different ProjectId hyperlinks in a column (the final six-digit number). All I want is the large block of text that is in the middle of the page. Sometimes there is no text, as in:
So I don't know how to deal with that either...
I would just like to paste the text into a cell adjacent to the hyperlink. I know that there is a visibility issue with 1024 characters, but can cope with that (no text will be longer than the 32k+ chars).
Any help would be appreciated - it's for my wife's PhD, and I just want to make it easier on her. I am reasonably ok with macros, but am by no means expert! If you need clarification,
Extract Web Page Elements Into Worksheet
I recently cahve been working with a lot of webpages. Documenting the pages is quite loborious and inaccurate. I recently came across a utility that would explort all of the elements, their types etc and put it into a worksheet. For the life of me, I have not been able to find it. I was wondering if anybody knows of a utility like this, or how I could write a macro to parse this info.
Code To Print Multiple Pages
I have a macro that cycles through several student ID numbers and prints a progress report for each one. It works great, but I would like to add an option to pick which printer to use at the beginning of the code. In other words, when I run the macro, the printer box opens and I can pick the printer. When I click okay to print, the macro continues.
Extract Dividend Ex-Date From Yahoo Finance Web Page
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Set DataSheet = ActiveSheet
qurl = "LUX PXD XOM TOT "
Yahoourl = [url]& qurl & "%20&f=%20qd"
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Yahoourl, Destination:=DataSheet.Range("A2"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.SaveData = True
Range("A1"). CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Macro To Format All Pages The Same
Im trying to get a macro that will set every sheet in the workbook i am usine, to match sheet1.
Dim WS As Worksheet
'Loop through all worksheets
For Each WS In ActiveWorkbook.Worksheets
'Do this for all sheets except these sheets
If WS.Name = "Pcard Statement" Or WS.Name = "namedranges" Then
'copy "pcard statement" page setups/print magins
Sheets(WS.Name).Range("A1").PasteSpecial Paste:=print margins?
Application.CutCopyMode = False
Web Query Code
I am trying to import data from Yahoo, about half the time it works well, but the other half I get the Run-time error '1004' below.
Run-time error '1004':
The file could not be accessed. Try on of the following:
.. Make sure the specified folder exists.
.. Make sure the folder that contains the file is not read-only.
.. Make sure the file name does not contain any of the folloing charaters: < >
? [ ] : | or *
.. Make sure the file/path name doesn't contain more than 218
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Macro To Paste Formulas As Values On Specific Pages
I have a macro that pastes formulas as values on specific pages (("CTY EME", "Int Center", " Total SW dist cost", "Int, pubs & oth", "Total". Is there a way to compress the macro ie with the sheet names?
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
Navigate In Ie Using Source Code In Web Page
i am trying to make excel open a web page then naviagte to a link. I know how to do this if the link has the same address all the time using
Set ie = CreateObject("InternetExplorer.Application")
.Visible = True
' Go to web page
Do Until .readyState = 4
but how could I navigate to a link which continually changes.
for example on a web page the link always shows "report", but the actual address may changes say every hour.
Is there a way to navigate to what ever address sits behind "report"
Getting The Entire HTML Source Code From A Web Page
I am looking to read the source code for a website that keeps the stats for a hockey league in Sweden
For other sites i can use the code below and it works fine, but the site i am using to get the Sweden stats seem to keep the data in some type of a Java app (sorry still somewhat of a newbie) and doesn't work the same as the others
when i veiw the source code just by right clicking the page all the data i want shows up. When i try to use my code it doesn't get the stuff i want.
I have tried both objDoc.body.innerHTML and objDoc.body.outerHTML and i get different results but not the same as right clicking on the page and viewing the source, is there another command that i can use to get it all?
the website is
Const strURIpre As String = [url]
Set ie = CreateObject("internetexplorer.application")
If ie.ReadyState = 4 Then
ie.Visible = False
Web Queries Don't Refresh During Code Execution
I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete.
Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck.
Dim SheetNum As Integer, ListSheet As Integer
Dim eqtQT As New EventfulQTable
Dim QueriesDone As Boolean
SheetNum = 1
ListSheet = 12
QueriesDone = False
On Error Goto QueryError
Abnormal Decimal Conversion: Auto-import Statistics From The Web Via Web Query
I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
Web Query For Password Protected Web-sites
I am trying to import data into Excel from a webpage using the web query function; however, the webpage requires a login and password. I have the required login and password, but I cannot figure out how to make excel automatically input these into the correct fields and login in order to import the data.
Since I am trying to make all of our databases update automatically, I don't want to enter this info everyday when the macro runs.
Web Query Read The Web Address From A Cell
I have the following task
1. in sheet 1, column A starting A1 I have addresses of web pages. all of them are one and the same except the last digits. Sth like this
2. in sheet 2 I have to import the data from these web sites as it follows
from www.datapage.com/source1 starting from cell A1
from www.datapage.com/source2 starting from cell A101
from www.datapage.com/source3 starting from cell A201
The question: I am trying to write a Macro that tooks the address from sheet 1 cell A1 and import the data through web query using the address in the cell and import it in sheet 2 starting cell A1. Then loop and took the address from cell A1 in Sheet 1 and put it into sheet 2 cell A101 etc.
Web Query Macro
I'm using this macro in order to bring some data to two cells.. it's currently working great...
With ActiveSheet.QueryTables.Add(Connection:= _
.Name = "fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
However, as you can see from the text in bold i'm currently telling the url instead of bringing it from a cell with an url. I want the macro to be able to select the url from a cell.
I'm trying to use this solution, but i keep getting a Run-time erro ī5ī:
Invalid procedure call or argument.
connstring = Range("GUS8301!B3").Value
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Macro: Login To Web Query
So I have some data that I'd like to extract from some financial sites that I'm a member of (one in particular is investors.com). I try and pull stock info from the site, but I have to manually initiate a new web query, go to the website, login, then cancel the web query for the macro on my spreadsheet to start pulling information. What I'd like to do is have two cells referencing my user id and password, and then click a button that will goto the website from excel, login, and allow the extraction of financial info to occur.
Code To Extract 4 Right Chars And Paste In Another Column
I am having problems with a macro I am writing and I need some guidance on where I am going wrong (I am still a noob at VBA).
I have pasted the code below (not the most efficient use of code I know).
I have a list of departments and codes from the Intranet at Work. Now this contains a Department Code, Department Description and within the Description is a number in brackets showing how high up the organisation hierarchy they are....
Pulling Data From Web Without Web Query
information from a website that does not allow web queries (or at least from Excel 2003).
I have to pull the latest data every Wednesday from this webpage: [url]
To make things even more complicated the name of the page changes every week as well (corresponding to the date).
Extracting Web Data From A Sub Web Page
I am using the "CreateObject("InternetExplorer.Application")" method to extract data from a webpage. This webpage is a sub page that I cannot find the data for, even after filtering through all the ".document.all(x)" items? Any ideas on how to obtain this "sub page" data, since there is other data that I need which is on the "main" page? I don't want to have to call up this sub page separately since I'm already seeing it (physically able to view it on the main page but not able to extract it) from the main page?
Macro: Paste Special From Web Page
I need to create a macro to "paste special - values", therefore I have coded
It works well if copy and paste special within the same excel sheet.
However, when I ctrl C on any other website and revert back to excel to use my macro to paste special, it cannot work.
Macro Web Query Return Blank
The actual link from a list on the left side of that web page is "Printable List".
I can't work out why all other web pages work fine with;
Get External Data/Web Query, but this particular web page won't work.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://acttab.com.au/interbet/racing?type=venues", Destination:= _
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
Web Style 'Back' Button VBA Code
I have been trying to get a back button to work on an 2003 excel document that works like a website.
In the web toolbar there is a facility to go back and forward (very much like using internet explorer). I want a button on the actual sheet that does this command. If i record a macro and click the web 'back' button it records the end result of the process, not the process itself -
eg if sheet 1 and sheet 2 link to sheet 3 and I click a hyperlink from sheet 1 to sheet 3 then create a back button on sheet 3 and record a macro by clicking on the web toolbar back button the macro records the end result - eg Application.Goto Reference:="Sheet1!R14C8". However if i then go to sheet 3 through sheet 2, the back button I created takes me to sheet 1 not back to sheet 2.
Anyone know the code I could use to make it actually go 'back' to the previous page?
Extract URLs Macro
I am looking to extract URL's from a excel sheet.
Actually,, I have some software, which I have opened in notepad,
and then copied the data into excel.
The data is a mess.
It has had loads of unwanted characters etc. I've managed to clean this all up now.
What I'm looking for if possible is to extract all URLs to another worksheet.
As an example, I have 1 sheet that has 1654 rows of data, that goes right to
All URLs now have 1 space before it and 1 space after it,
If possible, I would like to be able to run a macro that would
look at this worksheet and find all the URL's, then return
them into a new spreadsheet in a list,,, and if possible
not returning duplicates,
(If it does find dups if a pop up could mention how many dups deleted)
if this is hard to do then no worries at all.
As a note,I'd spent a while looking for a macro to delete all unwanted characters,, found 1,,
so for info purposes, I post it below, seemed to run fine;
but if there are any other alteratives/suggestions, I'm all ears
Dim vAddText(0 To 38) As String
vAddText(0) = Chr(1)
vAddText(1) = Chr(2)
vAddText(2) = Chr(3)
vAddText(3) = Chr(4)
vAddText(4) = Chr(5)
vAddText(5) = Chr(6)
vAddText(6) = Chr(7)
vAddText(7) = Chr(8)
Macro To Extract Data
I would like to create a macro so that it will pull all the data from the worksheet "Data" and put it onto worksheet "Extracted Data". However I only require the rows which have Booking Office No SU17.
Using A Macro To Extract And Rearrange Data?
I have a big collection of spreadsheets that are all laid out in the same fashion: from A2 down are the names of different individuals (one name per row). Column titles (from B1 right) are the names of unique qualifications that these individuals possess. In the cell where a row and column intersects is the date at which this qualification was awarded. If this doesnít make total sense, Iíve attached a very basic version of this to the post (Sheet 1) Ė although the spreadsheets Iím working with have hundreds of individuals and scores of qualifications, rather than just a couple!
This data needs exporting/rearranging into a format thatís recognisable by another piece of software that we use. In this format, the data is split into three columns: the personís name, a possessed qualification and the date it was awarded. This means that a single individualís name may have multiple rows, since a different qualification will be present in each row for that person. An example of this layout is shown on Sheet 2 of the attachment.
Macro To Extract Data From Multiple Worksheets
I have a spreadsheet containing a factory's operational data, with each machine in the factory being represented by its own worksheet.
The sheets all have the same structure, with each line of data representing a shift (we have 12-hour continental shifts, so column A of all the pages has the date in an standard format, and column B has either "d" or "n", representing the shift). Various datat is documented on each machine on a shift-per-shift basis.
What I need to do is figure out how to do a macro that extracts all of the lines on all of the machines' worksheets that pertain to a particular shift (for instance, all of the lines where column A read "14-Mar" and column B read "d"), and export the values of the entire row to another worksheet, creating a shift summary on one page.
To do this, I would like to make the date and shift user-definable variables that appear at the top of what would become the summary sheet, and have a button that activated such a macro at the top of the page as well.
Macro To Extract Data And Place Into Template ...
I need to create a "purchasing programme sheet from extracted data in a different workbook.
In short I need to open the Allocation, Add stock qauntities to the the boxes with a quantity in it and if its a zero i need (Once the button has been pressed) the information to be extracted to the corresponding boxes in the Purchasing Programme Document: Description, Part Number and quantity.
Macro To Extract Tickers / Pivot Table
I need to manipulate some data in order to make it reasonable. I'm reading up on Macro's now in order to be able to do this stuff in the future, but I think macro's are a long term thing.
Actually before I go into that, is there any book one can recommend for macro's? I do have a friend's books, How to Do Everything with MS Office Excel 2003, MS Excel Bible 2003, and MS Office - Excel 2003 Formulas. Are these good or any other recommendations? In particular I would like some data to play around w/ so that when in the book they tel me to do something I actually have some data to manipulate. I imagine learning the basics of macro's will be easy enough, but as I understand it the real power of macro's comes from programming in VB, and I dont see how that can be covered in these books.
Now for the more specific questions -
My raw data looks like the attached excel file called Raw Data, I want it to end up as the other attached excel file called Result. How would I create a macro to read where the ticker begins and ends? Is there any way to do it w/o VB knowledge?
More importantly, I want to be able to order the tickers by how many times they've been mentioned. For example in the second excel file called Result, Laura has been mentioned twice as often as anyone else. When I sort this list I want to sort by who's been mentioned the most. So I'd like to see 6 lines of her data, followed by the person mentioned the second most (if they had 5 lines, the next 5 should be them etc). For this one would pivot tables do the job, or do I need macro's for this as well?
Macro To Extract Data From 3 Workbooks With Execptions
Iím not to sure this can be done but I will try and explain what I would like. I have a Master workbook simply called Master.xls (Price) and I need to extract data from 3 other workbooks, these workbooks are in a folder called INFO, the workbooks are called, Summary.xls,(Sheet1) Supplier.xls (Sheet1) and List.xls.(Sheet1). I have attached sample files of all the workbooks, the original Summary has about 2000 colums and the original List.xls has about 20000 columns.
The only workbook i would like to open would be the Master.xls, would it be possible to exract the data without opening the other workbooks? or at least to look like they are not opened. On the Master.xls
When the data button is clicked I would like the following info displayed, if possible. A3 info comes from looking at Summary.xls columns B & C and comparing it against List.xls columns A & B, when a match is found in either of the columns, the code in List.xls C column will be displayed in the Master.xls A3 cell
Macro/filters To Extract (copy/paste)
I have a mixed list of materials in column 'A'. I have two macro/filters to extract (copy/paste)... one for those ending in "[G]", and one for all others (those not ending in"[G]"), to columns 'B' & 'C' respectively. The code looks like this;
Range("B2:B" & Range("B65536").End(xlUp).Row + 1).ClearContents
Range("A2:A" & Range("A65536").End(xlUp).Row + 1).Select
Selection. AutoFilter Field:=1, Criteria1:="=*[G]"
Range("A2:A" & Range("A65536").End(xlUp).Row + 1).Select
Macro: Extract Data From Closed Workbook
I'm trying to create a macro that will allow me to extract data from another workbook (whose name and location is listed in a defined cell) and populate a range in my new sheet with this data.
Ideally, I want to be able to do this without actualy opening the source file, but any means to this end would be great!
I know this was possible in Lotus 123, but have not yet found a way to do it in Excel.
VBA Macro Code Needed To Run The Code Contained Within A Cell
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.
Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"
I need a macro to "execute this code", as if it were in the macro itself.
I have assigned the above to a variable, but am not sure how to execute it.
the_calc = Range("B5").value
Now, how do I run the_calc ?