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 RepliesDim struserID As String
Dim strPassword As String
Dim strUploadFile As String
Dim strQueryURL As String
Dim objIE As SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlColl As MSHTML.IHTMLElementCollection
Set objIE = New SHDocVw.InternetExplorer
struserID = "xxxxxx"
strPassword = "abcdef"
strUploadFile = "C:Misc estupload.txt"
Code navigates to sign on page, enters userID and password, clicks on submit. The password used causes upload page to load. This all works ok. After upload page is loaded, the code does not enter either of the two following "for" routines.
I have a few hundred of these divs, that I need sort and filter.
I would like to remove ALL HTML and be only left with the following:
Example Name = KeithEmail = keith@example.comStore = Store 1 (This only has 2 options. Store 1, Store 2)
All of the below code is in an individual single cell, so there are about 400 cells
HTML Code:
<div userid=""286"">
<div id=""694"">
<h1 style=""display:none"">Keith</h1>
[Code]....
Getting some web page data into Excel 2010 using VBA. My scenario however is set up with the following titles in cell A1, B1, C1, D1 and E1 : POST CODE, OUTLET, ADDRESS, TELEPHONE, EMAIL
The result I want to achieve is I enter a post code into cell A2 for example, Excel then uses IE to navigate to the relevant web page as defined in the VBA code. I then want the following to happen:
The InnerText of the web page's h1 tag is then inserted into the OUTLET cell (B2)The first instance of the p tag is then inserted into the ADDRESS cell (C2)The second instance of the p tag is then inserted into the TELEPHONE cell (D2)The third instance of the p tag is then inserted into the EMAIL cell (E2)
All instances of the p tag are contained in a div element called div class="adBox_content" . There are also 5 other DIVs above that DIV in the hierarchy.
Using the YouTube tutorial link, the method has worked for me using the getElementsByTagName("h1").innerText
However, when I try adding a second getElementsByTagName("p")(01).innerText the whole thing fails.
So I'm left with two problems; I can't make the VBA get more than one element at a time from the page, I can only either have the h1 or the first instance of the p tag. I've tried all the getElementBy methods and none of them seem to work in getting the second and third instances to show.
I also need the code to make the data be put on the same row ONLY as where the post code was entered. In this scenario for example of entering a post code into A2, the OUTLET needs to land in cell B2 only, ADDRESS C3 only etc.
By following the youtube tutorial above by giving the cells names to refer to in the code, the data ends up being inputted in all further rows with identical cell names. I need it to not do that.
The code is needed for around 300 rows of post codes that will be entered and refreshed every week or so.
I've defined a string array and would want to use it as a basis for a vlookup. Is it possible to find the elements of the array directly in the worksheet ?So far I've got :
[Code] .....
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,
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.
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
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies View RelatedI am trying to embed part of a webpage into my worksheet. Specifically, I want to include a form from a web-site that allows me to enter a flight number. I then want the results to pop-up in a new web-window.
So, I want the search box (or a section of the webpage) to appear in my excel worksheet so that I don't have to always open IE when searching for data.
[url]
That's the web page.
Can you use something like that to pull "All the text" from that URL back to Excel (like in Select All, Copy, PasteSpecial) rather than using SendKeys? The existing URL listed in your Test Sub would be fine to use in the example. If so, could you please modify the Test Sub code to show the code?
View 5 Replies View RelatedNot something I've ever had to do before as i generally work with data sheets, i'm creating a document to print but need the page number to start from a set value and not default "1" of "10" etc,
I need it to start from say "5" of "15" rather than the "1" of "10",
Its simple to do in Word, but like i say not something I've ever had to think about in Excel.
I am trying to setup a search page in a worksheet on a document. I want to be able to search by an indicator (DID#), but there maybe multiples of the same (DID#) in the data. I want to be able to have each instance of the multiples displayed on the search page. In addition, I want to reference other data that matches that (DID#) in separate cells.
Please see below, I want to have a search area that I can input the DID# and have a return of the corresponding info for that row. If I search "123" there are 2 entries and will need both displayed. The data is much bigger, but this is just a sample.
Date
DID#
Location
Type
Owner
12/13/14
123
Anywhere
Flooring
RL
[Code] .........
I have a workbook with four single page 11x14 worksheets. I'd like to convert each worksheet to a single page 11x14 PDF document so that I can take it to my local printer and have it printed on cardstock. I've tried two different free conversion programs and am frustrated to the bone. The pages keep running over at the bottom onto a second page, and how to make it fit-to-page.
I'd like margins of about 1/4" on the left, right, and top. No headers or anything like that. Then fit to page on 11x14 paper size. I think that's it.
I have an Excel file that use an internal link from one worksheet to the next. When I click on the link, I'd like it to go directly to a print preview page so I can print that one page instead just switching to the other worksheet. Is this possible?
View 3 Replies View Relatedhow to reference cells in 1 page from another and understand basic formula's, and would appreciate any input on how or if i can acheive what i'm trying to accomplish.
I have a worksheet with say 100 rows of information, it is a list of materials to go into a construction job. the columns to the right of the description calculate volume/quantity etc and produce a cost for the item. what i want to do is have a summary page that only lists the items that are being used, not all 100 items are in every job and i dont want to have to print the whole list with a bunch of empty values, which will just waste paper and ink. I'd like to enter a value in a cell or setup a check box or something that i could somehow use to then list that row or a part of that row of information on a seperate sheet, for a nice concise, easy to read list of job materials. i would like to do produce 1 sheet with the cost associated with the item, and one without cost.
I have a workbook with multiple sheets where the view for each sheet is the Normal view.
If I print the entire workbook, the active sheet changes to the Page Break Preview view after the printing finishes.
I have to then reset the view back to Normal.
I don't know why the active worksheet is changing. I don't want to annoy the people who will use this workbook by making them change back to normal view after each time they print.
Is there a formula to get the Name of the current Worksheet and place it in a Cell? I have a Macro that will create a list of all of the worsheets within a workbook, but it requires indexing through all worksheets. I have not found the command that returns the name of the active worksheet.
View 5 Replies View RelatedI am trying to populate name a worksheet and select it via values produced from an array. I don't know how to change the value produced from the array to select a worksheet.
Dim arrayTypeCount As Integer
Dim arrayTypeName As Variant
Dim awaitingSheetString As Worksheet
arrayTypeCount = 0
arrayTypeName = Array("awaiting_CP_Response_All_WS", "Repo", "Credit", "Rates")
arraytype = arrayTypeName(arrayTypeCount)
I then want to use arraytype to say arraytype.select
I have a spreadsheet which is an extract from another application's DB, and it's just ugly in Excel. No nice neat columns etc. Data from some tables of the other application were just dumped into cells. Some of the data I need is scattered about the worksheet in various cells. There are no seperators that are common, no common length of text strings I could use to pull every X word etc.
The one common thread I am working with here, is the the data I need to extract ALL begins with the first 4 letters, and containers a total of 8 characters. For example:
A3 - The dogs collar had the name ABCD1234 on it
J9 - Today I found a dog collar at the store, and it read ABCD2345
c5 - It says ABCD3456
I'm trying to basically see if there is a way/function within excel that can go through all of the cells and extract anything that says ABCD? (and the 4 wildcards after it)
The Find all function finds them all, but trying to just extract the data ABCD? info.
I found something similar here VBA - How to select a range in a non-activated worksheet but when I tried to "apply" it, I kept getting an error if the relevant sheet wasn't activated. My code
Code:
Dim SearchRange As Range, FindRow As Range
Dim column As String, search_string as string
column = "K"
[Code]...
What I want to do is to be able to search for a specific value in a specific column on the "Combo boxes" sheet, which isn't activated (the number of rows will never be large, thereof the hard-coded 200).
I get the impression that some sites say the relevant sheet has to be activated, others say it doesn't.
The code above works fine if the sheet is activated, otherwise it fails with error 91 on the msgbox row.
Code through which I can extract the Name and father/Husband name from the following data in new Work Sheet in two column Name and Father/Husband Name
Cell A1 contain - Name : Ashok Kumar Father's Name : Raja Ram House No. : 1 Age : 60 Sex : Male
Cell A2 contain - Name : Renu Gerg Husband's Name : Ashok Kumar House No. : 1 Age : 55 Sex : Female
Cell A3 contain - Name : Mohd Yusuf Father's Name : Mohd Sabir House No. : 2 Age : 65 Sex : Male
Cell A4 contain - Name : Rani kishwar Sultana Husband's Name : Mohd Yusuf House No. : 2 Age : 52 Sex : Female
-
-
-
Cell A55000 contain - Name : Sudesh Father's Name : PC joshi House No. : 3 Age : 39 Sex : Female
This is just a screenshot of my huge data of over 60,000 rows. Owing to the restriction of HTML Maker, I am just showing in less than 30 rows in this screenshot.
I need to extract only the UNIQUE ROWS depending upon the column called "Unique Code" to another worksheet. A particular row or a record is repeated as many as 90 times in this database. I need to extract the whole row which is unique depending upon the column C which is "Unique Code".
I have tried the Advanced Filter option a couple of times but it does not seem to work. Also, the Auto Filter option is not versatile for such amount of data.
I am trying to create a basic order history in excel from orders generated on our limited ERP system. I have exported the data required and imported to excel, but due to the export limitations, I can only export certain fields at a time.
This means that I have to create three sheets for all the data I require.
The first sheet has the bulk of the data and is now nicely collumned. However, I cannot get both the customer code and full customer name on the same sheet due to the aforementioned limitations.
Sheet 1 has customer code and other data, sheet 2 has customer code plus full customer name.
I have created a collumn for the customer name on sheet 1 (A3) next to the customer code (A2)
Basically, what I am trying to get it to do is as follows:
In "A3" I need to create a formula that states that if A2 contains a certain customer code, then look in sheet 2, match the code and place the full customer name for that code. Bearing in mind that we have over 395 customer codes in use, so asking for a specific code to lookup would be as time consuming as entering the codes manually.
I have a spreadsheet with company details in a worksheet. Each company details are in a new column. Like the following example:
company name
company name
company name
[Code]....
i basically need each of the bold heading in the first row of the new worksheet. Each company details will then need to be moved into a new row with the corresponding data under the correct heading column. Not all the companies have all the data for each heading it would be fine to have "none" value or blank in this case
I have a folder with an unlimited number of Excel Workbooks. Data within workbooks are in same format - 3 columns of data. I would like to extract the Data in the range (B55:B70) for every workbook, transpose the data and append by rows into one worksheet.
To be clear - I want to take data range B55:B70 from first workbook,transpose and put on first row - take data range from second workbook, transpose and put on second row, repeating for unlimited number of times...
I am trying to extract the data values from the references Bundesbank page and get them into a worksheet so I can manipulate from there. What I have is below.
Code:
Sub Get_Data()
Dim IE As New InternetExplorer
IE.Visible = False
[Code] .........
I am now trying to get the Index worksheet to extract data from the sheets that it has indexed, for example, it returns the sheet name, but i want it also to return the value that is contained in C2 and C3. Is this possible? I have attached the code which i am using below for ease of reference.
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index...................................
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
With Sheets("INJ")
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
webIE.Navigate myURL
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerhtml...................................
I am trying to extract the number of times a name appears on a certain range of cells on a worksheet, now I can get this to work, but I am sure there is a better way I just cant get it to work!
=SUM(COUNTIF(sales1!$B$8:$F$11,A18),COUNTIF(sales2!$B$8:$F$11,A18),COUNTIF(sales!$A$8:$B$11,A18))
I have only used 3 pf the worksheets, but in time there could be up to 30.