You are here:

Excel/A follow up to my last question

Advertisement


Question
QUESTION: Hey, Tom, this is the first time I've seen a picture of you. We look somewhat alike with beard and mustache very much the same, except I've got a lot of gray-white hair hanging down the back of my neck for no good reason except my wife likes the assembled hair piece.
Anyway I wanted to ask you a new question, which I think you can probably answer rather quickly with a bit of macro magic beyond my current kindergarten skill level. Somehow if one doesn't stay involved with VBA, even at an early level, one comes back to it with fear in one's heart.
Anyway as you probably remember I've been rather involved with music searches.
At one step along the way, I've generated a list of the specific numbered Excel files that I need to copy from their current folder, call it Master Excel Files. They will each be listed in Column A starting at line A4 by their title, which will be simply a number. The numbers will all be sorted in ascending order. I would like the macro to look at each number in the list and then go to the "Master Excel Files" folder and find the Excel file with the same title number, then copy the file and paste it into an empty folder titled "Hits."
It would repeat this procedure until it reaches the bottom of the list in Column A and its work will be done.

Up to now I've done this copy-paste by hand, I knew it was stupid and a little bit dangerous since I could make a mistake with such a boring routine and maybe accidentally delete items from the master file without knowing I did it.

Anyway it would make my life much happier...to get rid of this endless routine when I have a 100 or 200 files to copy and paste into my HITS folder.

As always, with much thanks. Still please tell me if there is anything I can do to possibly add some happiness into your life if that's possible. You've certainly done that for me.

Cheers,
  Barry

ANSWER: Hello Barry,


This worked for me:


Change sPathMas to be equal to your directory containing the source files
Change sPathHits to be equl to your directory were you want the files copied.

Sub copyFiles()
Dim sPathMas As String
Dim sPathHits As String
Dim r As Range, cell As Range
sPathMas = "C:\Folder1\Master Excel Files\"
sPathHits = "C:\Folder1\Master Excel Files\HITS\"

Set r = Range("A4", Range("A4").End(xlDown))
If r.Count > 10000 Then
 Set r = Range("A4").Value
 If Len(Trim(r)) = 0 Then
   Exit Sub
 End If
End If
For Each cell In r
If Len(cell.Value) > 0 Then
  FileCopy sPathMas & cell.Value & ".xls", sPathHits & cell.Value & ".xls"
End If
Next
End Sub

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Hi Tom,
Your answer to me worked perfectly. I did discover one change I would like you to make. The macro was starting to throw up an error and I quickly discovered why. I forgot there will be a few Excel files that are on the list to copy, but have been lost. So the coding will need a line or two to say if the requested file is not there, the marco should go on to the next requested file in the list and copy it.

Here is the macro as I set it up and shows where the error hits (of course you would know where without my help, but I thought you might want to see how I have it.

Sub GetAllExcelHits()
Dim sPathMas As String
Dim sPathHits As String
Dim r As Range, cell As Range
sPathMas = "E:AAA_SaveAA OTO DB Excel Master-All\"
sPathHits = "E:AAA_SaveHITS\"
Set r = Range("A4", Range("A4").End(xlDown))
If r.Count > 10000 Then
Set r = Range("A4").Value
If Len(Trim(r)) = 0 Then
  Exit Sub
End If
End If
For Each cell In r
If Len(cell.Value) > 0 Then
 FileCopy sPathMas & cell.Value & ".xls", sPathHits & cell.Value & ".xls"
End If
Next
End Sub

(The line following after "If Len" is highlighted in yellow as a "breakpoint.")

By the way, it's been so long I've forgotten the best way to remove the breakpoint.

One other question, I would like to follow this macro with another that I've used for sometime to continue the processing of the macro files in the HITS folder. Can I just add that rather important macro immediately after this one and have it start on its own once the last Excel file has been copied into the HITS? folder?

Of course, thanks again, Tom, you certainly must have helped a great many people such as I over the years.

Cheers,
   Barry

Answer
Barry,


Sub GetAllExcelHits()
Dim sPathMas As String
Dim sPathHits As String
Dim r As Range, cell As Range
sPathMas = "E:AAA_SaveAA OTO DB Excel Master-All\"
sPathHits = "E:AAA_SaveHITS\"
Set r = Range("A4", Range("A4").End(xlDown))
If r.Count > 10000 Then
Set r = Range("A4").Value
If Len(Trim(r)) = 0 Then
  Exit Sub
End If
End If
For Each cell In r
If Len(cell.Value) > 0 Then
 sName = Dir(sPathMas & cell.value & ".xls")
 if sName <> "" then
 FileCopy sPathMas & cell.Value & ".xls", sPathHits & cell.Value & ".xls"
 End if
End If
Next
' add the name of your macro here
End Sub

should account for a missing file or files.

I think your path lines should look like this
sPathMas = "E:\AAA_SaveAA OTO DB Excel Master-All\"
sPathHits = "E:\AAA_SaveHITS\"

if you want to call another macro, I have indicated where you would at the name of the macro.  Putting in the name of the macro causes it to be executed when that line is reached.

unless you put in a breakpoint, then the yellow highlight is just how Excel VBA indicates where the error occurred.  It isn't a breakpoint.  After an error and the fix is made, you need to reset the Visual basic editor by clicking the reset button which it two buttons to the right of the run  button on the visual basic editor menu.  (or click on the run menu and click reset under that menu).  

If you still think you have a breakpoint, under the debug menu there is an option to clear all breakpoints.

Hope that clears up the problem.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.