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?
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.
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.
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.