macro to move from directory to directory

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

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


davegb wrote:
>
> 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 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



Dave Peterson wrote:
>
> 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
>
> davegb wrote:
> >
> > 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

--

Dave Peterson

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.

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

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 wrote:
>
> 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

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.

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 wrote:
>
> 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

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?

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?

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 wrote:
>
> 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

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.

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.



davegb wrote:
>
> 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

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

davegb wrote:
>
> 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

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!

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 wrote:
>
> 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

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?

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!

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







davegb wrote:
>
> 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?

--

Dave Peterson

Whew!

Glad you found the solution.

davegb wrote:
>
> 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

Thanks, Dave!