Periodically listing files in a folder

Hi

I have a folder containing photos which are updated on a daily basis (added to,replaced and removed.) I'm trying to set up a tracking system to monitor the folder contents.

Can anyone point me in the right direction to listing the contents of a folder using VBA?

Ideally I'd like to have the code associted with a button which updates the file listing completely within excel.

I currently generate a txt file from a dos prompt, link to this and update whenever neccesary.

Cheers
Tom

Hi Tom,

The code to achieve this is:
Sub dirList()
Dim MyFile, MyPath, MyName

Range("A1").Select

MyPath = "c:\"
MyName = Dir(MyPath)
Do While MyName <> ""
If MyName <> "." And MyName <> ".." Then
ActiveCell.Value = MyName
ActiveCell.Offset(1, 0).Activate
End If
MyName = Dir
Loop

end sub

To get a button to launch it in Excel right click on the button bar and
select customise. Then, in the Toolbar tab click Add. Then go to Commands
tab, select Macros from the Categories box, and drag 'Custom Button' to your
new menu.

The last step is to right-click on your new button, select 'Assign Macro'
and point it to the new macro that you have just created.

Michael

"haven104" wrote:

>
> Hi
>
> I have a folder containing photos which are updated on a daily basis
> (added to,replaced and removed.) I'm trying to set up a tracking
> system to monitor the folder contents.
>
> Can anyone point me in the right direction to listing the contents of a
> folder using VBA?
>
> Ideally I'd like to have the code associted with a button which updates
> the file listing completely within excel.
>
> I currently generate a txt file from a dos prompt, link to this and
> update whenever neccesary.
>
> Cheers
> Tom
>
>
> --
> haven104
> ------------------------------------------------------------------------
> haven104's Profile: http://www.excelforum.com/member.php...o&userid=30254
> View this thread: http://www.excelforum.com/showthread...hreadid=499610
>
>

Tom,

For the Word-macro below (I saw it long ago in a Word news letter) you do
not need to enter the path (very convenient!).

Put this macro in Word and execute it. In the appearing "open" dialog box
just select your directory and highlight (click on) any file in it. Click on
the "open"-button (or OK-button, I do not know its name in the English
version) to open the file. It will not open, but instantly you will get a
Word document with lists the names of all files in that directory, topped by
the full path of your directory.

You can use it for any directory that you select (Excel, Word, C, D etc.)
without knowing or entering its full path. The result is a neat Word
document. Try it, it works like a charm.

Disregard the 'text in Dutch, it says the same as I wrote above.

Jack Sons
The Netherlands

----------------------------------------------------------------------------------------------

Sub Content_Directory()

' deze macro maakt het mogelijk een directory uit te printen doordat
' er een lijst in een Worddocument van wordt gemaakt
' als deze sub wordt uitgevoerd verschijnt de "normale" Open-dialogbox.
' Daarin moet een willekerig bestand worden aangeklikt (wordt dus blauw
' gehighlighted, en als dan op de knop "Openen" wordt geklikt, zal een
' nieuw Worddocument ontstaan - bij voorbeeld "document 1" - dat de
' complete list met bestanden bevat, met bovenaan het volledige pad
' naar de directory.

Dim PathWanted As String
Dim Temp As String
Dim i As Integer

With Dialogs(wdDialogFileOpen)
.Name = "*.*"
If .Display = -1 Then
'Documents.Add

PathWanted = Options.DefaultFilePath(wdDocumentsPath)
Set newdoc = Documents.Add
With newdoc
.Content.Font.Name = "Times New Roman"
.SaveAs FileName:=PathWanted
End With

Selection.TypeText "Files in " & PathWanted & ":" & vbCrLf
With Application.FileSearch
.LookIn = PathWanted
.FileName = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Temp = .FoundFiles(i)
While InStr(Temp, "\") > 0
Temp = Mid(Temp, InStr(Temp, "\") + 1)
Wend
Selection.TypeText Temp & vbCrLf
Next
End If
End With
End If
End With
End Sub

----------------------------------------------------------------------------------------------

"haven104" <[email protected]> schreef
in bericht news:[email protected]...
>
> Hi
>
> I have a folder containing photos which are updated on a daily basis
> (added to,replaced and removed.) I'm trying to set up a tracking
> system to monitor the folder contents.
>
> Can anyone point me in the right direction to listing the contents of a
> folder using VBA?
>
> Ideally I'd like to have the code associted with a button which updates
> the file listing completely within excel.
>
> I currently generate a txt file from a dos prompt, link to this and
> update whenever neccesary.
>
> Cheers
> Tom
>
>
> --
> haven104
> ------------------------------------------------------------------------
> haven104's Profile:
> http://www.excelforum.com/member.php...o&userid=30254
> View this thread: http://www.excelforum.com/showthread...hreadid=499610
>