Web Query in Excel

E

ExcelMonkey

Hi folks. This is an area that I am not familiar with at all. I am
trying to query a web page from excl:

http://ets.powerpool.ab.ca/Market/Reports/CSDReportServlet

What I want is to dump the data from the tables into Sheet1 cell A1. I
originally attempted to do this manually by going to Data/Import
Data/New Web Query. I put the address in the address window, clicked
the boxes next to the tables I wanted and hit Import. I get an error
message say the web query returned no data.

A colleague of mine told me that the reason it does not work is that
the HTML code has a line in it (line 3) that says:

<p>The report is downloading, please wait...</p>

As such the query is failing due to this line of code. Firstly I need
to figure out why this is not working manually. Upon figuring that
out, I need to write VBA code which will undetake this task - updating
every 5 minutes. That is I want it to paste the desired table to the
same excel cell destination every 5 minutes.

Any help would be appreciated.

Thank-you
 
J

Jake Marx

Hi ExcelMonkey >,

I think your colleague is correct. That div and the javascript code is
causing Excel some problems.

Here's a workaround that may work for you. Below is a subroutine I wrote to
grab the HTML from a URL and save the resulting document to a local path,
modifying the HTML to remove the offending parts. There's no error
handling, so you may want to add that.

You could call this sub every 5 minutes using Application.OnTime. You can
then set up your web query to point to the local file and set it to
automatically refresh every 5 minutes, too.

Public Sub GenerateLocalHTML(rsURL, rsTargetPath)
Dim xml As Object
Dim fso As Object
Dim lHeadPos As Long
Dim sBase As String
Dim sHTML As String

Set xml = CreateObject("Microsoft.XMLHTTP")

With xml
.Open "GET", rsURL
.send
sHTML = .responseText
End With

Set xml = Nothing

If Len(sHTML) Then
'/ set base URL for images
lHeadPos = InStr(1, sHTML, "<head>", vbTextCompare)
If lHeadPos Then
sBase = Left$(rsURL, InStr(InStr(1, rsURL, "//") _
+ 2, rsURL, "/") - 1)
sHTML = "<html><head><base href=""" & sBase & """>" _
& Mid$(sHTML, lHeadPos + 6)
End If
'/ create file
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.CreateTextFile(rsTargetPath, True)
.Write sHTML
.Close
End With
Set fso = Nothing
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
T

Tom Ogilvy

Note that the help for the page says it refreshes every 10 minutes, so 5
minutes may be too frequent.

--
Regards,
Tom Ogilvy

Jake Marx said:
Hi ExcelMonkey >,

I think your colleague is correct. That div and the javascript code is
causing Excel some problems.

Here's a workaround that may work for you. Below is a subroutine I wrote to
grab the HTML from a URL and save the resulting document to a local path,
modifying the HTML to remove the offending parts. There's no error
handling, so you may want to add that.

You could call this sub every 5 minutes using Application.OnTime. You can
then set up your web query to point to the local file and set it to
automatically refresh every 5 minutes, too.

Public Sub GenerateLocalHTML(rsURL, rsTargetPath)
Dim xml As Object
Dim fso As Object
Dim lHeadPos As Long
Dim sBase As String
Dim sHTML As String

Set xml = CreateObject("Microsoft.XMLHTTP")

With xml
.Open "GET", rsURL
.send
sHTML = .responseText
End With

Set xml = Nothing

If Len(sHTML) Then
'/ set base URL for images
lHeadPos = InStr(1, sHTML, "<head>", vbTextCompare)
If lHeadPos Then
sBase = Left$(rsURL, InStr(InStr(1, rsURL, "//") _
+ 2, rsURL, "/") - 1)
sHTML = "<html><head><base href=""" & sBase & """>" _
& Mid$(sHTML, lHeadPos + 6)
End If
'/ create file
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.CreateTextFile(rsTargetPath, True)
.Write sHTML
.Close
End With
Set fso = Nothing
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi folks. This is an area that I am not familiar with at all. I am
trying to query a web page from excl:

http://ets.powerpool.ab.ca/Market/Reports/CSDReportServlet

What I want is to dump the data from the tables into Sheet1 cell A1.
I originally attempted to do this manually by going to Data/Import
Data/New Web Query. I put the address in the address window, clicked
the boxes next to the tables I wanted and hit Import. I get an error
message say the web query returned no data.

A colleague of mine told me that the reason it does not work is that
the HTML code has a line in it (line 3) that says:

<p>The report is downloading, please wait...</p>

As such the query is failing due to this line of code. Firstly I need
to figure out why this is not working manually. Upon figuring that
out, I need to write VBA code which will undetake this task - updating
every 5 minutes. That is I want it to paste the desired table to the
same excel cell destination every 5 minutes.

Any help would be appreciated.

Thank-you
Click to expand...
Click to expand...
 
E

ExcelMonkey

Hey Jake, I tried the code. My Norton antivirus detected it an
starting popping up alerts regarding suspicious code. I disabled i
and when it ran, it failed with an error statement saying permissio
denied. What happened?

Thank
 
Top