Avatar of John Carney
John Carney
Flag for United States of America asked on

Don't understand how this code works

I have a problem brewing at work. A new hire is formally educated in Excel and I am not. She asked me for help with a macro that wasn't working for her and she came to me as I am considered to be one of two experts here. The problem is that my brain is virtually incapable of learning anything on my own from manuals, I can only learn directly from other people. I've accumulated virtually all of my knowledge by asking questions here and “dumbing it down” to personally intuitive and very effective code. I guess I'm amazed at what I can do without formal training but more than ever now I need to understand how the formal syntaxes actually work.

The project is to find and copy Excel files in a designated folder and paste them into other customer specific folders, sub-folders of another designated folder. I created code for her on Monday which completely solved her problem using my kind of home-made super simple code, but she managed to fix her formal code over Christmas. The crux of my problem is that I have no idea of how her code can do what my code can do. The first code below is her revised code. The second is mine.

My first question is what exactly is an object such that FSO is one in  “Dim FSO As Object.” (Why isn’t it just defined as “Workbook?”) And how does that empower it to serve the objective described in my question above?

Second question: since she doesn’t define the variable names of the source files to be copied, how does her code identify the file to be copied? The source folders have a number of different “xls” files and her code only copies one of them. One disadvantage of her code is that she has hard-coded one of the customers, “AAL”, and has to rename it in the VBA for each of 40+ customers and run it again, whereas my code loops through a list on Sheet1, and copies and pastes the files for all 40+ customers with one click. (For me, using a list on a worksheet is so much more intuitive and easier to modify than using an array in the VB Editor).

Third question: What is accomplished by this line which seems to be key:  "Set FSO = CreateObject("scripting.filesystemobject")"
Also, am I correct that the asterisk in front of "*xls"  returns the entire name of the xls  file?

My code would be even more concise than it is except that it includes the PDF files that need to be copied as well, whereas she has created a separate file for the PDF's.


HER CODE
Private Sub CommandButton1_Click()
Dim FSO As Object

Dim sourcePath As String
Dim destinationPath As String
Dim fileExtn As String

sourcePath = "H:\Depts\css\A_ILS & Reliability\Reliability\Monthly Reliability Reports\ISEDIS Reports\ISEDIS MTBF MTBUR extractions\1 Latest ISEDIS Results\Special Reports\AAL"
destinationPath = "H:\Depts\InFlyt_Informed_Data\LATEST DEVICE REPORTS\AAL-Monthly Report\2019"

fileExtn = "*xls"

If Right(sourcePath, 1) <> "\" Then
sourcePath = sourcePath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(sourcePath) = False Then
MsgBox sourcePath & " does not exist "
Exit Sub
End If

If FSO.FolderExists(destinationPath) = False Then
MsgBox sourcePath & "does not exist"
Exit Sub

End If
FSO.CopyFile Source:=sourcePath & fileExtn, Destination:=destinationPath
copy_files_from_subfolders
MsgBox "Your files have been copied from " & sourcePath & " to " & destinationPath

End Sub

Open in new window


MY CODE
Sub CopyFiles3()
Dim Cstmr As String, SrcPth As String, SourcePath As String, DestPath As String, SrcFile As String, i As Long, _
   Mnth As String, MnthName As String, MnthName2 As String, SrcFilePDF As String
   
SourcePath = "\\us02fshares03.dom1.taus.us.thales\toledo_share\Depts\css\A_ILS & Reliability\Reliability\Monthly Reliability Reports\ISEDIS Reports\ISEDIS MTBF MTBUR extractions\1 Latest ISEDIS Results\Special Reports\"

Mnth = [D2]
MnthName = Mnth & "_" & [E2]
MnthName2 = [MonthNum2] & "_" & [E2]

For i = 2 To [B100].End(xlUp).Row
   Cstmr = Cells(i, 2)
   
   SrcPth = SourcePath & Cstmr & "\" & MnthName & "\"
   DestPath = "\\us02fshares03\toledo_share\Depts\InFlyt_Informed_Data\LATEST DEVICE REPORTS\" & Cstmr & "-Monthly Report\2019\"
   
   SrcFile = MnthName2 & "_2019_" & Cstmr & "_MM_6Mo_Charts.xls"
   FileCopy SrcPth & SrcFile, DestPath & SrcFile
   
   SrcFilePDF = MnthName2 & "_2019_" & Cstmr & "_MM_6Mo_Report.pdf"
   FileCopy SrcPth & SrcFilePDF, DestPath & SrcFilePDF
Next i

End Sub

Open in new window

VB ScriptVBA

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
NVIT

Fwiw... Using the VBA debug breakpoints, adding statements and variables to the watch list, and stepping thru each line by line helps tremendously
ASKER CERTIFIED SOLUTION
byundt

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
or
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
SOLUTION
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.
SOLUTION
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.
John Carney

ASKER
Okay, for starters here's the missing code:
Sub copy_files_from_subfolders()
Dim FSO As Object, fld As Object
Dim FsoFile As Object
Dim FsoFol As Object

