troubleshooting Question

Don't understand how this code works

Avatar of John Carney
John CarneyFlag for United States of America asked on
VB ScriptVBA
11 Comments3 Solutions61 ViewsLast Modified:
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.

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
MsgBox "Your files have been copied from " & sourcePath & " to " & destinationPath

End Sub

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 = "\\\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
Join our community to see this answer!
Unlock 3 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros