Making Changes in a Group of Workbooks
 

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

Making Changes in a Group of Workbooks
 

It worked when I took this line out

If MyFile "" Then MyFile = dirName & MyFile

"QTGlennM" wrote:

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

Making Changes in a Group of Workbooks
 

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


QTGlennM wrote:

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

--

Dave Peterson