retrieving data from a table on a website

There is a internet page with chemical data :

http://spreadsheets.google.com/pub?key=twQ35hFIq-y0N84xQ5l0ICQ&output=html

It has chemicals in the first column and proerties in adjacent columns.

Is there a way to have a userform lookup a chemical in a textbox from the 
websites 1st column and then populate other textboxes with the adjacent data 
from the table?

Can anyone help?

Thanks,

Roger
0
Utf
1/18/2010 4:59:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1055 Views

Similar Articles

[PageSpeed] 46

On Jan 18, 9:59=A0am, Roger on Excel
<RogeronEx...@discussions.microsoft.com> wrote:
> There is a internet page with chemical data :
>
> http://spreadsheets.google.com/pub?key=3DtwQ35hFIq-y0N84xQ5l0ICQ&output..=
..
>
> It has chemicals in the first column and proerties in adjacent columns.
>
> Is there a way to have a userform lookup a chemical in a textbox from the
> websites 1st column and then populate other textboxes with the adjacent d=
ata
> from the table?
>
> Can anyone help?
>
> Thanks,
>
> Roger

Hi Roger...The following code should give you what you want.  It finds
the chemical name to search for in cell A2 and places the data of
interest in cell B2.  You can polish it by breaking the text string
containing the data into columns and putting in a header for each
column.  Also you can adopt it to text boxes rather than cells should
you choose...Ron

Sub Chem_Name()

' Name of chemical of interest is in A2
    chem =3D Range("A2")

' Get the source code from the website
    my_url =3D "http://spreadsheets.google.com/pub?key=3DtwQ35hFIq-
y0N84xQ5l0ICQ&output=3Dhtml"
    Set my_obj =3D CreateObject("MSXML2.XMLHTTP")
    my_obj.Open "GET", my_url, False
    my_obj.send
    my_var =3D my_obj.responsetext
    Set my_obj =3D Nothing

' Locate beginning and end of data for chemical of interest
    loc_1 =3D InStr(1, my_var, chem, vbTextCompare)
    loc_2 =3D InStr(loc_1, my_var, "S3", vbTextCompare)

' Extract and data of interest and remove unecessary characters
    chem_text =3D Mid(my_var, loc_1, loc_2 - loc_1)
    chem_text =3D Replace(chem_text, "Acetone", "")
    chem_text =3D Replace(chem_text, "class=3D", "")
    chem_text =3D Replace(chem_text, "'s2'", "")
    chem_text =3D Replace(chem_text, "<td  >", ", ")
    chem_text =3D Replace(chem_text, "<td  '", "")

' Put data in B2
    Range("B2") =3D chem_text
End Sub
0
ron
1/18/2010 5:42:44 PM
Looks like a simple WebQuery will get it -

Sub QueryChemicalData()
Dim sUrl As String

' two parts to avoid line wrapping!
sUrl = "http://spreadsheets.google.com/pub?key="
sUrl = sUrl & "twQ35hFIq-y0N84xQ5l0ICQ&output=html"

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & sUrl _
        , Destination:=Range("A1"))
        .Name = "ChemicalData"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False ' << note default is True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

' and to update -
Sub RefreshQ()
Dim qt As QueryTable

    Set qt = ActiveSheet.QueryTables("ChemicalData")
    'qt.BackgroundQuery = False
    qt.Refresh

End Sub

Regards,
Peter T



"Roger on Excel" <RogeronExcel@discussions.microsoft.com> wrote in message 
news:66AFC579-8F90-4DE8-AE46-1F183005A06A@microsoft.com...
> There is a internet page with chemical data :
>
> http://spreadsheets.google.com/pub?key=twQ35hFIq-y0N84xQ5l0ICQ&output=html
>
> It has chemicals in the first column and proerties in adjacent columns.
>
> Is there a way to have a userform lookup a chemical in a textbox from the
> websites 1st column and then populate other textboxes with the adjacent 
> data
> from the table?
>
> Can anyone help?
>
> Thanks,
>
> Roger 


0
Peter
1/18/2010 8:10:03 PM
Reply:

Similar Artilces:

