(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

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

So, FileSearch cannot work with an http path? Am I seeing that
correctly?

Daisy

On Apr 18, 11:00 pm, Daisy P wrote:
(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

Thanks for the FTP suggestion. I'm afraid, however, it's not doable
for me because I'd have to code in an ID and password, which I'm
certain won't be given to me.

It appears then that there's no way to search an http link for file
names, even if it is behind our firewall and accessable. But, anyone
with comments, fire away.

In the meantime, I'm having IT write a routine that daily duplicates
the needed file (with a CONSTANT name) into a normal shared drive that
I can then access with a simple file open command. So far, they're
cooperative about doing it. I'm keeping my fingers crossed. I just
hate it that I wasn't able to handle this myself, but oh well. I
tried.

Daisy

On Apr 19, 12:01 am, "NickHK" wrote:
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- Hide quoted text -

- Show quoted text -