How do I crate a web query, but of a windows file directory

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dfalconx - 25 Sep 2007 14:32 GMT

Hi,

I have an interesting problem which I have been trying to solve. I've got a
windows explorer directory full of word files which go back 3 years. Their
file names are all well formatted and can be imported into excel as text. I
then use excel to sort the files from the names into invoice numbers,
customer names, and billing date (all a part of the word file name). I also
use excel to hyperlink back to the original file on the hard drive. Now I
have a tool which anyone can use to retrieve a file.

Problem: It's easy to add a batch of file names with hyperlinks to excel.
But adding individual ones is time consuming. Can I use a database query to
monitor the files in the windows explorer folder for new additions? I've
even thought of using the windows indexing service- if it can be queried.

Is there and advice.

Best regards,
Duncan Falconer

Reply to this Message

Don Guillett - 25 Sep 2007 14:35 GMT

How about using DIR to make a list of the files on a worksheet

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 20 lines]
> Best regards,
> Duncan Falconer

Reply to this Message

Dfalconx - 25 Sep 2007 15:00 GMT

Cheers Don,

but how do I do that?

Duncan

> How about using DIR to make a list of the files on a worksheet
>
[quoted text clipped - 22 lines]
> > Best regards,
> > Duncan Falconer

Reply to this Message

Don Guillett - 25 Sep 2007 15:13 GMT

Sub GetFileList()
Dim ThePath As String
Dim fname As String
Dim i As Long
ThePath = "c:\A" 'ThisWorkbook.Path
fname = Dir(ThePath & "\*.doc")
i = 1
Do While fname <> ""
On Error Resume Next
'Workbooks.Open Filename:=fname
'MsgBox fname
Cells(i, 1) = fname
On Error GoTo 0
fname = Dir()
i = i + 1
Loop
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Cheers Don,
>
[quoted text clipped - 35 lines]
>> > Best regards,
>> > Duncan Falconer

Reply to this Message

Dfalconx - 25 Sep 2007 15:56 GMT

Hi Don,

the script works a charm. I've never used one before.

Regards

> Sub GetFileList()
> Dim ThePath As String
[quoted text clipped - 53 lines]
> >> > Best regards,
> >> > Duncan Falconer

Reply to this Message

Rate this thread: