Making Changes in a Group of Workbooks

[QTGlennM]

I found this macro on Allen Wyatts excel tips, but it won't work. I
get the following error:

Run-time error '1004':

'C:\Documents and Settings\BMW\Desktop\New FolderTest File .xls' could
not be found.

Whats happening is the \ is not inserted between, New Folder\Test
File.xls .... Any ideas on how to get this working. All help is
greatly appreciated.

Thanks
Glenn

Public Sub ChangeFiles3()
Dim MyPath As String
Dim MyFile As String
Dim dirName As String

With Application.FileDialog(msoFileDialogFolderPicker)
' Optional: set folder to start in
.InitialFileName = "C:\Excel\"
.Title = "Select the folder to process"
If .Show = True Then
dirName = .SelectedItems(1)
End If
End With

MyPath = dirName & "\*.xls"
myFile = Dir(MyPath)
If MyFile > "" Then MyFile = dirName & MyFile

Do While MyFile <> ""
If Len(MyFile) = 0 Then Exit Do

Workbooks.Open MyFile

With ActiveWorkbook
For Each wks In .Worksheets
' Specify the change to make
wks.Range("A1").Value = "A1 Changed"
Next
End With

ActiveWorkbook.Close SaveChanges:=True

MyFile = Dir
If MyFile > "" Then MyFile = dirName & MyFile
Loop
End Sub

 

[Guest]

It worked when I took this line out

If MyFile > "" Then MyFile = dirName & MyFile

 

[Dave Peterson]

I bet the folder you selected was:
C:\Documents and Settings\BMW\Desktop\New Folder
and the file you wanted was
Test file .xls

Notice that there isn't a backslash between the path and filename, like:
C:\Documents and Settings\BMW\Desktop\New Folder\Test File .xls

So one change (that still has a bug!) to make:
If MyFile > "" Then MyFile = dirName & MyFile
becomes
If MyFile > "" Then MyFile = dirName & "\" & MyFile

But you could have another problem.

If you choose the root folder on the C: drive (say), then this line:
dirName = .SelectedItems(1)
returns
C:\
With the backslash!

So sometimes you need to add the backslash and sometimes you don't.

I'd check before I used it:

Option Explicit
Public Sub ChangeFiles3()
Dim MyPath As String
Dim MyFile As String
Dim dirName As String
Dim wks As Worksheet 'added

With Application.FileDialog(msoFileDialogFolderPicker)
' Optional: set folder to start in
.InitialFileName = "C:\Excel\"
.Title = "Select the folder to process"
If .Show = True Then
dirName = .SelectedItems(1)
End If
End With

If Right(MyPath, 1) = "\" Then
'ok, do nothing
Else
MyPath = MyPath & "\"
End If

MyPath = dirName & "*.xls" '<--removed \
MyFile = Dir(MyPath)
If MyFile > "" Then MyFile = dirName & MyFile

Do While MyFile <> ""
If Len(MyFile) = 0 Then Exit Do

Workbooks.Open MyFile

With ActiveWorkbook
For Each wks In .Worksheets
' Specify the change to make
wks.Range("A1").Value = "A1 Changed"
Next
End With

ActiveWorkbook.Close SaveChanges:=True

MyFile = Dir
If MyFile > "" Then MyFile = dirName & MyFile
Loop
End Sub