Web query VBA problem

[Guest]

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/shared/GenePage.cgi?locus=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,

 

[Guest]

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

 

[Don Guillett]

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.

 

[Randy Harmelink]

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/shared/GenePageIdentity.cgi?tigr_locus=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,"Coordinates")
=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,"Molecular 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

 

[Don Guillett]

Using Randy's URL>establish the query and use this to refresh with the locus
name of your choice.
Updated workbook sent to you.

With Sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://cmr.tigr.org/tigr-scripts/CMR/shared/GenePageIdentity.cgi?tigr_locus="
& [locus] 'NT01NM0954"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

 

[Guest]

Hi,

I would just like to thank everybody for all of their help

Thanks

 

[Don Guillett]

Randy,
I'm curious as to how you determined this vs the other url.?

 

[Randy Harmelink]

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.