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

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

Cheers Don,

but how do I do that?

Duncan

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

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

 

[Guest]

Hi Don,

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

Regards