So in Excel, via VBA, I extracted a dynamic URL (it changes periodically) from the stockcharts.com website via some XMLHTTP code I found.
Now I would like to copy the content of that dynamic URL page and paste it into Excel, but I am running into a problem. When I try to navigate to that dynamic URL via browser instance in order to copy/paste the content of the web page, instead of loading the page so I can copy/paste the content, it seems to download the PNG image on that page via Internet Explorer's "View Downloads" window, and interrupts the VBA code and never selects/pastes anything into Excel. Do you know what the issue might be here?
The dynamic URL seems to load the PNG when navigating to it, and Internet Explorer seems to intercept it and download it instead of leaving it to Excel to load the page so it can copy/paste the URL. The PNG is what I want to paste into Excel, so I need to prevent Internet Explorer from intercepting...
See my code below. The first part extracts the needed dynamic URL, while the second part attempts to load the dynamic URL content and copy/paste it into Excel, but fails.
Luke
Edit: By the way the extracted dynamic URL, if pasted into Internet Explorer outside of Excel properly loads the PNG on a website, which I can manually select all/copy/paste. Only within Excel, it triggers the download interception.
Sub GetLatestScriptoriumPosts()
Dim i As Integer
Dim sURL As String, sHTML As String, sAllPosts As String
Dim oHttp As Object
Dim lTopicstart As Long, lTopicend As Long
'URL to open
sURL = "http://stockcharts.com/h-sc/ui?s=GES&p=W&yr=3&mn=0&dy=0&id=p26710837442"
' Create an XMLHTTP object and add some error trapping
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
MsgBox "Error 0 has occured while creating a MSXML.XMLHTTPRequest object"
End If
On Error GoTo 0
If oHttp Is Nothing Then
MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
Exit Sub
End If
'Open the URL in browser object
oHttp.Open "GET", sURL, False
oHttp.Send
sHTML = oHttp.responseText
'Extract the desired information from the returned HTML code (text)
'To make things a little easier I usually cut of most of the unwanted code first
'so sHTML is smaller to work with.
lTopicstart = InStr(1, sHTML, "/c-sc/sc?s", vbTextCompare)
lTopicend = 48
sHTML = "http://stockcharts.com" & Mid(sHTML, lTopicstart, lTopicend)
Set oHttp = Nothing
MsgBox (sHTML)
Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Navigate URL:=sHTML
Do While ie.Busy Or Not ie.ReadyState = _
READYSTATE_COMPLETE
DoEvents
Loop
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, _
DisplayAsIcon:=False
ie.Quit
Set ie = Nothing
End Sub
Now I would like to copy the content of that dynamic URL page and paste it into Excel, but I am running into a problem. When I try to navigate to that dynamic URL via browser instance in order to copy/paste the content of the web page, instead of loading the page so I can copy/paste the content, it seems to download the PNG image on that page via Internet Explorer's "View Downloads" window, and interrupts the VBA code and never selects/pastes anything into Excel. Do you know what the issue might be here?
The dynamic URL seems to load the PNG when navigating to it, and Internet Explorer seems to intercept it and download it instead of leaving it to Excel to load the page so it can copy/paste the URL. The PNG is what I want to paste into Excel, so I need to prevent Internet Explorer from intercepting...
See my code below. The first part extracts the needed dynamic URL, while the second part attempts to load the dynamic URL content and copy/paste it into Excel, but fails.
Luke
Edit: By the way the extracted dynamic URL, if pasted into Internet Explorer outside of Excel properly loads the PNG on a website, which I can manually select all/copy/paste. Only within Excel, it triggers the download interception.
Sub GetLatestScriptoriumPosts()
Dim i As Integer
Dim sURL As String, sHTML As String, sAllPosts As String
Dim oHttp As Object
Dim lTopicstart As Long, lTopicend As Long
'URL to open
sURL = "http://stockcharts.com/h-sc/ui?s=GES&p=W&yr=3&mn=0&dy=0&id=p26710837442"
' Create an XMLHTTP object and add some error trapping
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
MsgBox "Error 0 has occured while creating a MSXML.XMLHTTPRequest object"
End If
On Error GoTo 0
If oHttp Is Nothing Then
MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
Exit Sub
End If
'Open the URL in browser object
oHttp.Open "GET", sURL, False
oHttp.Send
sHTML = oHttp.responseText
'Extract the desired information from the returned HTML code (text)
'To make things a little easier I usually cut of most of the unwanted code first
'so sHTML is smaller to work with.
lTopicstart = InStr(1, sHTML, "/c-sc/sc?s", vbTextCompare)
lTopicend = 48
sHTML = "http://stockcharts.com" & Mid(sHTML, lTopicstart, lTopicend)
Set oHttp = Nothing
MsgBox (sHTML)
Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Navigate URL:=sHTML
Do While ie.Busy Or Not ie.ReadyState = _
READYSTATE_COMPLETE
DoEvents
Loop
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, _
DisplayAsIcon:=False
ie.Quit
Set ie = Nothing
End Sub
