I don’t know if anyone can help, but I am trying to retrieve the html code for a https site (online broker site). I can do this no problem for a http site (second URL) using a get request comand.
The code is below (sorry but i could not post URLs so i had to chop them up).
I am not too sure if I am missing something or if this can actually be achieved using VBA. Does anyone know if this can be done?
[vba]
Sub test()
Dim HttpReq As Object
Dim response As String
Dim URL As String
Try:
[VBA]Sub test()
'requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
Dim HttpReq As New WinHttpRequest
'Dim HttpReq As Object
I have just tried the code you posted and added the reference to Micorsoft WinHTTP Serivices, version 5.1, however, the html text is still not being pulled from now from both the URLs.
Are there other settings I have to configure by any chance?
You are right it does work. However it is pulling back the source code from the homepage of Comsec and not the source code relating to the company ARI (ticker). This site you do have to log into and I have tried this but still it’s just pulling back the home page source code.
Is it possible to get around this in order to pull back the source code for a stock quote? Or is this a limitation?
Since I can't login, I can't really test it. I guess that is where the "s" for security comes from in https. Try viewing the source code directly in your browser.
There are other sites like yahoo that make getting stock quotes easy.
With HttpReq
.Open "GET", URL, False
.Send
End With
response = HttpReq.responseText
HttpReq.WaitForResponse
StrToTXTFile fn, response
Shell "notepad " & fn, vbNormalFocus
End Sub
Sub StrToTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(GetFolderName(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If
hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Print #hFile, str
Close hFile
End Sub
Rem Needs Tools > References > MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default...b;en-us;186118
Function GetFolderName(filespec As String) 'Returns path with trailing "\"
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetParentFolderName(filespec)
Set fso = Nothing
GetFolderName = s
End Function
[/VBA]
I am not sure exactly what you are trying to capture, but another route you could try if this is not working for you at all is using inner and outer html in htmlObjectLibrary...
The down side is that this is no where near as efficient (very slow) as you have to open the web page to access this data...
sample code below:
[VBA]Sub test()
Dim sURL As String
Dim IE As InternetExplorer
Dim doc As HTMLDocument
'requires Microsoft Internet Controls and Microsoft HTML Object Library References
'http://www.vbaexpress.com/forum/showthread.php?t=43237
Using CodeNinja's method, you can sacrifice speed for the other features of the IE object.
e.g.
[vba]' requires Microsoft Internet Controls and Microsoft HTML Object Library
Sub Test_IE()
Dim sURL As String
Dim IE As InternetExplorer
Dim doc As HTMLDocument
Set IE = New InternetExplorer
With IE
.Visible = True
'.Visible = False
.Navigate sURL
Do Until .ReadyState = 4: DoEvents: Loop
End With
Set doc = IE.Document
'Do Until Not doc Is Nothing: Loop
With doc
Debug.Print .body.innerHTML, vbLf, vbLf
.all.Item("Login1_txtUserName").Value = "ken"
.all.Item("Login1_txtPassword").Value = "pw"
.forms(0).submit
Debug.Print .body.innerHTML
End With
End Sub[/vba]
I have spent the last couple of days doing some research into accessing password https sites and while there are a few threads out there I can’t seem to find one that has been able to solve the issue (I have listed a couple) below. The SetCredential method seems to come up a lot, and I have tried to implement this into my code below adding my username and password for the site but with still no luck.
Reply With Quote
