I want to automatize the daily download of an Excel file from a server. I am
asked for a username and password when accessing the url.

I have not seen any conclusive solutions on the newsgroup nor on the
internet (for VB I did find a solution:
officeone.mvps.org/vba/ftp_download_file.html and
officeone.mvps.org/vba/http_download_file.html)

Any ideas how to do this?

TIA Martin

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



"Martin Los" wrote in message
...
I want to automatize the daily download of an Excel file from a server. I
am
asked for a username and password when accessing the url.

I have not seen any conclusive solutions on the newsgroup nor on the
internet (for VB I did find a solution:
officeone.mvps.org/vba/ftp_download_file.html and
officeone.mvps.org/vba/http_download_file.html)

Any ideas how to do this?

TIA Martin