sourcePath = "H:\Depts\css\A_ILS & Reliability\Reliability\Monthly Reliability Reports\ISEDIS Reports\ISEDIS MTBF MTBUR extractions\1 Latest ISEDIS Results\Special Reports\AAL"
targetPath = "H:\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\Configs&FlightLegs-Refernc\JC\Deepali_Copy\Destination\AAL"


If Right(sourcePath, 1) <> "\" Then sourcePath = sourcePath & "\"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.GetFolder(sourcePath)
If FSO.FolderExists(fld) Then
For Each FsoFol In FSO.GetFolder(sourcePath).SubFolders
    For Each FsoFile In FsoFol.Files
    If Right(FsoFile, 4) = ".xls" Then
    FsoFile.Copy targetPath
    End If
    Next
    Next
    End If
End Sub

Open in new window

I'm starting to understand this a little better. I misunderstood my colleague because I got the impression that she just wanted one of six xls files typically in the source folder copied and pasted, but it turns out she wanted all of them copied. So now my assumption is that the code is looping through all the subfolders of the sourcePath, and then copying any Excel workbooks it finds in them. But when I run it it only copies the Excel files in the sourcePath itself into the targetPath folder. What am I missing?

Also, since FsoFol and FsoFile are not defined, how does Excel know what they are?

I'll be here at work for another hour (til 3PM PST) and then gone til Monday.

Thanks,
John
byundt

since FsoFol and FsoFile are not defined, how does Excel know what they are?
Actually, VBA does know exactly what they are. They are defined in statements 14 & 15 of the code block you posted.

Statement 14 loops through the subfolders collection returned by the .SubFolders property of the folder returned by the .GetFolder method of the FSO object. The For Each statement means that you repeat the loop, each time with a different subfolder. Each subfolder returned is represented by the FsoFol variable.
For Each FsoFol In FSO.GetFolder(sourcePath).SubFolders

Open in new window


Similarly, statement 15 loops through the files collection returned by the .Files property of FsoFol. Each file returned is represented by the variable FsoFile.
For Each FsoFile In FsoFol.Files

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
byundt

The code only copies an Excel file from the subfolder if it has the .xls file extension. A file won't be copied if it has a .xlsm, .xlsx or .xlsb file extension.

When you were testing the code, did the subfolders contain .xls files or some other file extension?
John Carney

ASKER
Thanks, Brad. I think I'm making myself seem more stupid than I am! I do understand that statement 14 is looping through the subfolders that are one level down. I have adapted her code to copy and paste files that are in two folders ("2018" and "2019") one level down from the master source path. How would i modify line 14 to specify two levels down? In other words I want to get the files in the two folders under folder "2018" and "2019", which are "Jan" and "Feb".

Also, are my modifications remotely in the realm of best practice?
Sub copy_files_from_subfoldersNew()
Dim FSO As Object, fld As Object
Dim FsoFile As Object
Dim FsoFol As Object
Dim annum As String

sourcePath = "\\us02fshares03\toledo_share\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\Configs&FlightLegs-Refernc\JC\FSOFiles\Source\"
targetPathTop = "\\us02fshares03\toledo_share\Depts\css\A_ILS & Reliability\Reliability\1-CURRENT\System Reporting\Configs&FlightLegs-Refernc\JC\FSOFiles\Destination\"

If Right(sourcePath, 1) <> "\" Then sourcePath = sourcePath & "\"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.GetFolder(sourcePath)
If FSO.FolderExists(fld) Then
For Each FsoFol In FSO.GetFolder(sourcePath).SubFolders
annum = FsoFol.Name & "\"
targetPath = targetPathTop & annum
    For Each FsoFile In FsoFol.Files
    If Right(FsoFile, 5) = ".xlsx" Then
    FsoFile.Copy targetPath
    End If
    Next
    Next
    End If
End Sub

Open in new window


Thanks,
John
byundt

John,
I am tempted to suggest that you revise the code to become recursive. In so doing, it keeps searching for subfolders until there aren't any. Or is there a limit on the depth of subfolders that you want to search?

At the risk of making things confusing, I attached a file I built to dive through a set of folders and perform actions like getting data, listing files, etc. There are several different ways of recursing through the directory based on file and folder naming patterns and depth. You choose one of these recursion methods, and then edit its code to replace the default action routine with one of your choosing. In your case, the action routine moves a file.

Brad
RecursiveFileAppender-April-2016.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John Carney

ASKER
Thanks, Brad. I trust I will deduce the meaning of recursive from your file. I'm leaving work in a few minutes and will have a chance to look at your file after New Year's. In the meantime, Happy 2020!

John
byundt

Recursion is when a sub or function calls itself. For example, you might make a sub that copies every file in a folder call itself for each subfolder within that folder.

BTW, is it your intention to copy the 2018 and 2019 folders as well as all the months within those folders and their files over to the target folder? So the target folder will end up with 2018/Jan, 2018/Feb, 2019/Jan and 2019/Feb folders & files?