| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query VBA problem
Hi,
I am trying to write a little script that will download webpages with the following type of URL http://cmr.tigr.org/tigr-scripts/CMR...cus=NT01NM0954 It works fine if I go through the steps manually in Excel 2003 I tried to record a macro, but was given the "error could not record", however. If I try to modify the standard examples I get an error "The web query contains no data" I also tried to open the webpage using excel,and it comes up blank I am very confused at this stage, as I don't know why I can do this manually but not with VBA. Any help will be greatly appreciated, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query VBA problem
Option Explicit
Sub CreateWebQuery() ' With ThisWorkbook Sheets.Add ActiveSheet.Name = "sheet" Range("A1").Value = "webpage; table 'results'" End With With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.something/", Destination:=Range("A3")) .Name = "" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query VBA problem
This one proved to be a bear in that you had to create the web fetch first
and then introduce the url. If you send me your email I will send the sample to you. -- Don Guillett SalesAid Software "Iain" wrote in message ... Hi, I am trying to write a little script that will download webpages with the following type of URL http://cmr.tigr.org/tigr-scripts/CMR...cus=NT01NM0954 It works fine if I go through the steps manually in Excel 2003 I tried to record a macro, but was given the "error could not record", however. If I try to modify the standard examples I get an error "The web query contains no data" I also tried to open the webpage using excel,and it comes up blank I am very confused at this stage, as I don't know why I can do this manually but not with VBA. Any help will be greatly appreciated, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query VBA problem
I think your problem may be that it is a framed page. Try using this
URL instead (which is the URL of the frame I believe you're attempting to extract the data from): http://cmr.tigr.org/tigr-scripts/CMR...cus=NT01NM0954 ================================================== ============================== If you're interested, I have a free open-source add-in that can grab the data for you off that page. The add-in, documentation on its functions, and sample templates can be found in the files area of this Yahoo group: http://finance.groups.yahoo.com/group/smf_addin/ For example, if I put the "Locus Name" of "NT01NM0954" in cell A1, these formula using my add-in: =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Putative identification") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Gene Symbol") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Coordina tes") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"DNA Molecule Name") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Gene length") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Protein length") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Molecula r Weight") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"pI") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"% GC") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Enzyme Commission #") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"TIGR Cellular Role Category") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Gene Ontology (GO) Role Category") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,2,"Gene Ontology (GO) Role Category",,,,1) ....get me this data: NT01NM0954 3-deoxy-D-manno-octulosonate cytidylyltransferase kdsB 851876-852637 chromosome Neisseria meningitidis serogroup A Z2491 762 253 27793.79 6.2405 59.97 2.7.7.38 Cell envelope: Biosynthesis and degradation of surface polysaccharides and lipopolysaccharides GO:0008690: molecular_function, 3-deoxy-manno-octulosonate cytidylyltransferase activity GO:0009244: biological_process, lipopolysaccharide core region biosynthetic process On Mar 20, 12:25 pm, Iain wrote: I am trying to write a little script that will download webpages with the following type of URL http://cmr.tigr.org/tigr-scripts/CMR...i?locus=NT01NM... It works fine if I go through the steps manually in Excel 2003 I tried to record a macro, but was given the "error could not record", however. If I try to modify the standard examples I get an error "The web query contains no data" I also tried to open the webpage using excel,and it comes up blank I am very confused at this stage, as I don't know why I can do this manually but not with VBA. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query VBA problem
Hi,
I would just like to thank everybody for all of their help :) Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query VBA problem
Randy,
I'm curious as to how you determined this vs the other url.? -- Don Guillett SalesAid Software "Randy Harmelink" wrote in message ups.com... I think your problem may be that it is a framed page. Try using this URL instead (which is the URL of the frame I believe you're attempting to extract the data from): http://cmr.tigr.org/tigr-scripts/CMR...cus=NT01NM0954 ================================================== ============================== If you're interested, I have a free open-source add-in that can grab the data for you off that page. The add-in, documentation on its functions, and sample templates can be found in the files area of this Yahoo group: http://finance.groups.yahoo.com/group/smf_addin/ For example, if I put the "Locus Name" of "NT01NM0954" in cell A1, these formula using my add-in: =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Putative identification") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Gene Symbol") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Coordina tes") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"DNA Molecule Name") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Gene length") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Protein length") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Molecula r Weight") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"pI") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"% GC") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Enzyme Commission #") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"TIGR Cellular Role Category") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,1,"Gene Ontology (GO) Role Category") =RCHGetTableCell("http://cmr.tigr.org/tigr-scripts/CMR/shared/ GenePageIdentity.cgi?tigr_locus="&$A$1,2,"Gene Ontology (GO) Role Category",,,,1) ...get me this data: NT01NM0954 3-deoxy-D-manno-octulosonate cytidylyltransferase kdsB 851876-852637 chromosome Neisseria meningitidis serogroup A Z2491 762 253 27793.79 6.2405 59.97 2.7.7.38 Cell envelope: Biosynthesis and degradation of surface polysaccharides and lipopolysaccharides GO:0008690: molecular_function, 3-deoxy-manno-octulosonate cytidylyltransferase activity GO:0009244: biological_process, lipopolysaccharide core region biosynthetic process On Mar 20, 12:25 pm, Iain wrote: I am trying to write a little script that will download webpages with the following type of URL http://cmr.tigr.org/tigr-scripts/CMR...i?locus=NT01NM... It works fine if I go through the steps manually in Excel 2003 I tried to record a macro, but was given the "error could not record", however. If I try to modify the standard examples I get an error "The web query contains no data" I also tried to open the webpage using excel,and it comes up blank I am very confused at this stage, as I don't know why I can do this manually but not with VBA. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query VBA problem
Since I use FireFox, I just did a right click on the table and saw
there was an option of " This Frame Show Only This Frame", which indicated it was a page with frames. After clicking that, I see the URL in the address bar for that frame by itself. I did a few experiments with that URL to see if I could shorten it (there were a lot of parameters that looked redundant). Another option would be to do a "view-source:" protocol on the original URL. That would show you the frameset coding and the URL for the frame in question. On Mar 21, 8:10 am, "Don Guillett" wrote: I'm curious as to how you determined this vs the other url.? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with MS Query - can't edit query | Excel Discussion (Misc queries) | |||
| Query problem | Excel Discussion (Misc queries) | |||
| Problem with the Web Query | Excel Programming | |||
| Problem with Query | Links and Linking in Excel | |||
| Problem with .Background Query option of ODBC Query | Excel Programming | |||
Linear Mode
