No of Files Count (1 Viewer)

[sathsaj]

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]

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

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

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]

Why are you not programmatically deleting the older file for them? See the Kill statement.

 

[arnelgp]

just use Dir() on your second call:

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]

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

 

[Mike Krailo]

ebs17, wouldn't you have to initialize the lCount variable to zero first?

 

[Gasman]

ebs17, wouldn't you have to initialize the lCount variable to zero first?

I believe it is initialised when dimmed?

 

[ebs17]

Sub LookHere()
    Dim lCount As Long
    Debug.Print "lCount = " & lCount
End Sub

 

[Isaac]

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

 

[Mike Krailo]

It defaults to zero I think.

Didn't know that.

 

[Gasman]

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]

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:

https://en.wikipedia.org/wiki/IEEE_754-1985https://en.wikipedia.org/wiki/IEEE_754-1985

 

[Gasman]

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]

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]

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).