macro to move from directory to directory

[davegb]

I am writing/recording a macro to make changes in 64 different
spreadsheets in 64 different folders on a network. I am just learning
vba. I don't know how to tell Excel to move through the 64 folders,
test for the spreadsheet to be sure it's there, then make changes. Any
suggestions?
TIA
Dave

 

[Dave Peterson]

I would use a worksheet to help out.

In A2:A65, I'd put the 64 folders to check.
In B1, I'd put the filename to look for.

Then something like this:


Option Explicit
Sub testme2()

Dim myRng As Range
Dim myCell As Range
Dim myFileName As String
Dim TestStr As String
Dim tempWkbk As Workbook

With Worksheets("sheet1")
Set myRng = .Range("a2:A65")
For Each myCell In myRng.Cells
myFileName = myCell.Value
If Right(myFileName, 1) <> "\" Then
myFileName = myFileName & "\"
End If
myFileName = myFileName & .Range("b1").Value

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
myCell.Offset(0, 2).Value = "Missing!"
Else
myCell.Offset(0, 2).ClearContents
Set tempWkbk = Workbooks.Open(Filename:=myFileName)
'do your work
tempWkbk.Close savechanges:=True
End If
Next myCell
End With
End Sub

 

[Dave Peterson]

I assumed that the name of the workbook was the same for all the folders. If
it's not, then you could put the individual names in B2:B65 and change this
line:

myFileName = myFileName & .Range("b1").Value
to:

myFileName = myFileName & mycell.offset(0,1).Value

 

[davegb]

Dave,
Thanks for all your help. I've copied the code for future use. The
macro approach was getting increasing complex as my client kept adding
things they wanted changed in these spreadsheets. I finally realized it
was easier to create a "base" file (like a template, sort of) and just
to a save as to each folder and then change a few text entrys.

 

[davegb]

I've now created this macro and done some test runs. I can't get it to
find the files in the folders. I'm not even sure it's finding the
folders. It just places "Missing" in column c. How do I tell it where
the folders are? I did change the default location to save Excel files
into the first of the directoriew to see if I could get it to find
them. But I'd rather do it with a macro command than change the default
where Excel files are saved.
When I step through the macro, I get a message that "C:\" cannot be
found. So I'm guessing that the macro is looking in the root directory
rather than where the files are stored.
Any ideas?
TIA

 

[Dave Peterson]

The first code looked for the folder names in column A and the workbook names in
column B.

But read my follow up post if the names of the workbooks varied.

 

[davegb]

Thanks for your prompt reply!
In the test I did, all the filenames were the same. I decided to do it
that way at first, then add the complication later. So this was run
with all the file names the same. The workbook name was in B1.

 

[Dave Peterson]

You put the common filename in B1.

You put the folders in A2:A65?

I'm guessing that the folder names weren't spelled correctly. Or since all were
marked missing, maybe the filename wasn't spelled correctly. (Did you include
the .xls?)

 

[davegb]

The filename, New Microsoft Excel Worksheet.xls is correct and in cell
b1
The directory names are in cells A2:A5 (decreased the number for test
purposes, changed in macro appropriately).
The folder names, for the test, are one, two, three, four. Spelled
correctly. I used names that are hard to misspell.
Any other ideas?

 

[davegb]

Here is the code as I have modified it for this test. The only changes
I made were to the range of cells and had it enter the number 55 into
cell A1 so I know the macro has opened the spreadsheet.

Sub MoveFolder2Folder()

Dim myRng As Range
Dim myCell As Range
Dim myFileName As String
Dim TestStr As String
Dim tempWkbk As Workbook

With Worksheets("sheet1")
Set myRng = .Range("a2:A5")
For Each myCell In myRng.Cells
myFileName = myCell.Value
If Right(myFileName, 1) <> "\" Then
myFileName = myFileName & "\"
End If
myFileName = myFileName & .Range("b1").Value
TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0
If TestStr = "" Then
myCell.Offset(0, 2).Value = "Missing!"
Else
myCell.Offset(0, 2).ClearContents
Set tempWkbk = Workbooks.Open(Filename:=myFileName)
'do your work
ActiveCell.FormulaR1C1 = "55"
tempWkbk.Close savechanges:=True
End If
Next myCell
End With
End Sub

