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

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