I too could not find much on this but after reviewing lots of sample code I
found on the net I pieced together this and it works for my applications.
Note that the sites I get data from post files specifically for downloading.
You will need to change the ranges used or hard code the values into your
code. I use ranges from the spreadsheet a lot (instead of hard-coding)in
order to make changes more easily.
Good Luck !
Sub GetWebPage()
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' Need Reference to Microsoft XML, v3.0
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'On Error GoTo ErrorHandler
Dim mywebsite, myusername, mypassword As String
Dim objXMLHTTP, xml
mywebsite = Range(MyRange)
myusername = Range("Username")
mypassword = Range("Password")
'Create new XML Object
Set xml = New XMLHTTP
'Open Website with user id and password
xml.Open "GET", mywebsite, False, _
myusername, mypassword
xml.send
'==================================================
'Wait for site to come up - usually very quick
mystate = xml.ReadyState
'Use Timer (20 seconds)to avoid endless loop should something go wrong
MyTimelimit = Now() + 0.000232
Do While xml.ReadyState <> 4
DoEvents
If Now() > MyTimelimit Then Exit Do
Loop
'==================================================
'This reads everything into one long continuous text stream - no rows or
columns
'So we put it into the clipboard (see sub ToClipboard) as text then paste
into worksheet as Text
RtnPage = xml.responseText
Set xml = Nothing
ErrorHandler:
End Sub
This puts string into clipboard so you can paste to spreadsheet:
Sub ToClipboard()
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' Need Reference to Microsoft Forms 2.0 Object Library
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
On Error GoTo ErrorHandler
'Can only put a dataobject as "text" to the clipboard
'So we make one
Dim MyDataObj As New dataobject
'Get string that was returned from Web Site
mytext = RtnPage
'The SetText method of the DataObject variable is used to store a text
string or numeric value in the variable
MyDataObj.SetText mytext
'To copy the contents of the variable MyDataObj to the Windows
'clipboard, use the PutInClipboard method
MyDataObj.PutInClipboard
'Reset all text to columns settings in case they were changed.
'Needed if getting more than one file or if
'this file is ran more than once without first exiting Excel
Sheets(MySheet).Select
Range("A6") = "If You See This an Error Occured in the Download - probably
page was not available" 'Must have data to use TextToColumns on
Range("A6").Select
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
'Paste the clipboard as text
Sheets(MySheet).Select
Range("A6").Select
'ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
ActiveSheet.Paste
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Comma:=True
Application.CutCopyMode = False
ErrorHandler:
Range("A1").Select
End Sub
