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.fi
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.
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 & "\"
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FolderExists(sourcePath) = False Then
MsgBox sourcePath & " does not exist "
If FSO.FolderExists(destinationPath) = False Then
MsgBox sourcePath & "does not exist"
FSO.CopyFile Source:=sourcePath & fileExtn, Destination:=destinationPath
MsgBox "Your files have been copied from " & sourcePath & " to " & destinationPath
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