How to retrieve the string from dialog box
Hi All, Does anyone know how to retrieve a string in IDC_STATIC on a dialog box? I have to print out a report with an application version at the bottom line for users to be aware of it in my application. I don't know how to retrieve the string in IDC_STATIC control on a dialog box. It would be very nice that someone let me know how to do that without calling the dialog box. TIA Hwasoo Lee Back 9 wrote: > Hi All, > > Does anyone know how to retrieve a string in IDC_STATIC on a dialog box? > I have to print out a report with an application version at the bottom line &g...

Running Totals for Pivot Tables
Hello I have a pivot table with months across the top and version as the row:- Data Version Jul Aug Sept Oct 1 1 4 7 10 2 2 5 8 11 3 3 6 9 12 Just wondered if it is possible to add cumulative totals to a pivot table? ie. Data Version Jul Aug Sept Oct 1 1 4 7 10 2 2 5 8 11 3 3 6 9 12 Cumulative 1 1 5 12 22 Thanks Lesley Make your pivot table and then use menu Insert/Calculated Field. You can n...

[MFC + DAO] Data selection
Hi, I've connection to database ( .mdb file) In one of tabels i've some fields: e.g like those: idTab - Autonumering idWho - Number idOK - Number idBad - Number And what i want to do is: const char pszIDTabFind[] = "idTab = %s "; CString strId - ID taken from another table CString str; str.Format( pszIDTabFind, strID ); RS.FindFirst ( str ); And what i get? An exception:/ !! Sth like : "Operation for this object is not supported" Why i can't find any data? What's more if i search for strings everything goes ok. Could You please tell me how should i...

problem in accessing fields from table with multiple foreign keys referencing single primary key
i came accross requirement of multiple foreign keys in a tablereferencing the same primary key another table. i created the tablerelations using the relations editor in access. even though i couldhave multiple foreign keys to a table access did allow the referentialintegrity.my database structure istable1 - students tabletable 2 - subjects tabletable 3 - subjects opted by studentsin the table 3 totally there are 6 subject columns and a student canopt for variable number of subjects.table structurestudentsID -> primary key, FirstName, LastName, ........subjectsID->primary key, subjectstud...

pivot table trouble (tricky)
I'm making a pivot table to study information about disease. Each disease has several characteristics like organism that causes it, symptoms, body part it effects, ways to detect it, ways to treat it, etc. Sometimes one category will have multiple entries in it -- for example: H. influenzae causes flu, there might be one treatment, one body part if effects, but 2 ways to detect and 5 different symptoms. I want to be able to arrange and sort my pivot table bases on any single property like the symptom of coughing, but the only way I know to do this is to repeat the entry many m...

Quarterly Data in Pivot Table
Hi Everyone, I am using Excel 2003. I just made my first pivot table last week. I have a chart and a sheet that I created from the wizard. I have grouped by years, quarters and months. On my sheet, I can see the years and months but not the data by quarter. I have noticed that I can double click on my year and it collapses down. I only have data from April thru Dec 2008, then Jan thru Dec 2009, and this year Jan thru March. I would like to know how to view the quarter data Thanks, Linda Hey Guys, Nevermind. I just dropped the month out of my grouping and ...

Data Table Manipulation
I have a rather large data table (~6600 lines of data) that has severa different locations, dates, times and then events for eac Location/Time pair. For example, at Location 1 at 12:00 on 5/6/200 there were 5 events. I'm trying to figure out the average number o events across all dates for a particular location and time (i.e Location 1 generally has 4.3 events at 13:00) Right now I'm doing brute for method with data filtering and using the automatic averag function imbedded in Excel 2000. I was curious if there is a mor elegant and easier solution, Currently it takes me about an ...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

tables for a window
There used to be a way (not Modifier) where I can have Great Plains show the tables linked to the current Great Plains window. I have forgotten it. can you please let me know. I believe you're looking for the Window Descriptions. If that's the case, go to Tools>> Resource Descriptions>> Windows. Leeland "Jack Tundra" wrote: > There used to be a way (not Modifier) where I can have Great Plains show the > tables linked to the current Great Plains window. I have forgotten it. can > you please let me know. > > > No, this is somethin...

