Get a list of files from a URL

[Mark Scholes]

Hi,

I want to get a list of all the *.xls files from 'http://www.paj.gr.jp/english/statis/data/07/' using VBA in Excel. I can quess some of the file names and I have some code which copies the file, so if I have a list I could get them all

Thanks MarkS

[:) Sixthsense :)]

Unfortunately we don't have access to that url

[Mark Scholes]

You can copy files from there using this code
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, vFF As Long, oResp() As Byte

' You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
oXMLHTTP.send 'send request

' Wait for request to finish
Do While oXMLHTTP.readyState <> 4
DoEvents
Loop
' File not found
If InStr(oXMLHTTP.responseText, "404 Not Found") > 0 Then
MsgBox ("File Not Found" & Chr(13) & vWebFile)
Stop
End If

oResp = oXMLHTTP.responseBody 'Returns the results as a byte array

' Create local file and save results to it
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF

' Clear memory
Set oXMLHTTP = Nothing

End Function

Sub TestingSaveWebFiles()

SaveWebFile "http://www.paj.gr.jp/english/statis/data/07/paj-7E_201403.xls", "C:\paj-7E_201403.xls"

End Sub

So we have a least read access