Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro events
Hi
At present I am running a macro which list the files in a specified directory on a worksheet using the following events, Sub Worksheet_Activate() Sub Worksheet_SelectionChange(ByVal Target As Range). Can I run the macro whenever a file is saved to this directory? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro events
Hi
Better use an UDF Here are 3 UDF's, combining which allows you to list all files of certain type in folder, the workbook is saved in, and/or in all of it's subfolders Public Function GetSubfolder(MyFolder As String, FolderNum As Integer, Optional MyTime As Date) Dim fs, f, f1, s, sf Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set sf = f.SubFolders i = 0 For Each f1 In sf i = i + 1 If i = FolderNum Then GetSubfolder = f1.Name Next End Function Public Function GetFileName(MyFolder As String, FileNum As Integer, MyExtension As String, Optional MyTime As Date) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set fc = f.Files i = 0 For Each f1 In fc If Right(f1.Name, 3) = MyExtension Then i = i + 1 If i = FileNum Then GetFileName = f1.Name End If Next End Function Public Function GetThisFolder(Optional MyTime As Date) GetThisFolder = ThisWorkbook.Path End Function -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nc" wrote in message ... Hi At present I am running a macro which list the files in a specified directory on a worksheet using the following events, Sub Worksheet_Activate() Sub Worksheet_SelectionChange(ByVal Target As Range). Can I run the macro whenever a file is saved to this directory? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Editing a simple macro | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) |