Counting specifc extensions in a folder

[UnsolvedCoding]

Hey all - this is something of a new one for me.

It was requested that I hook in a few changes to an existing app. Basically they want to use Excel to open a folder, check if a MDB is present (Source folder) move it to a different folder (Destination folder). The MDB name may exist in the destination folder already at which point the MDB in the destination folder needs to be renamed.

I have found several ways to count folders in files - example

Sub Demo()

MsgBox FileCountA("C:\Test\")

MsgBox FileCountB("C:\Code Library\VBA Code\")

End Sub

Function FileCountA(Path As String) As Long
Dim strTemp As String
Dim lngCount As Long

strTemp = Dir(Path & "*.*")
Do While strTemp <> ""
lngCount = lngCount + 1
strTemp = Dir
Loop

FileCountA = lngCount

End Function


Function FileCountB(Path As String) As Long

Dim objFSO As Object

Set objFSO = CreateObject("Scripting.FileSystemObject").GetFolder(Path)
FileCountB = objFSO.Files.Count

Set objFSO = Nothing

End Function


And there are several ways to count files ending in specific extensions. However I need to capture the names of the MDB's in an array, rename to duplicate and then copy the original from the source folder to the destination folder.

Ideas?

 

[SkipVought]

hi,

Check out the Name Statement in VBA HELP...

Name OldName As NewName    ' Move and rename file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I need to capture the names of the MDB's in an array

Option Explicit[blue]
Dim aryMDBFiles() As String[/blue]

Private Sub Command1_Click()
Dim i As Integer

MsgBox FileCountA("C:\Test\")
[blue]
For i = 0 To UBound(aryMDBFiles)
    Debug.Print aryMDBFiles(i)
Next i[/blue]

End Sub

Function FileCountA(Path As String) As Long
    Dim strTemp As String
    Dim lngCount As Long
    
[blue]    ReDim aryMDBFiles(0)[/blue]
    
    strTemp = Dir(Path & "*.*")
    Do While strTemp <> ""[blue]
        If Right(strTemp, 4) = ".MDB" Then[/blue]
            lngCount = lngCount + 1[blue]
            ReDim Preserve aryMDBFiles(UBound(aryMDBFiles) + 1)
            aryMDBFiles(UBound(aryMDBFiles)) = strTemp
        End If[/blue]
        strTemp = Dir
    Loop
     
    FileCountA = lngCount[blue] - 1[/blue]
End Function

The very first element in an array aryMDBFiles is empty.

Have fun.

---- Andy

 

[UnsolvedCoding]

Skip - Name OldName As NewName moved the file but didn't leave a copy in the original location. Is there a way around this?

Andrzejek - Thanks

 

[SkipVought]

check if a MDB is present (Source folder) move it to a different folder (Destination folder).

Where is the " leave a copy in the original location" part?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

From OP:

copy the original from the source folder to the destination folder.

In my book, 'copy' is a copy - make a copy in other location, leave the original alone where it is. 'Move' - on the other hand is - well.... move it, which is: take it where it is and move it where I want it to be.



Have fun.

---- Andy

 

[SkipVought]

I guess I never got to the bottom of the OP, where he/she contradicts the original statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Skip, you are right (as usual... )

check if a MDB is present (Source folder) [blue]move it[/blue] to a different folder (Destination folder).

and then...

[blue]copy [/blue]the original from the source folder to the destination folder.

Have fun.

---- Andy

 

[UnsolvedCoding]

Hey all - I was able to get around it by writing a sub to rename the offender and then moving the MDB to the destination file after the rename.

' Rename the database
Name sFolderName & "\" & sFileNameNew As sFolderName & "\" & SFilename2

' Copy the original database to the destination file
ofs.CopyFile sFolderName & "\" & sFileNameNew, dFolderName & "\" & sFileNameNew, False