I have stepped through the macro and the filename, in cell B1 is not
getting into the myFileName variable. The directory names, for the
test, are one, two, three and four. The first in the directory is
"four". myFileName = "four\" even after the reference to B1. It should
include the file name "New Microsoft Excel Spreadsheet.xls" but it
doesn't. What am I missing?

 

[Dave Peterson]

This line:

myFileName = myFileName & .Range("b1").Value

Picks up the value in B1.

Try replacing that one line with these 3.

MsgBox .Range("b1").Value
myFileName = myFileName & .Range("b1").Value
MsgBox myFileName

It won't help the macro run any better, but you'll see what is actually in B1
and what myFileName is holding.

(I'm still guessing typos!)

And you did put your data on Sheet1???

If no, then change this line of code:

With Worksheets("sheet1")

 

[davegb]

Thanks for your patience.
After replacing the line with the 3 new lines, message boxes appeared,
first a blank, then one with the first directory name in it and a
backslash (no filename), then a blank, then the next directory with
backslash, then a blank, etc.

Also, I tried putting in the file name (in quotes) in the macro in
place of the .Range("b1").Value, and myFileName picked it up, but still
said the files were missing. Does that tell you anyting?

I also changed the name of the files and B1 to "test.xls" to be sure
there were no spaces or unseen characters in them. Still the same
result.

 

[Dave Peterson]

If this line:

MsgBox .Range("b1").Value

resulted in a blank message box, then you don't have the value in B1 (maybe
column B is hidden and you have it in column C???---or maybe row 1 is hidden and
you're in row 2????)

Or maybe you don't have it on sheet1.

 

[Dave Peterson]

This is gonna be a slap the forehead when you find the solution!

 

[davegb]

Found part of the problem, and it is a slap in the forehead! All this
time I had the filename in cell b2! Duh! But now that I've changed the
macro to look in B2, it gets the right name, but still can't find any
of the files. Any ideas on what to look for next?
Thanks again!

 

[Dave Peterson]

I still gotta believe that it's a typo problem.

After this line:

myFileName = myFileName & .Range("b2").Value
put these lines:

debug.print myfilename
exit sub

Then hit ctrl-g to see the immediate window.

You'll see the filename that is being searched for.
highlight it and copy it.
windows start button|Run
paste it into that dialog box.

If this opens the file correctly, then there's a problem with the macro. If it
doesn't, then there's a problem with your typing.

If it fails, paste that result in the followup post. I'd like to see what that
string looks like.

(Remember to remove these debugging lines (msgbox's, debug.prints, and exit sub)
when you're ready.)

 

[davegb]

Did as you suggested. It returned "four\test.xls". Didn't have to run
it, I know that it can't be found. It's not returning the full path to
these folders and files, only the local directory name and the file
name. How do I tell XL where these folders are?

 

[davegb]

Found it! Remember I asked you earlier how XL could find these
directories and files? The problem was the I never gave the full
pathname to XL. I added a statement to make strPath =
ActiveWorkbook.path, then added this and a "\" to the myFileName
variable. Macro ran like a charm!
Thanks for all your help, Dave! Now on to creating a macro to put the
spreadsheet names in a column because they will not all be the same,
but a variant on the folder names. Then I can use the list for another
macro will use it to open and modify all the files for the 64 counties!

 

[Dave Peterson]

make sure that column A includes the complete path:

c:\four\
or
c:\my folder\my documents\my sub folder\four

====

Weird guess follows--probably not close to being the problem...

If you select one of those cells, what do you see in the cell?
What do you see in the formulabar for that cell?

If it's not the same, maybe someone included portions of the path in the
numberformat for that cell.

If that's the case, you could use either (not both) of these fixes:
Format the cells as General and fix the values in all those cells.

or...

Change this line from:
myFileName = myCell.Value
to
myFileName = myCell.Text

 

[Dave Peterson]

Whew!

Glad you found the solution.

Found it! Remember I asked you earlier how XL could find these
directories and files? The problem was the I never gave the full
pathname to XL. I added a statement to make strPath =
ActiveWorkbook.path, then added this and a "\" to the myFileName
variable. Macro ran like a charm!
Thanks for all your help, Dave! Now on to creating a macro to put the
spreadsheet names in a column because they will not all be the same,
but a variant on the folder names. Then I can use the list for another
macro will use it to open and modify all the files for the 64 counties!