Q
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
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