Excel refresh returns "Please enable JavaScript to view the page content."

[Adison]

Long time Mr. Excel viewer. First time poster. Thanks beforehand.

Here's the problem - Excel refresh returns "Please enable JavaScript to view the page content."

Understand a work around to this is to right click, edit query, click on import. This works 75% of the time. Not all the time - with 25% of the time returning the JavaS error.

However, when attempting to macro around this problem, the resulting macro fails.

With Selection.QueryTable
.Connection = _
"URL;http://[surpressed www.]"
.CommandType = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Here, ".CommandType = 0" returns a Run-time error '5'. There are some posts on this, but no solution.

When I rem out the ".CommandType..", you guessed it, the Java error returns.

I wrapped a loop around it to run until the error is gone. This loop runs in-definitely.

I've spent about three hours trying to fix this. Again, many thanks for your help.

HELP

 

[paulnb]

So I was wondering if we could use edge instead of IE since IE is also being deprecated.

No luck! Apparently no API for VBA in edge.

internet explorer - Will Microsoft Edge support COM automation (InternetExplorer object)? - Stack Overflow

Still wondering what the Microsoft solution is. Maybe Microsoft thinks we should be programming VBA with Javascript? lol. Is that even possible? I though Java was also on it's last legs?

 

[paulnb]

Have also tried to generate the table using querytable TEXT connection as opposed to URL connection, as it may be possible to feed it the raw html via a cut and paste since it cannot be obtained programatically....

Also a dead end as in TEXT mode, there is no way to tell it to recognize the HTML table tags.

 

[paulnb]

Ok. On to a solution here that can build the same thing as the querytable. Of course, who knows how long it will work for before either MS or the website changes something.
Still working on my solution it but basically like this...

>>

Const cURL = "http://www.asx.com.au/asx/statistics/prevBusDayAnns.do"

Dim doc As HTMLDocument
Dim Elem As IHTMLElement

Worksheets("Testing1").Activate
Application.ScreenUpdating = False

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True
ie.Navigate cURL

'Wait for initial page to load
Do While ie.Busy
Application.Wait (Now + TimeValue("0:00:02"))
Loop

Set doc = ie.Document

Set Tables = doc.getElementsByTagName("table")

Application.Wait (Now + TimeValue("0:00:02"))

destnLine = 1
Set DataTable = Tables(0) ' << NB for this site.
Firstrow = "Y"
For Each tablerow In DataTable.Rows
Set tableData = tablerow.Cells(0)
Worksheets("Testing1").Range("A" & destnLine).value = Left(tablerow.Cells(0).innerText, 10) ' does 9 characters
Worksheets("Testing1").Range("B" & destnLine).value = Left(tablerow.Cells(1).innerText, 10)
Worksheets("Testing1").Range("C" & destnLine).value = Left(tablerow.Cells(2).innerText, 50)
Worksheets("Testing1").Range("D" & destnLine).value = Left(tablerow.Cells(3).innerText, 50)
Worksheets("Testing1").Range("E" & destnLine).value = Left(tablerow.Cells(4).innerText, 50)
Worksheets("Testing1").Range("F" & destnLine).value = Left(tablerow.Cells(5).innerText, 50)
destnLine = destnLine + 1
Z = Z ' for checkpoint stop to debug
Next


>>


Hope this helps. Seems to build the same ASX table as querytables, but of course any subsequent processing is going to be equally broken using prior mentioned methods.

 

[paulnb]

New issue: Cannot seem to get certain aspects of the table, for example whether the announcement is 'price sensitive'.
Cell C is blank in the following example despite the price sensitive asterix being indicated.
Can anyone tell me how to get that out of the 'DOM', is it some sort of table cell attribute?

<tr class="">
<td>FID</td>
<td>6:32 PM</td>
<td class="pricesens"><img src="/images/asterix.gif" class="pricesens" alt="asterix" title="price sensitive"></td>
<td>Appendix 4C - quarterly</td>
<td>5</td>

were this not price sensitive, the html would look like:

<tr class="">
<td>FID</td>
<td>6:32 PM</td>
<td> </td>
<td>Appendix 4C - quarterly</td>
<td>5</td>

 

[paulnb]

Cracked it Beerbloke. All working again now using this 'DOM' thingy. Is it Javascript? I honestly don't know.

As per post above and the other tricky items like the price sensitive asterix can be obtained with: innerHTM, so for the price sensitive / asterix column:

      dataitem_value = tablerow.Cells(2).innerHTML
      LPosition = InStr(dataitem_value, "asterix.gif")
      If LPosition <> 0 Then
       Worksheets("Announcements").Range("C" & destnLine).value = "asterix"
      Else
       Worksheets("Announcements").Range("C" & destnLine).value = ""
      End If

 

[BeerBloke]

Hi paulnb

Just caught up with all your posts and will try your code. Well done for your perseverance and achievement! I must admit your knowledge is far better than mine.

I'm not sure if you got my private message. My work around was to use a well known share forum website and grab the announcements from there albeit not a simple table query.

But I'm going to try your code.

Cheers for your trouble!

 

[paulnb]

Yep, got the pm. but also got notified I had to delete it or I would not receive any others as the PM mailbox limit here is one single message!

In the end the code is simple and only a few lines to replace the query table, but it was hours with a forked stick and digging dry holes before I could divine where the water lay.

Maybe this DOM method which is of course restricted to IE will last a few more years before MS kills IE or the like.

 

[paulnb]

PS here is the start of my sub, with declarations which should get you up and running quickly.. some declarations used later in my sub though, so take what you need.
I do a mop up of prior day also in case I am away or miss some..

Sub S_whatever()
  ' Author: PNB
  
  Dim ie As Object
  Dim WsShell As Object
  Dim fso As Object
  Dim Return_code As Integer
  Dim fname As String
  Dim objWeb As QueryTable
  Dim sWebTable As String
  Dim DestnCell As String
  Dim DestnSheet As String
  Dim SubFuncRtn As Double
  Dim URL_exists As Boolean
  Dim rowcnt As Long
  Dim ann_date As String
  Dim ann_YYYYMMDD As String
  Dim TempArray_Descr() As Variant
  
  Dim doc As HTMLDocument
  Dim PageForm As HTMLFormElement
  Dim UserIdBox As HTMLInputElement
  Dim PasswordBox As HTMLInputElement
  Dim FormButton As HTMLInputButtonElement
  Dim Elem As IHTMLElement
  Dim elTableCells
  Dim ieObj As Object
  Dim ieDoc As Object
  Dim ieForm As Object


  Worksheets("Announcements").Activate
  Application.ScreenUpdating = False
  Set ie = Nothing

  Set ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True

  Sheets("Announcements").Range("A1:P2500").value = ""
  continue_processing = "Y"
  trycount = 0
  savecount = 0
  
  For x1 = 1 To 2
   Sheets("Announcements").Range("A1:P2500").value = ""
   If x1 = 1 Then
    fname = "http://www.asx.com.au/asx/statistics/prevBusDayAnns.do"
   Else
    fname = "http://www.asx.com.au/asx/statistics/todayAnns.do"
   End If

   ' Test URL exists
   URL_exists = HttpExists(fname)
   If URL_exists = True Then
    sWebTable = "1"
    DestnCell = "A1"
    DestnSheet = "Announcements"
     
    ie.Navigate cURL
    Do While ie.Busy
     Application.Wait (Now + TimeValue("0:00:02"))
    Loop
    Set doc = ie.Document
    
    Set Tables = doc.getElementsByTagName("table")
    Application.Wait (Now + TimeValue("0:00:02"))
    destnLine = 1
    Set DataTable = Tables(0)
    Firstrow = "Y"
    For Each tablerow In DataTable.Rows
     Set tableData = tablerow.Cells(0)
     If Firstrow = "Y" Then
      'xxx = tablerow.Cells(1).innerText
      If InStr(1, tablerow.Cells(1).innerText, "Published") = 0 Then
       MsgBox ("Error. ASX announcement table text not as expected")
       Exit For
      End If
     Else
      Worksheets("Announcements").Range("A" & destnLine).value = Left(tablerow.Cells(0).innerText, 100) ' does 99 characters
      Worksheets("Announcements").Range("B" & destnLine).value = Left(tablerow.Cells(1).innerText, 100)
      'Worksheets("Announcements").Range("C" & destnLine).value = Left(tablerow.Cells(2).innerText, 100)
      dataitem_value = tablerow.Cells(2).innerHTML
      LPosition = InStr(dataitem_value, "asterix.gif")
      If LPosition <> 0 Then
       Worksheets("Announcements").Range("C" & destnLine).value = "asterix"
      Else
       Worksheets("Announcements").Range("C" & destnLine).value = ""
      End If
      Worksheets("Announcements").Range("D" & destnLine).value = Left(tablerow.Cells(3).innerText, 100)
      Worksheets("Announcements").Range("E" & destnLine).value = Left(tablerow.Cells(4).innerText, 100)
      Worksheets("Announcements").Range("F" & destnLine).value = Left(tablerow.Cells(5).innerText, 100)
     End If
     Firstrow = "N"
     destnLine = destnLine + 1
     Z = Z
    Next
   ' at end of the above code you will have similar output as the former querytable.   

   Else
    ' Url not found.
    AppActivate "Microsoft Excel"
    Application.Wait (Now + TimeValue("0:00:01"))
    MsgBox "Warning: could not find url for main ASX page: " & fname
    continue_processing = "N"
   End If
   
  ' continue your code

 

[BeerBloke]

Thanks again. Got it working with your previous code.

Certainly a lot of time and effort to fix something that had been working flawlessly for a long time.

I also used/modified your code to get the price information of an individual share. However, while it was basically working, I'm now getting the following message from the ASX website via Internet Explorer (11):

The requested URL was rejected. Please consult with your administrator.

Your support ID is: 4039780453510230258

I've tried deleting browser history/cookies but I'm still experiencing this problem. Anyway, I'll hopefully figure out, and many thanks again for sharing your code.

 

[paulnb]

Yes, I got the same error 3 times today while testing. I am pretty sure this is thrown out by the ASX website and not the DOM... maybe due to too many requests?
I will have to see how often I get it in future. I also deleted cookies at the time, but still got the error afterward.

Are you making repeated calls to the ASX website for more data when you get it? If you are in a loop you might want to insert a delay of 4 seconds or so, so you don't overload their website and get your ip tagged as a nuisance?