Refresh a list

[manc]

Good morning,

I have the following code sitting behind the scenes...

Private Sub Workbook_Open()

Dim strFolderName   As String
Dim strFileType     As String
Dim pasteRange      As Range
Dim returnVals      As Variant

'// set parameters, change as required
strFolderName = "C:\Customer_Templates\"
strFileType = "*.xlsx"
Set pasteRange = Worksheets("Sheet1").Range("A1")

'// retrieve output of DIR command from CMD.exe
returnVals = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & strFolderName & _
                strFileType & """ /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

'// Display results in chosen column
pasteRange.Resize(UBound(returnVals) + 1, 1).Value = WorksheetFunction.Transpose(returnVals)
End Sub

Works great, except if a file is deleted from "C:\Customer_Templates".
I can add files to the folder, and column A updates, but if I delete a file, column A still displays the deleted filename.
I need it work so that if a file is deleted from "C:\Customer_Templates", it deletes the filename from the list also.

Any ideas or suggestions greatly appreciated.

N.B. Please ignore the smiley face. It should read (without spaces) / A : - D

Best regards
manc

 

[Mister H]

Hello manc

The code seems to work for me when I run it manually. It does not pick up the deleted files at my end. I am thinking that maybe once the file is run that possibly it is saved with ALL files listed in column A. You go in and delete files from your folder then open the file again and the macro runs but just overwrites the info. So if you started with 10 files listed they are all still listed when you open the file... Blah blah blah Lol


Does it work for you if you clear the contents of column A FIRST. Something like this...

Private Sub Workbook_Open()
Dim strFolderName   As String
Dim strFileType     As String
Dim pasteRange      As Range
Dim returnVals      As Variant

'CLEAR Column A and then acquire NEW List of files
Columns("A:A").Select
 Selection.Clear
 Range("A1").Select
 
 
 
 
 '// set parameters, change as required
strFolderName = "C:\Customer_Templates\"
strFileType = "*.xlsx"
Set pasteRange = Worksheets("Sheet1").Range("A1")
'// retrieve output of DIR command from CMD.exe
returnVals = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & strFolderName & _
                strFileType & """ /B /A:-D").StdOut.ReadAll, vbCrLf), ".")
'// Display results in chosen column
pasteRange.Resize(UBound(returnVals) + 1, 1).Value = WorksheetFunction.Transpose(returnVals)
End Sub

Hopefully that will resolve your issue. If not write back

Good Luck,
Mark

 

[manc]

Hi Mark,

Thanks for your reply.

Unfortunately, I cannot delete column A, as this is our history of all previous jobs. If i delete it, then we have no record of what we have done.

I just tried it again - the Customer Templates folder contains four different Sheets - BookA, BookB, BookC, BookD.
I made a copy of BookD and renamed it BookZ

I opened up the workbook, clicked on the drop-down list in cell A1, and BookZ appears with the others.
Exited the workbook, deleted BookZ from the Customer Template folder, and reopened the workbook.
Now in the drop-down list, there are two rows called BookD!

Something not right!

Best regards
manc