No of Files Count (1 Viewer)

sathsaj

Registered User.
Local time
Today, 13:36
Joined
Sep 19, 2017
Messages
24
Dim count As Integer

StrFile = Dir("C:\Testing\TEST*.csv")
Debug.Print "File name is :" & StrFile
Debug.Print Len(StrFile)
Path = "C:\Testing\"
Debug.Print Dir(Path)
count = 0
Do While StrFile <> ""
count = count + 1
StrFile = Dir("C:\Testing\TEST*.csv")
Loop

Debug.Print "No of Files available --> " & count

If count > 1 Then
MsgBox ("Multiple files found in folder,please delete and keep current date file")
End
End If


If there is one file in the folder, i want the vba code to run and when there are two files I need the vba count to fail
there is some error on count = 0. Any idea how to resolve.
 

moke123

AWF VIP
Local time
Today, 16:36
Joined
Jan 11, 2013
Messages
3,940
Count is a reserved word so you may want to use a different variable name.

I prefer fso. Here's a function for file count

Code:
Public Function fGetFileCount(FolPath As String) As Integer
     Dim fso As Object
     Dim fol As Object

     Set fso = CreateObject("Scripting.FileSystemObject")

     Set fol = fso.getfolder(FolPath)

          fGetFileCount = fol.files.Count

End Function

Then you could use a select case statement to do what you want

Code:
select case  fGetFileCount(" your folder path goes here")

case 0
     'Do Something
case 1
     'Do Something
case is >1
     'Do Something
case else
     'Do Something

end select
 

tvanstiphout

Active member
Local time
Today, 13:36
Joined
Jan 22, 2016
Messages
260
Why are you not programmatically deleting the older file for them? See the Kill statement.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,246
just use Dir() on your second call:
Code:
Dim count As Integer

StrFile = Dir("C:\Testing\TEST*.csv")
Debug.Print "File name is :" & StrFile
Debug.Print Len(StrFile)
Path = "C:\Testing\"
'Debug.Print Dir(Path)
'use StrFile not the path
StrFile = Dir$(StrFile)
count = 0
Do While StrFile <> ""
count = count + 1
StrFile = Dir$()
Loop

Debug.Print "No of Files available --> " & count

If count > 1 Then
MsgBox ("Multiple files found in folder,please delete and keep current date file")
End
End If
 

ebs17

Well-known member
Local time
Today, 22:36
Joined
Feb 7, 2020
Messages
1,991
Code:
Sub DoIt()
    Dim sPath As String
    Dim sFile As String
    Dim lCount As Long

    sPath = "C:\Testing\"             
    sFile = Dir(sPath & "TEST*.csv")                     
    Do While sFile > vbNullString         
        'Debug.Print sFile, sPath & sFile                         
        lCount = lCount + 1

        ' somehow use the file
    
        ' if necessary, delete the file (VBA.Kill) or move the file to an archive (VBA.Name)

        sFile = Dir                                 
    Loop
    Debug.Print lCount
End Sub
 
Last edited:

Mike Krailo

Well-known member
Local time
Today, 16:36
Joined
Mar 28, 2020
Messages
1,044
ebs17, wouldn't you have to initialize the lCount variable to zero first?
 

ebs17

Well-known member
Local time
Today, 22:36
Joined
Feb 7, 2020
Messages
1,991
Code:
Sub LookHere()
    Dim lCount As Long
    Debug.Print "lCount = " & lCount
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 13:36
Joined
Mar 14, 2017
Messages
8,907
It defaults to zero I think.

I was thinking of suggesting FSO solutions too, but the OP seems to be required to count CSV's specifically, and I most definitely cannot claim that an FSO-based approach looping through and looking at filenames would be faster than a creative use of DIR, which is near instantaneous, hence I did not suggest
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:36
Joined
Sep 21, 2011
Messages
14,472
It defaults to zero I think.

I was thinking of suggesting FSO solutions too, but the OP seems to be required to count CSV's specifically, and I most definitely cannot claim that an FSO-based approach looping through and looking at filenames would be faster than a creative use of DIR, which is near instantaneous, hence I did not suggest
I thought each type were initialised?, so a string variable would be ZLS, a date as 0 ?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 28, 2001
Messages
27,332
To @Mike Krailo and @Gasman - since you expressed some uncertainty, I thought I would look it up and post it for you.

Specifically when a program variable comes into existence, it is initialized to a default value. This default is, of course, type dependent. My answer was derived from the Microsoft VBA Language Specification v20140424, section 2.3, "Variables".

When a variable is created, it is initialized to a default value. The default value of a variable is determined by the declared type of the variable

A big table follows that statement in the document. Here is the summary of defaults:

  • Boolean: FALSE (represented by all 0 bits in the variable.)
  • All integer-like variables: 0 (all 0 bits in the variable, from BYTE to LONGLONG.)
  • All scientific variables: +0.0
  • Date: 30 Dec 1899 00:00:00, also known as the EPOCH, which happens to be represented by a TYPECAST of +0.0 in DOUBLE format.
  • String: An EMPTY string.
  • Variant: EMPTY
  • String*n: A pre-allocated string of length n, each byte filled with NULL in the implemented representation, usually UNICODE U+0000
  • Object: NOTHING
The table also lists array data types that mention creating the array of the indicated size based on its dimensions and with each element of the array defined according to the array's base type.

Section 2.1, "Data Values and Data Types", describes the range of each data type and makes note of special cases.

  • For BOOLEAN, TRUE = -1 and FALSE = 0. We probably knew that. But there are a couple of special cases that might be useful to know.
  • For integers, there are no special values. All bit patterns are part of the integer number being represented.
  • For scientific numbers, they use the IEE 754-1985 standard, so there are the usual numbers plus NaN (Not a number), two different signed zeros, and two different signed infinities.
  • Objects: NOTHING is an object pointer pointing to address 0.
  • Strings: A string variable has a separate length indicator so has a positive indication of a zero-length string in something called a string descriptor. Those familiar with assembly language would recognize a "counted string" data sub-type.

The following keyword-values have implementation-specific bit patterns in variables that can display them:
  • EMPTY
  • ERROR
  • NULL
  • MISSING.

The document I have does not specify the patterns for those four values.

For the overly curious, the IEEE standard mentioned above is explained better in this:
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:36
Joined
Sep 21, 2011
Messages
14,472
Thank you Doc.
I was not uncertain, as I have dimmed and used variables many times in the past, and always wondered why I saw people set something to zero or zls.

TBH I was just trying to be polite?
Not my usual response perhaps, but I was replying to a respected experienced posters.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:36
Joined
Feb 19, 2002
Messages
43,513
Old habits die hard. In COBOL, I worked primarily with CICS transactions and therefore my code always needed to be serially reusable. That means that a second transaction might run while the program was in memory and therefore all variables always needed to be reinitialized as the code started. It was not safe to "assume" a fresh load. In Access, that is how some of my forms need to work although not all of them. Therefore, I ALWAYS initialize variables at the start of a procedure so that I never have to think about how I might change the workflow later.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 28, 2001
Messages
27,332
As an addendum, any variable declared in a subroutine header is defined in what is called a Stack Frame. It contains all variables declared locally in the subroutine... EXCEPT for the case of STATIC variables, which can still be associated with some routine but which are NOT part of the dynamic stack frame. They persist in value outside of the context of the routine as long as the class module is still active (form is still open).
 

Users who are viewing this thread

Top Bottom