Daisy,
Assuming the server supports FTP also, this would be my route, although
others may have other suggestions.
Whilst this may look rather daunting, it is relatively simple. Also, once
mastered, adding functionality (create/kill folders/files) is
straight-forward.
http://vbnet.mvps.org/code/internet/ftplist.htm

There is also the Inet control

NickHK

"Daisy P" wrote in message
oups.com...
(Excel 2003)

Thought this would be simple, but I'm having problems.

I ultimately want to open an xls file, but first have to get the file
name because it changes. So, I need to return the xls file name that
begins with a given prefix from a certain folder. Only one such file
will exist in the folder. It always begins with "YTD Stats", but its
full name will change several times per year. It may be "YTD Stats
June - RevA" and then the next week change to "YTD Stats June - RevB".
Plus the month string can change. So, the only constants are the path,
it's an XLS file, and it begins with "YTD Stats".

The code below reflects the task and works fine when the path is a
normal Windows type folder and drive. But it fails when I use an HTTP
path. (Users DO have access permission to the path and successfully
open files using the path in code.)

So, can the code below be modified to work with an http path? If not,
what are some other suggestions to find the xls file name based on a
prefix and using an http path?

(If I can't make this work programmatically, I'll have to make the
user input it, which will be a real hassle for them being it changes
without warning. I'm really hoping someone knows of a solution.)

Thank you for helping,
Daisy


Sub GetWkbkName()
'Find the workbook name within a folder that matches a given prefix.
Dim strPath As String, strPrefix As String
strPath = "http://mycompany.com/firewall/tsp/shared%20documents"
strPrefix = "YTD Stats"
Dim rw As Long, i As Long
With Application.FileSearch
.NewSearch
.LookIn = strPath
.SearchSubFolders = False
.Filename = strPrefix & "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Range("a1").Value = Dir(.FoundFiles(i))
Exit For
Next i
Else
MsgBox "Error - No stat files found."
End If
End With
End Sub