Hi all,

can somebody stear me in the right direction.

I would like to download this file
http://nus800:8060/resources/thors/cni/CNI_Tool.xls into this folder
g:\everyone\thorsreports\ and schedule it to run automatically each
day, eg 9am with opening excel?

The file would replace the existing file each day, so that it's "up to
date"...

Can this be done with VBA?

Excel 97 WinNT, always online


TIA and cheers
steve

Steve



I found this code sometime ago and I use it almost daily to download
text file & a csv file






' Copyright ©1996-2003 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Const ERROR_SUCCESS As Long = 0
Private Const BINDF_GETNEWESTVERSION As Long = &H10
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000


Sub AutoDownloadIntranetFile()
Dim sSourceUrl As String


sSourceUrl = "http://nus800:8060/resources/thors/cni/CNI_Tool.xls"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
sSavedInetFile$ = "g:\everyone\thorsreports\CNI_Tool.xls"
If Dir(sSavedInetFile) <> "" Then
Kill sSavedInetFile
End If
If DownloadFile(sSourceUrl, sSavedInetFile) = False Then
MsgBox "Error In Downloading File" _
& Chr(10) _
& "Cannot Continue", vbCritic
End
End If
On Error GoTo 0
End Sub


Public Function DownloadFile(sSourceUrl As String, _
sLocalFile As String) As Boolean

'Download the file. BINDF_GETNEWESTVERSION forces
'the API to download from the specified source.
'Passing 0& as dwReserved causes the locally-cached
'copy to be downloaded, if available. If the API
'returns ERROR_SUCCESS (0), DownloadFile returns True.
DownloadFile = URLDownloadToFile(0&, _
sSourceUrl, sLocalFile, _
BINDF_GETNEWESTVERSION, _
0&) = ERROR_SUCCESS
End Function

Note Displayed Winking Face = close bracke

--
Message posted from http://www.ExcelForum.com

perfecto
cheers


On Wed, 28 Jul 2004 23:01:29 -0500, mudraker
<<(E-Mail Removed)>> wrote:

>Steve
>
>
>
>I found this code sometime ago and I use it almost daily to download a
>text file & a csv file
>
>
>
>
>
>
>' Copyright ©1996-2003 VBnet, Randy Birch, All Rights Reserved.
>' Some pages may also contain other copyrights by the author.
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>' Distribution: You can freely use this code in your own
>' applications, but you may not reproduce
>' or publish this code on any web site,
>' online service, or distribute as source
>' on any media without express permission.
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>Private Declare Function URLDownloadToFile Lib "urlmon" _
>Alias "URLDownloadToFileA" _
>(ByVal pCaller As Long, _
>ByVal szURL As String, _
>ByVal szFileName As String, _
>ByVal dwReserved As Long, _
>ByVal lpfnCB As Long) As Long
>
>Private Const ERROR_SUCCESS As Long = 0
>Private Const BINDF_GETNEWESTVERSION As Long = &H10
>Private Const INTERNET_FLAG_RELOAD As Long = &H80000000
>
>
>Sub AutoDownloadIntranetFile()
>Dim sSourceUrl As String
>
>
>sSourceUrl = "http://nus800:8060/resources/thors/cni/CNI_Tool.xls"
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>sSavedInetFile$ = "g:\everyone\thorsreports\CNI_Tool.xls"
>If Dir(sSavedInetFile) <> "" Then
>Kill sSavedInetFile
>End If
>If DownloadFile(sSourceUrl, sSavedInetFile) = False Then
>MsgBox "Error In Downloading File" _
>& Chr(10) _
>& "Cannot Continue", vbCritic
>End
>End If
>On Error GoTo 0
>End Sub
>
>
>Public Function DownloadFile(sSourceUrl As String, _
>sLocalFile As String) As Boolean
>
>'Download the file. BINDF_GETNEWESTVERSION forces
>'the API to download from the specified source.
>'Passing 0& as dwReserved causes the locally-cached
>'copy to be downloaded, if available. If the API
>'returns ERROR_SUCCESS (0), DownloadFile returns True.
>DownloadFile = URLDownloadToFile(0&, _
>sSourceUrl, sLocalFile, _
>BINDF_GETNEWESTVERSION, _
>0&) = ERROR_SUCCESS
>End Function
>
>Note Displayed Winking Face = close bracket
>
>
>---
>Message posted from http://www.ExcelForum.com/
>