Searching a url for excel files 2

[automaker]

I am at least minimally familiar with using DIR and Filesearch to search a directory for specific files. However, I am not familiar with accessing a directory referenced through a url. Can either of these be used in this situation or do I need something else? Below is some code that I have tried. The result is no files.

With Application.FileSearch
.LookIn = "

http://restofurl"

.FileType = msoFileTypeExcelWorkbooks
.Execute
MsgBox .FoundFiles.Count & " were found."
End With

 

[mscallisto]

first are you certain that there are excel files in "

http://restofurl

 

[automaker]

mscallisto,
Good question. I appreciate you starting at the lowest (most obvious) level. This is where I generally mess up.

Yes, there are excel files at the url location.

Mark

 

[Bong]

I've never seen this used before but it seems to me that the "http" protocol is wrong for this. That is, if it's to work at all, it would be "ftp://" maybe?

_________________
Bob Rashkin

 

[mscallisto]

I too don't know if this can be done but have you tried .FileType = msoFileTypeAllFiles as a test

 

[mscallisto]

Strange Happenings although I admit I don't know what I'm doing but when I changed the .lookin
to "www.siyafundavolunteers.co.za/images/"

Note I left HTTP: out of the URL

and searched for .FileType = msoFileTypeAllFiles

I get 6 files found even though there are many more files there.

Hopefully someone else has an answer, I for one would like to know.

 

[automaker]

Thanks to all for the valuable tips. Here is what I have to date.

In the url I am trying to search, there are many files that should meet the criteria (Excel).

The url is a company internal website that requires access be granted. I have the access and if I use something like
workbooks.open or workbooks.saveas no password is needed and the commands execute fine.

Searching for all file types does not give any results.

There is no error message

The server is not ftp

Seems to me that there should be something that works. Storing files on an intranet site is not new although I have not tried to access files like this using a program before.

I will keep trying things. If someone comes up with something, let everybody know. Seems a solution here would be useful to a bunch of folks.

Thanks,
Mark

 

[mscallisto]

Now I'm fairly sure I doesn't work and actually defaults to your "My Documents" folder when you try to enter a URL.

Try this code:

Option Explicit
Dim i As Integer


Sub v()
With Application.FileSearch
    .LookIn = "[URL unfurl="true"]www.siyafundavolunteers.co.za/images/"[/URL]
    .FileType = msoFileTypeAllFiles
    .Execute
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

 

[automaker]

Thanks,
I will give this a try and get back to you.

Mark

 

[automaker]

I tried this code and it does seem to default to the "my documents" folder. No error message or anything.

I am fairly ignorant when it comes to files stored on servers and accessed through the internet or an internal intranet. It seems though that the files are still stored on some type of drive and have an assigned location. By specifying that location, I should be able to work with those files.

I am stumped.

Mark

 

[RonRepp]

It is definitely defaulting to MyDocuments.

I tried using IE to go to

http://restofurl

and recieved a "Page could not be found" error.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[PHV]

Use an UNC path instead of an URL.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[automaker]

PH,
I thought the UNC path was used for a local network drive. This is an http path. Aren't they different?

Mark

 

[PHV]

if I use something like workbooks.open or workbooks.saveas ...
So, in which directory ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[automaker]

PH,
Please excuse my ignorance on this. Here is at least some of my confusion.

I can execute a statement like the following:

workbooks.open("

http://blah/blah/blah/filename.xls")

and the workbook will open

However, a group of statements like:

With Application.FileSearch
.LookIn = "

http://blah/blah/blah/"

.FileType = msoFileTypeAllFiles
.Execute
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Returns either no files or defaults to the "my documents" folder.

It seems to me that if I can locate a file to open it, I should be able to search the directory the file is in.

Thanks,
Mark

 

[strongm]

Excel understands and can speak the HTTP protocol. Thus downloading a file (which is what you are doing with workbook.open) is fine, since HTTP is in essence a protocol for downloading files.

But FileSearch would require a directory function of some description, which HTTP doesn't support

 

[automaker]

Strongm,
Thanks. That helps me understand why one works but the other doesn't.

So is there a different Excel VBA function that could be used to search for files?

Mark

 

[strongm]

Not if your target is a vanilla webserver; HTTP doesn't implement a filing system

 

[automaker]

Strongm,
Thanks for the info but that isn't what I wanted to read dammit. I guess I will have to figure another way.

Thanks again to everybody for their help.

Mark

 

[mscallisto]

Thanks for the explanation strongm

have a splat!

sam