Avatar of Bushmouse
 asked on

Copy all files from one folder to another in vba

Ok experts, I'm back for more.  Make a long story short I need to copy all files from one folder to another.  I snooped around through the forum, found some code and plugged it into my database; however I keep getting an error:  Bad File for file number when the code executes.  The code is called from a form to a function in a module.  The actual FileCopy command line is highlighted when I click debug on the error.  All of the files are copied to the new directory like they should.  The hover text shows that strFullPath is = to ""  when the error appears.  I am thinking the following line should prevent that from happening:  Do While Len(strFullPath) > 0 ; however I still get the error.  Thanks in advance.

Form Code (seems to work fine)

Private Sub cmdFunctionExecute_Click()
    Dim MyParameter As String
    Dim blEval As Boolean
    Dim strEval As String
    Dim ClassID As Long
    ClassID = Me!ClassID
    MyParameter = Me!Function
    strEval = MyParameter & "(" & ClassID & ")"
    blEval = Eval(strEval)  
End Sub

Module Code (here where the error is"
Function CopyFiles(ClassID As Long) As Boolean

    Dim rsCurrentTT101Date As New ADODB.Recordset
    Dim strSQL
    Dim strMsg As String
    Dim CurrentTT101Date As Date
    Dim CurrentTT101Month As String
    Dim CurrentTT101Year As String
    Dim PreviousTT101Date As Date
    Dim PreviousTT101Month As String
    Dim PreviousTT101Year As String
    Dim CurrentDir As String
    Dim PreviousDir As String
    Dim strFullPath As String
    Dim strFileName As String
    Dim SourceFolder As String
    Dim DestinationFolder As String
    Dim FileExtension As String
    If Not IsNull(ClassID) Then
         strSQL = "SELECT * FROM qryClassAscending"
         rsCurrentTT101Date.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
         rsCurrentTT101Date.Find "ClassID = " & ClassID, , adSearchForward
         CurrentTT101Date = rsCurrentTT101Date![StartDate]
         PreviousTT101Date = rsCurrentTT101Date![StartDate]
         'Set Current Month and Date
         CurrentTT101Month = Format(CurrentTT101Date, "mmm")
         CurrentTT101Year = Year(PreviousTT101Date)
         'Set Previous Month and Date
         PreviousTT101Month = Format(PreviousTT101Date, "mmm")
         PreviousTT101Year = Year(PreviousTT101Date)
         CurrentDir = "Z:\ALL\TT101\" & CurrentTT101Month & " " & CurrentTT101Year & "\"
         PreviousDir = "Z:\ALL\TT101\" & PreviousTT101Month & " " & PreviousTT101Year & "\"
    End If

    SourceFolder = PreviousDir
    DestinationFolder = CurrentDir
    'make sure the trailing "\" exists
    If Right$(SourceFolder, 1) <> "\" Then
        SourceFolder = SourceFolder & "\"
    End If

    strFullPath = Dir$(SourceFolder)
        Do While Len(strFullPath) > 0
                strFullPath = Dir$
                strFileName = Mid$(strFullPath, InStrRev(strFullPath, "\") + 1)
                FileCopy SourceFolder & strFullPath, DestinationFolder & strFileName

        strMsg = MsgBox("From:  " & PreviousDir & Chr$(13) + Chr$(10) & "To:  " & CurrentDir, vbOKOnly, "Files Copied From")
 Set rsCurrentTT101Date = Nothing
         strSQL = ""
         strMsg = ""
         CurrentTT101Date = 0
         CurrentTT101Month = ""
         CurrentTT101Year = ""
         PreviousTT101Date = 0
         PreviousTT101Month = ""
         PreviousTT101Year = ""
         PreviousDir = ""
         CurrentDir = ""
         SourceFolder = ""
         DestinationFolder = ""
         strFullPath = ""
         strFileName = ""

End Function
Microsoft Access

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon

If no ClassID u dont do anything inside that IF
then u do this

SourceFolder = PreviousDir
    DestinationFolder = CurrentDir

But PreviousDir and CurrentDir are not set to anything
what should it be set to ?

If Not IsNull(ClassID) Then
         strSQL = "SELECT * FROM qryClassAscending"
         .... etc ....
End If
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Again thanks for all your help experts.  Ultimately I went with mathewspatrick, but rockiroads I owe you a few points because your solution did stop the error that was occuring; however it only copied every other file over from some reason.  
Patrick Matthews

Glad to help :)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck