Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making Macros Available in All Workbooks | Excel Worksheet Functions | |||
how to making to project group | Excel Programming | |||
Making links between workbooks work | Excel Discussion (Misc queries) | |||
making a group visible (or not...) | Excel Programming | |||
Making your UserForm print other workbooks | Excel Programming |