We help IT Professionals succeed at work.
Get Started

Don't understand how this code works

John Carney
John Carney asked
on
57 Views
Last Modified: 2020-09-28
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

Comment
Watch Question
Mechanical Engineer
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 3 Answers and 11 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE