createDocumentFromURL

[Guest]

Dear Sir,

I am using a macro to retreive multiple (>100) pages from the web using
createDocumentFromUrl function. I receive the following IE message:

This page has an unspecified potential security flaw. Would you like to
continue?

Is there a way to supress this message.

Note that retreive the page manually with IE, I do not receive this message.

Regards

JC

 

[Tim Williams]

Ave you tried

application.displayalerts=false

?

Tim.

 

[Guest]

yes, it didn't work - the message is from IE, not excel

 

[Tim Williams]

Do you have an example URL which would provide the message?

Or try the workaround from here:
http://athena.uwindsor.ca/units/its...f50492b3ba8020b185256efc00482b24?OpenDocument


Tim.

 

[Guest]

here's what I am using:
sURL =
"http://tradingroom.com.au/apps/qt/quote.ac?section=quotedetail&sy=tpl&type=delayedquote&code=BHP"

 

[Tim Williams]

Doesn't give me a warning. Try adusting your IE settings.

Tim

 

[Guest]

Dear Tim,

seems like your the only person that has responded to my multiple requests
on this issue. I have tried adjusting the ie settings, but did not manage to
solve the issue - sort of random changes to the ie options (which I am loathe
to take), I am stuck.

Any other ideas?

Thanks

JC

 

[Guest]

PS think that it might have something to do with cookie handling, as there is
a similar issue reported in the MSDN knowledge base about unspecified
potential security issue.

refer:

http://support.microsoft.com/default.aspx?scid=kb;en-us;323034

does this prompt any ideas?

Regards

JC

 

[Tim Williams]

Maybe you could show your exact code - or at least the part which is
erroring out.
I noticed that the page does have a pop-up which launches on load -
don't know if that's the issue.

You could always try using XMLHTTP to grab the source, save it locally
and then open it from there.
Or just open the page directly in Excel.

Tim.

 

[Guest]

Here is the code, will try the xmlhttp stuff as suggested. Again, thanks for
the ongoing assistance.

JC

Function TradingRoomGetQuoteData(ByVal sWS As String) As Boolean
Dim oMSHTML As MSHTML.HTMLDocument
Dim oHTMLDoc As MSHTML.HTMLDocument
Dim oDataTable As MSHTML.HTMLTable
Dim oWebTables As Object

Dim oWB As Workbook
Dim oWSCompanyList As Worksheet

Dim sQuoteQuery As String
Dim sTableData As String
Dim sASXCodes As String
Dim sURL As String

Dim iLastCompanyRow As Integer
Dim iCompanyRow As Integer
Dim iTableRow As Integer
Dim iTableCell As Integer

Dim bFoundLast As Boolean, bFoundYield As Boolean, bFoundCap As Boolean

Set oMSHTML = New MSHTML.HTMLDocument

Set oWB = ActiveWorkbook
Set oWSCompanyList = oWB.Worksheets(sWS)

sURL =
"http://tradingroom.com.au/apps/qt/quote.ac?section=quotedetail&sy=tpl&type=delayedquote&code="

iLastCompanyRow = oWSCompanyList.Cells.SpecialCells(xlLastCell).Row - 1

iCompanyRow = ASXLIST_OFFSET

Do While iCompanyRow <= iLastCompanyRow

sASXCodes = oWSCompanyList.Cells(iCompanyRow, 2).Value
'sQuoteQuery = sURL & "BHP"
sQuoteQuery = sURL & sASXCodes
Set oHTMLDoc = oMSHTML.createDocumentFromUrl(sQuoteQuery,
vbNullString)

While oHTMLDoc.readyState <> "complete"
DoEvents
Wend
'other code here deleted for simplicity
iCompanyRow = iCompanyRow + 1
Loop

End Function

 

[Tim Williams]

Try replacing this:
Set oHTMLDoc = oMSHTML.createDocumentFromUrl(sQuoteQuery, vbNullString)

with something like:
.....
dim oWB as workbook
....
set oWB=workbooks.open sQuoteQuery
'you should then be able to extract your information from the new workbook
oWB.close

Tim.

 

[Guest]

the xmlhttp suggestion works well - I will stick that as it is cleaner for
what I am trying to do. With regard to suggestion below (also tried
successfully): surely it can't be that easy...

thanks for the help

 

[erkr]

Fix found!!!

It was very hard to solve the issue similar as Jack Clift was reporting. therefor I post it here to help guys googling around like me.

On XP it was sufficient to add the sites you wanted to crawl as trusted sites, but for Vista that doensn't work either.

I solved by setting the editMode before creating the document. This seems to supress execution of active content (scripts etc), and therefor more safe:

 Set objMSHTML = New MSHTML.HTMLDocument
  objMSHTML.DesignMode = "on" 'surpress security warning IE "this page has an unspecified potential security flaw" in next line
  Set objDoc = objMSHTML.createDocumentFromUrl(strUrl, vbNullString)

Happy coding
ErKr