retrieve an AVERAGE of a range using VLOOKUP to locate start/end d
Can I calculate the average of a range based on VLOOKUP? In other words, can I get Excel to "look up" the respective START / END dates of an investment position listed in Sheet 1 and then go and locate those 2 distinct cell values in in a different sheet (Sheet 2) utilizing VLOOKUP function and then return the calculated AVERAGE of the cells within that range back into Sheet 1? Appreciate any thoughts if doable in Excel in any way? tAKE A LOOK AT AverageIFS and see if that does what you want. If it were me, I'd probably enter the formula manually, record the resu...

Extra SQL row--bad table buffer--help!
I have a window in a custom form with data associated to rows in the scrolling window for the Payables Distribution form (through the VCHRNMBR and DSTSQNUM--it's a one-to-one relationship). When the row changes, the window saves any user changes. Suppose I have three rows of distributions, and have saved data for rows 1 and 2. Then I go back in and change the data associated with row 2, click "OK" and close the window. When I do this, I'm getting an extra row in SQL with the VCHRNMBR and DSTSQNUM of row 3 of the scrolling window, and all the user information for ...

data validation and null text
Is there a method (no macro) to prevent that when editing a cell it cannot contains a null (blank) text? I have tried with cell A1 inserting the following formula in Data validation: "=(LEN(A1)>0)" but it does not work. Uncheck the ignore blank checkbox as well. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Scarababau" <scarabeo@mailinator.com> wrote in message news:1143794739.937316.163040@u72g2000cwu.googlegroups.com... > Is there a method (no macro) to prevent that when editing a cell it > cannot contains a null (blank)...

Change source data in a pivot chart
Hi All I have a stack of pivot tables each with a pivot chart. I now need to move the location ot the Pivot tables which is the source data to the charts. But in Chart=>Source data.. is dimmed out and i can't find a way of changing the pivot chart's pivot table location. Any suggestions?? TIA jpohnb Did you try moving the pivot table to see what would happen? I just did a quick test with a pivot table and standalone chart. I cut the pivot table and copied it to a different sheet, and the pivot chart knew where the pivot table ended up. - Jon ------- Jon Peltier, Microsoft...

Pivot Table calculated field error
I have a calculated field in a pivot table in Excel 2007 that averages the selling price of an item. The Columns are the years from 2008 to present, Row Labels are items then by Customer Type, and the values are Quantities Sold, Total Dollar Amount and the calculated field is the average selling price (Dollars/Quantity). The problem is that each item is not sold for every year and I get Div/0 error. When I try the following =if(Dollars<>0, Dollars/Quantity,"") the #value error replaces the Div/0 error. I looked at the Contexture website and didn't find any ide...

pivot table calculations
how do i modify how a pivot table calculates my data? i would like to have they Grand Totals to use the =SUM(COUNTIF(E10:E15,"1")) & " / " &SUM(COUNTIF(E9:E15,"0")) formula, but it only sums and counts. not both. how do i get it to calulate the totals according to that formula? thanks much, stephen Hi AFAIK you can't add such formulas to a pivot table -- Regards Frank Kabel Frankfurt, Germany "Pringles." <Pringles@discussions.microsoft.com> schrieb im Newsbeitrag news:90E99FE0-EB98-4F22-B565-A2BBED9B3C92@microsoft.c...

Retrieve mouse position
Hi, I am writing an app in which I need to retrieve the mouse coordinates even when it mouse is outside the main frame. How do I do it? Thanks! xg >Hi, I am writing an app in which I need to retrieve the mouse coordinates >even when it mouse is outside the main frame. How do I do it? Thanks! Have you tried calling GetCursorPos? If you want to know when the mouse is moving I think you'll need to use a hook - have a look at SetWindowsHookEx WH_MOUSE. Dave IN addition to David's answer, you have not stated the problem you are trying to solve. For examp...

Outlook freeze with each input of data
Hello, I have the following problem. I work with the laptop client 3.0. With each input of data Outlook freezes. Then I have to shut down the task. This problem arises in similar way with each Client. Any ideas? Greetings, Stefan ...

Retrieving comment using range name
I have singular cells in different places referenced by their range names. Is there a way to get the comments corresponding to the referenced ranges (singular cells)? Thanks in advance, Tomek Set testrng = Range("A3:A4", "C3:C4") comment1 = testrng(1).Comment.Text .... commentn = testrng(n).Comment.Text Stefi „topola” ezt írta: > I have singular cells in different places referenced by their range > names. Is there a way to get the comments corresponding to the > referenced ranges (singular cells)? > Thanks in advance, > Tomek > > did yo...

Retrieving messages
Help, I mistakenly restored all the defaults on Outlook (the computer did not crash) losing all the messages and address book. I have had to set up the application from scratch entering account details etc. Question is: are all the messages and contacts still held on the computer even the program needs to be set up again and, if so, how do I retrieve them. Any assistance greatly appreciated. Thanks. P89 If you made a backup of your system or My Documents, search for a file called Outlook.pst. If you have that you can import all the information. Al "peterson89" <peterson...

max data transfer in storport driver
hi all, i am trying to find the max buffer size a storport miniport can transfer. as per my understanding in ATA driver it is max upto 128KB but not sure about storport. is it 16 MB max? what are the parameters which will affect the max data transfers size in storport? is it true that in PORT_CONFIGURATION_INFO data structure, number of physical breaks and max transfer size decides the maximum data transfer length from port driver? please reply, thanks, Hitesh On Aug 3, 12:09=A0am, hitesh <hitesh.ughr...@gmail.com> wrote: > hi all, > i am trying to find the...

Dynamic copying of data to new sheets
I need to create a seperate sheet in an excel workbook that contains th information from a data sheet. There are multiple columns and I need to seperate the rows out tha match values in one column. Number Status Notes 1 Fail none 2 Pass none 3 Not Run none 4 Missing none I need to make a seperate sheet for fail, pass, not run, and missing. I would like these sheets to update automatically, as the data sheet i quite large and it would take sometime to update the information b hand. Thanks, Quirthano -- Message posted from http://www.ExcelForum.com ...

Retrieving lost messages after Synchronization in Outlook Express
Setup for my hotmail messages to be downloaded to Outlook Express. When tried to connect to see an old message, didn't realize that it was setup to auto synch. My messages were deleted from hotmail, and are now lost on Outlook Express after synching. Is there anyway to retrieve the lost messages from Outlook Express? I really needed some of the items in there, and had downloaded them so they wouldn't be lost. Thanks! ...

Retrieve
Hi how do i retrieve all the postings i have posted in this group. sometimes forget of where i posted my i will be glad to get to know how to pull out my own questions cheers Click on your name to view the profile...Then click 'Recent posts by this user' http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=467a932d-65b6-4bb1-98e1-25def53a971a&mid=467a932d-65b6-4bb1-98e1-25def53a971a -- Jacob (MVP - Excel) "Amin" wrote: > Hi how ...

Rotating/sorting data
Hi everyone a bit of prob..im trying to do this Ja Fe Marc in to this orde Jan Feb March Apri can anyone tel me how to do it? Cheers Copy the range, select a new cell, and do Edit > Paste Special > Transpose. HTH Jason Atlanta, GA >-----Original Message----- >Hi everyone, >a bit of prob..im trying to do this >Jan >Feb >March > >in to this order >Jan Feb March April > >can anyone tel me how to do it? >Cheers >. > Hi Ali one way: if your original data is in column A and you want to get it in row 1 on a separate sheet (starting in A1) enter...

Forms and data
Good Morning, First time here so be gentle please, A few years ago I did a HND in Computing and was quite into Excel, and seem to remember using forms to input data and get report type outputs, THIS WAS DEFINATLY NOT DONE IN ACCESS. I have been asked by a friend if It is possible to create a form based excel file that can be used to log incoming call information such as the following Time/Date of call Autofilled What it was about Who it was for Action to be taken Resolved Y/N I know this is so easy to do in access, but she doesn't have access only excell, and her boss won't pay f...