Writing Macro To Extract Data From Web Page
Mar 17, 2014
As part of a research project i need to extract as much data as possible from a webpage. The problem is to access each table i have to follow lots of links which I can't get to work automatically.
Its from a greyhound-data.com. So an example would be I want to extract all the racing stats for every dog that raced in swindon between 1st jan 2010-1st feb 2010. When i put it in the search engine I get 512 races in a table. I have to follow the link on name of race for each race.. Then I have to follow the link for each dog in the racers box, 6 for each race. Then when i'm on the dogs page theres a link saying on the top of the page saying a nember of races eg. 67races (number will vary for each dog). If I press that I get a list of all the racing stats for that dog. Its that table i need. But i also need the dogs name as a constant column. I need the table for each of the 6 dogs for each race out of the 512.. So in total 3072 tables.. It a lot of data and I have no idea how to get it?!
My biggest problem is I don't know how to say follow the various links. And I don't know how to loop multiple times - once for each of the races in the original list and then for the 6 dogs.
View 4 Replies
ADVERTISEMENT
Dec 31, 2008
I would like to extract some data from a web page, but only part of it. The website is:
[url]
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:
[url]
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,
View 9 Replies
View Related
May 8, 2014
I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.
So leaving it general, Word.doc and Excel.xlsx
It's all because my company's POS system will only export to Word but everything we do is in XL.
View 6 Replies
View Related
Jul 31, 2009
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.
View 2 Replies
View Related
Jun 29, 2007
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View 3 Replies
View Related
May 14, 2014
Basically, what I'm dealing with is an inventory system. When a cell value reaches below a certain quantity, we have a cell light up with the word "YES" (under the "re-order" column). What I'd like to do is have a macro that checks to see if that cell is populated with "YES" and if it is, to run another macro (which I already have written) that sends out an email notifying us of the need to re-order.
How it would work: The person pulling out the inventory would fill out this form and click "Submit"
submitbutton.jpg
I would then have a "call" code tied to the "Submit" button that when clicked, would run the macro to check cell population and send out the email. The email code is already written and works flawlessly on it's own. I just cant seem to figure out how to write a macro that checks for cell value and then runs it (or not) based on that.
View 14 Replies
View Related
Jun 26, 2008
Sub Dividend()
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
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("A1"). CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
End Sub
View 9 Replies
View Related
Jul 3, 2007
i have an report in which i need to delete the entire row if the column contains values more than 250K, but the chanllenge here is; if any of the cells in the column contains a formula then the macro should not delete the row.....
View 9 Replies
View Related
Oct 7, 2008
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.
View 3 Replies
View Related
May 24, 2014
Macro that extract information i need from column A into column B. My file contains comments regarding a sale made by a manager. And, the only information that pertains to me is the bags it will be using.
Below is a example of a comment (ROW 28 of the file):
PO 186
TRADER
SHIPPING WITH BROWN
240 X 20 # PANDA $ 9.90 EACH $ 49.50 CWT.
850 X 40 # PANDA $ 19.05 EACH $ 47.63 CWT.
US # 1 MAX. 4% BROKENS
The only information i need is 240 X 20 # PANDA and 850 X 40 # PANDA.
In the file, the Bags Tab, lists the names of all of the bags we current have in our inventory. I was thinking that a macro can run through column A of the comment tab and if it finds a match in the bag tab it would list it out in column B of the comments tab.
Here is my file. [URL]....
View 8 Replies
View Related
Nov 5, 2007
I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).
If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.
Should we write the macro or can it be accomplished by any Excel Function?
View 11 Replies
View Related
Oct 16, 2009
I'm trying to use code to write the numbers 1 to 100 in 10 rows of 10
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16......................
So far i have the following which works:
View 4 Replies
View Related
Oct 18, 2007
Every month I download a report from a state agency that I need to reformat, create charts from and send reminder emails based on some of the data. The report is downloaded as an EXCEL workbook containing a single worksheet. The worksheet is preformatted as a printed report. I have no control of this formatting and I can not get the data as a .csv file. The report contains 3 sections. See detailed report description below.
What I want to do:
1.Is copy the 3rd section of the report to a new worksheet.
2.Sort the new worksheet by name, and date.
3.Filter some names based on resource type.
4.Create a new file, which will be attached to an email to my boss for follow-up.
Originally I used the built-in EXCEL macro recording function and this worked for a couple of months. Then the number of rows in the 2nd section varied and my generated worksheet failed to sort correctly, and I lost the column headings so my filter no longer worked. The results were not something I could forward. I do not know how to modify the macro to search for the literals which end section 2 or start section 3 so I can copy the correct data to the new worksheet. I am not a VBA programmer, just an old-fart trying to do some administrative volunteer support for an important program, and really do not want to become one.
Detail Report Description:
Section 1: Headings –contain fixed headings in merged & centered cells.
Row 1 – Report heading 1 in merged cells A1–I1
Row 2 – Report heading 2 in merged cells A2-I2
Row 3 – Report heading 3 in merged cells A3–I3
Row 4 – Blank row............
View 9 Replies
View Related
Dec 10, 2008
I am trying to write an if statement in my macro. It is not returning what I want. Can anyone tell me where I have gone wrong.
Dim D As Variant
D = Sheets("Instructions").Range("n3").Value
If D = 1 Then
'range("n5).value
Else
'range("b3").value
End If
View 9 Replies
View Related
May 1, 2009
I am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01
2 w 2009.05.02
3 w 2009.05.01
4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
View 3 Replies
View Related
Mar 15, 2007
I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.
View 2 Replies
View Related
Sep 6, 2009
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.
View 2 Replies
View Related
Sep 23, 2008
I am now trying to hide rows when the data in a column matches that of a cell with a data validation list. Ideally i would like only the rows with matching data to show. When the cell is changed then the visible rows change and when the the entry "total" is selected all the rows are shown. There are several small tables of data on a page so I can't use auto filter. The data areas will also change in size as new entries are added. So the last row in the data tables will increase over time. I will copy and apply the macro to each separate table of data.
As an example cell c19 contains "lwsf"
The data table is in the range c24:q47. the column with matching data is column D so all rows of entries with "lwsf" in column D should be visible and the rest hidden. When c19 is changed to "TF" then the rows visible become those with TF in column D and the rest hidden.
View 14 Replies
View Related
Nov 18, 2008
Just wanted to know if I could have a range or ranges which are split. The current range i have is N5:AV6. I need to miss out every 6th cell so am looking to do a number of ranges like this - N5:R6, T5:X6, Z5:AD6 etc etc. If this can be done could you show me how I would need to lay it out?
View 2 Replies
View Related
Oct 17, 2008
I have a dataset that is as follows:
Col1 2 3 4 ...... 45
123 456 xxx xxx xxx
100 234 xxx xxx xxx
221 543 xxx xxx xxx
112 234
112 689
122 956
122 234
This goes on from left to right for 45 odd columns.
I need to copy each set i.e set 1 is 123 down to 122 to say cell a25 and then copy the next set i.e. 456 to 234 below the first set.
I need to somehow write a loop code that knows where to get the 2nd, then 3rd set, etc and copy it to the bottom of the preceeding set
View 9 Replies
View Related
Mar 5, 2008
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.
View 11 Replies
View Related
Feb 23, 2009
able to press the create report button ive created, which will open a template report sheet....
View 13 Replies
View Related
Feb 23, 2010
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
...........
View 4 Replies
View Related
Nov 15, 2013
Can VBA be used to extract a table from a website and output it on an excel sheet?
Basically we have to log in to a website and type data to our spreadsheet which is a bit tedious from the table on the website.
I'm guessing this can be done in some way but us having to enter username/password on website first to get to page we want will probably cause an issue would it?
View 1 Replies
View Related
Mar 15, 2003
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.
View 9 Replies
View Related
Nov 17, 2006
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.
View 3 Replies
View Related
Dec 11, 2008
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
View 8 Replies
View Related
Apr 20, 2014
i am looking macro code to extract data from sheet1 to sheet2 & sheet3, with criteria background color :
1) extract data from sheet1 to sheet2 for data contains/highlighted yellow color background
2) extract data from sheet1 to sheet3 for data contains/highlighted red color background
For detail information, see attached workbook : extract after coloring.xlsx‎
View 3 Replies
View Related
Jun 25, 2014
I am finding it very difficult to extract the Project reference data for each employee, I have attached sample working file for reference.
For Example
I have a project names in Rows and Employee names in Columns and each employee fill the working efforts in multiple projects and i would like to extract which all project they have worked for each month. Since the data is very huge i am not able to progress fast in collecting the data
Project NameEmp1Emp2Emp3
A
B 1015
C
D 2012010
Working.xlsx‎
View 5 Replies
View Related
Oct 12, 2012
I have following macro recorded to extract data from a mainframe system
open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
The requirement here is run the above macro for the next 30 days automatically, for example
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro
View 7 Replies
View Related
