PDA

View Full Version : Counter sums quantity of files in said folder but doesnt update unless double clicked



BigError
10-15-2017, 02:32 AM
Option Compare Text
Option Explicit

Function CountFiles(Directory As String) As Double
'Function purpose: To count all files in a directory
Dim fso As Object, _
objFiles As Object

'Create objects to get a count of files in the directory
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set objFiles = fso.GetFolder(Directory).Files
If Err.Number <> 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If
On Error GoTo 0
End Function


Sub TestCount()
'To demonstrate the use of the CountFiles function
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.folderexists(Selection) Then
MsgBox "I found " & CountFiles(Selection) & " files in " & Selection, _
vbOKOnly + vbInformation, CountFiles(Selection) & " files found!"
Else
MsgBox "Sorry, but I can't find the folder: " & vbCrLf & Selection _
& vbCrLf & "Please select a cell that has a valid" & vbCrLf & _
"folder name in it!", vbOKOnly + vbCritical, "Error!"
End If

End Sub




How do I get this to refresh or auto update?

Also I have no Idea what I'm doing as this script was pinched from another user of this fine website and community!

paulked
10-15-2017, 07:12 AM
Hi BigError! Welcome to the forum.

When do you want it to update/refresh?

SamT
10-15-2017, 08:02 AM
Many ways. One way is to use "CountFiles" as an Equation Function in a cell

=CountFiles("C:\MyFolder")
Alternatively, use any WorkSheet or Workbook Event Sub. (Selection_Change, Sheet_Activate, Workbook_Open, etc)

BTW, I added Code Formatting Tags to you post. Please use the # icon when adding Code blocks to a post,

BigError
10-15-2017, 07:52 PM
Thank you for the recommendation and editing of the post. Looks much cleaner!

BigError
10-15-2017, 07:53 PM
Hi BigError! Welcome to the forum.

When do you want it to update/refresh?

Well I'm open to any suggestions, I don't have much experience so I'm more wondering what options I have! Thank you very much for your assistance.

SamT
10-15-2017, 08:03 PM
Well I'm open to any suggestions,
Open Excel... Right Click on a worksheet Tab and choose "View Code."

Above the Code page, thee are two DropDowns, General and Declarations.

Drop down the General and select Worksheet. Then...

Drop down the Declarations and look at all the Events that occur on a worksheet. Pick one to be the Trigger of your code.

BigError
10-16-2017, 01:00 AM
Many ways. One way is to use "CountFiles" as an Equation Function in a cell

=CountFiles("C:\MyFolder")
Alternatively, use any WorkSheet or Workbook Event Sub. (Selection_Change, Sheet_Activate, Workbook_Open, etc)

BTW, I added Code Formatting Tags to you post. Please use the # icon when adding Code blocks to a post,

I used the equation to originally get the cell to operate as I want it but now its refreshing that information, the issue is it will certainly count the files in said folder but after it counts, it wont update after more files are added to the folder unless double clicked to show the code and exited. How can I get it to update as files are added without having to double click the code as there will be many many cells that need updating as files are being added to many different folders that need to be counted.

Sorry if my jargon is off! I really appreciate the help

BigError
10-16-2017, 01:02 AM
Open Excel... Right Click on a worksheet Tab and choose "View Code."

Above the Code page, thee are two DropDowns, General and Declarations.

Drop down the General and select Worksheet. Then...

Drop down the Declarations and look at all the Events that occur on a worksheet. Pick one to be the Trigger of your code.

I followed your instructions and chose




Private Sub Workbook_Open()


End Sub


Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub


It doesnt really do anything though. sorry man... Im super new to this.

BigError
10-16-2017, 01:58 AM
*Update*
I'm trying to refresh the number shown from my custom function. it works as I need it but once it calculates and something is added or removed from the folder the cell does not update. I have to find a round about way to get it to update where I would like if it was set on a timer to check, or even update anytime the worksheet is opened.

Writing this again for clarity of the question. I have no preference towards how this happens and am unsure of the options I have as I'm new to the Excel programming community.

SamT
10-16-2017, 07:24 AM
Add this line a the top of your UDF (User Defined Function, AKA Custom Function)

Application.Volatile That tells Excel to calculate it every time the sheet is calculated


something is added or removed from the folder That action may not touch Excel, so Excel has no way to know. Are you adding-removing files from that folder using Excel? If yes, then add the Range Calculate Method below to the bottom of that sub. Add the sheet name to the Range. Sheet?.Range(?).Calculate

These two, along with the Volatile method above, will trigger it most often

Private Sub Worksheet_Activate() 'Sheet open event
Range("A1").Calculate 'Edit to refer to the range the UDF is in
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '
Range("A1").Calculate
End Sub

BigError
10-16-2017, 02:32 PM
Add this line a the top of your UDF (User Defined Function, AKA Custom Function)

Application.Volatile That tells Excel to calculate it every time the sheet is calculated

That action may not touch Excel, so Excel has no way to know. Are you adding-removing files from that folder using Excel? If yes, then add the Range Calculate Method below to the bottom of that sub. Add the sheet name to the Range. Sheet?.Range(?).Calculate

These two, along with the Volatile method above, will trigger it most often

Private Sub Worksheet_Activate() 'Sheet open event
Range("A1").Calculate 'Edit to refer to the range the UDF is in
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '
Range("A1").Calculate
End Sub



Perfect! Thank you very much for your assistance Sam!