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
|