Notification of a new file in a folder?

[MOB]

Hi

I have a summary spreadsheet that links to all the spreadsheets in a certain folder to summarise the data.

Is there any way for excel to notify if there are any new files in that folder that dont appear on the summary spreadsheet? ie if someone has created a new file.

At the moment I have to periodically check the folder for new files and add if necessary.

Thanks

 

[Jaafar Tribak]

if you are wanting to be notified automatically as the file is being added to the folder , I think it is going to be difficult. I still remember trying to do this but failed.

Otherwise, you could for example save the file names in the registry upon closing the workbook so you can subsequently retrieve and check them when you reopen the workbook in a different session.

Regards.

 

[Ivan F Moala]

Have a look here on how to do this....


http://www.dailydoseofexcel.com/archives/2006/07/07/monitor-directory/

 

[MOB]

Thanks for that link - I'm not an IT person so I'm going to get some assistance with this - but it looks like its what I need.

 

[Ivan F Moala]

If you can explain a bit more on what you are doing I may be able to modify it for you.

 

[erik.van.geit]

Hi,

this would be my contribution

Option Explicit

Sub check_for_new_files()
'Erik Van Geit
'060922
'Current list in column 1, path + filetype in first cell
'examples:
'C:\windows\desktop\*.*
'C:\windows\desktop\*.txt
'C:\windows\desktop\*name*.xls
'C:\windows\desktop\name.*
'All found files which are not in column 1, will be listed in column 2

Dim file_name As String
Dim foundName As Range

'**** EDIT ****
Const FilesCol = 1
Const NewFilesCol = 2
'**** END EDIT ****

'Cells(1, FilesCol) has Path
file_name = Dir(Cells(1, FilesCol))

    Do Until file_name = ""
    Set foundName = Nothing
    Set foundName = Columns(FilesCol).Find(what:=file_name, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=vbNo)
        If foundName Is Nothing Then
        Cells(Rows.Count, NewFilesCol).End(xlUp)(2) = file_name
        End If
    file_name = Dir
    Loop

End Sub

  A                         B                            
1 C:\bus\window\new*.txt                                 
2 new instance of Excel.txt newwindow for each sheet.txt 
3 newwindow.txt                                          

filelist
[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[MOB]

If you can explain a bit more on what you are doing I may be able to modify it for you.

I have a folder S:\finance which contains say 10 spreadsheets

I need to know somehow when any of these spreadsheets are deleted, or if new spreadsheets are added.

Thanks for your help!