Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Copy4)
Hi Ron,
After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Copy4)
Hi Steve
Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Hi Ron,
Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
If there is a error let the code give you the name of the workbook and go to the next file.
After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Ok, how can I do that?
Because so far, all I can tell is that it's telling me there is an error, but I've found no way to bypass the "bad file" and move on to the next file. If I debug, it goes to the macro, and shows me the line of code that's throwing that error. If I stop, it ends the operation the macro is performing. The only other option is help. The continue option has been "greyed out" not allowing me to continue on. The first error was due to one workbook actually having data in 65536 rows, and my needing to delete that data manually (it was bad data that needed deleting), because the macro had done its job, but still called an error. The next file that caused me trouble threw an error because the last column went to 0, and once I added an if statement matching the one for a last row, it was fixed, and I moved on. here is the complete code for all of the elements. Please keep in mind that there are UDF's, and macros. Each work. They all work together, and have acted to process some 32 files in 10 minutes. But if I hit an error because of something in a file that requires that error to be called, I have no means in here to bypass that error, and move on with the next file in sequence. It's been forcing me to start over again each and every time I hit that error. -------------------------------------------------------------------------------------- Sub CompactAllSheets() Dim wks As Worksheet 'Dim lngVisible As Long For Each wks In Worksheets 'lngVisible = wks.Visible CompactSheet wks 'wks.Visible = lngVisible Next wks End Sub '====================================== Public Sub CompactSheet(Optional ByVal wks As Worksheet) Dim rng As Range If wks Is Nothing Then Set wks = ActiveSheet Set rng = LastCell(wks) wks.Range(rng.Offset(0, 1), wks.Cells(1, Columns.count)).EntireColumn.Delete wks.Range(rng.Offset(1, 0), wks.Cells(Rows.count, 1)).EntireRow.Delete End Sub ========================================= Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If If intLastColumn = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function '================= Sub ASaveNewFormat() 'Joel @ MSDN Newsgroups. 6-17-2008. With Application .DisplayAlerts = True .ScreenUpdating = False End With Folder = ActiveWorkbook.Path 'this sets the folder of the source file FName = ActiveWorkbook.Name 'this looks at the existing file's name 'remove extension FName = Left(FName, InStr(FName, ".") - 1) ' this appears to remove the existing file's extension SaveName = Folder & "\BloatReducedFiles\" & FName & ".xlsx" ' this tells where to save it, but one directory deeper. ' if you wish to have it save to another directory, you must specify that directory. ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlOpenXMLWorkbook ' This assigns to the SaveName, and tells it what format. 'Sweet..... Call CompactAllSheets 'This calls to run another macro/function. With ActiveWorkbook .Save .ChangeFileAccess Mode:=xlReadOnly .Close End With End Sub '================================= Sub AFileSearch() Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub -------------------------------------------------------------------------------------- Thanks for your input. It really is appreciated. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Hi Ron,
For now I'll use the on error resume next. It appears to be working ok. Do you have an idea as to how I can use an if statement to look if there is a file by a given name in the final directory, and if so, skip, and go to the next file? Something akin to: if FileName is true then next FileName end if Thanks. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Hi Steve
Is it one file or more ? Does the name start or end with the same characters -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, For now I'll use the on error resume next. It appears to be working ok. Do you have an idea as to how I can use an if statement to look if there is a file by a given name in the final directory, and if so, skip, and go to the next file? Something akin to: if FileName is true then next FileName end if Thanks. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
It's multiple files, and the first four characters are identical.
The last characters, are always numeric and will always differ. As I've thought about this, it seems that I'd have to compare the file name that is open, to the name of the files in the directory where I'd want to save to. If the same pre-save file name is identical to a file name that arleady exists, then it needs to skip that "new" file, and move on to the next file in the source directory. I hope that makes sense. If not, pelase ask. "Ron de Bruin" wrote: Hi Steve Is it one file or more ? Does the name start or end with the same characters -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, For now I'll use the on error resume next. It appears to be working ok. Do you have an idea as to how I can use an if statement to look if there is a file by a given name in the final directory, and if so, skip, and go to the next file? Something akin to: if FileName is true then next FileName end if Thanks. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Hi Steve
Try this If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then This is the folder where you copy to C:\Users\Ron\test2\ Sub Example() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim ErrorYes As Boolean Dim FSO As Object 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If Set FSO = CreateObject("scripting.filesystemobject") 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then 'do nothing Else Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath End If FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in one worksheet in mybook On Error Resume Next With mybook.Worksheets(1) If .ProtectContents = False Then .Range("A1").Value = "My New Header" Else ErrorYes = True End If End With If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... It's multiple files, and the first four characters are identical. The last characters, are always numeric and will always differ. As I've thought about this, it seems that I'd have to compare the file name that is open, to the name of the files in the directory where I'd want to save to. If the same pre-save file name is identical to a file name that arleady exists, then it needs to skip that "new" file, and move on to the next file in the source directory. I hope that makes sense. If not, pelase ask. "Ron de Bruin" wrote: Hi Steve Is it one file or more ? Does the name start or end with the same characters -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, For now I'll use the on error resume next. It appears to be working ok. Do you have an idea as to how I can use an if statement to look if there is a file by a given name in the final directory, and if so, skip, and go to the next file? Something akin to: if FileName is true then next FileName end if Thanks. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Hi Ron,
code first, then comments/questions. -------------------------------------------------- If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then ........ Set FSO = CreateObject("scripting.filesystemobject") 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then 'do nothing Else Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath End If FilesInPath = Dir() Loop ------------------------------------------------------------------------------ Ok, now that I've gone through this, it appears slightly different than the copy4 version you referred me to on your site, the day before yesterday. The main difference that I've seen is the FSO.fileexists() element. Please talk to me about that. It appears that it looks through the final directory for identical file names. If it finds the name, it moves on, and if it does not find the identical file name, it saves a new version, and performs the requested code-task. Is this a correct understanding? Thank you. "Ron de Bruin" wrote: Hi Steve Try this If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then This is the folder where you copy to C:\Users\Ron\test2\ Sub Example() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim ErrorYes As Boolean Dim FSO As Object 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If Set FSO = CreateObject("scripting.filesystemobject") 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then 'do nothing Else Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath End If FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in one worksheet in mybook On Error Resume Next With mybook.Worksheets(1) If .ProtectContents = False Then .Range("A1").Value = "My New Header" Else ErrorYes = True End If End With If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... It's multiple files, and the first four characters are identical. The last characters, are always numeric and will always differ. As I've thought about this, it seems that I'd have to compare the file name that is open, to the name of the files in the directory where I'd want to save to. If the same pre-save file name is identical to a file name that arleady exists, then it needs to skip that "new" file, and move on to the next file in the source directory. I hope that makes sense. If not, pelase ask. "Ron de Bruin" wrote: Hi Steve Is it one file or more ? Does the name start or end with the same characters -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, For now I'll use the on error resume next. It appears to be working ok. Do you have an idea as to how I can use an if statement to look if there is a file by a given name in the final directory, and if so, skip, and go to the next file? Something akin to: if FileName is true then next FileName end if Thanks. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
modification of auto-multiple workbook macro, Ron DeBruin (Cop
Correct
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, code first, then comments/questions. -------------------------------------------------- If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then ....... Set FSO = CreateObject("scripting.filesystemobject") 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then 'do nothing Else Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath End If FilesInPath = Dir() Loop ------------------------------------------------------------------------------ Ok, now that I've gone through this, it appears slightly different than the copy4 version you referred me to on your site, the day before yesterday. The main difference that I've seen is the FSO.fileexists() element. Please talk to me about that. It appears that it looks through the final directory for identical file names. If it finds the name, it moves on, and if it does not find the identical file name, it saves a new version, and performs the requested code-task. Is this a correct understanding? Thank you. "Ron de Bruin" wrote: Hi Steve Try this If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then This is the folder where you copy to C:\Users\Ron\test2\ Sub Example() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim ErrorYes As Boolean Dim FSO As Object 'Fill in the path\folder where the files are MyPath = "C:\Users\Ron\test" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If Set FSO = CreateObject("scripting.filesystemobject") 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If FSO.FileExists("C:\Users\Ron\test2\" & FilesInPath) = True Then 'do nothing Else Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath End If FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Change cell value(s) in one worksheet in mybook On Error Resume Next With mybook.Worksheets(1) If .ProtectContents = False Then .Range("A1").Value = "My New Header" Else ErrorYes = True End If End With If Err.Number 0 Then ErrorYes = True Err.Clear 'Close mybook without saving mybook.Close savechanges:=False Else 'Save and close mybook mybook.Close savechanges:=True End If On Error GoTo 0 Else 'Not possible to open the workbook ErrorYes = True End If Next Fnum End If If ErrorYes = True Then MsgBox "There are problems in one or more files, possible problem:" _ & vbNewLine & "protected workbook/sheet or a sheet/range that not exist" End If 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... It's multiple files, and the first four characters are identical. The last characters, are always numeric and will always differ. As I've thought about this, it seems that I'd have to compare the file name that is open, to the name of the files in the directory where I'd want to save to. If the same pre-save file name is identical to a file name that arleady exists, then it needs to skip that "new" file, and move on to the next file in the source directory. I hope that makes sense. If not, pelase ask. "Ron de Bruin" wrote: Hi Steve Is it one file or more ? Does the name start or end with the same characters -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, For now I'll use the on error resume next. It appears to be working ok. Do you have an idea as to how I can use an if statement to look if there is a file by a given name in the final directory, and if so, skip, and go to the next file? Something akin to: if FileName is true then next FileName end if Thanks. "Ron de Bruin" wrote: If there is a error let the code give you the name of the workbook and go to the next file. After the macro is ready you can check out the problem files Show us the code you are running on each file and tell us where you get the error in some workbooks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, Actually, I do want to "update" all of my xl* files. The problem goes back to having processed some files, and then if I get hung up on some error, it requires that I restart all over again. Errors I can deal with, but starting over each time I get yet another error is killing my time. I need to be able to pick up where I left off, and move forward. What would it take to modify this so that I don't need to start from scratch each time I hit a bug/error? "Ron de Bruin" wrote: Hi Steve Do not change Fnum If you only want to run the macro on the xls files then change this line FilesInPath = Dir(myPath & "DTR*.xl*") to FilesInPath = Dir(myPath & "DTR*.xls") -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SteveDB1" wrote in message ... Hi Ron, After I made the modifications to your copy4 macro, I've found the following-- code first, then issue. ---------------------------------------------------- Dim myPath As String, FilesInPath As String Dim MyFiles() As String, Fnum As Long Dim myBook As Workbook myPath = "S:\Assignments - Final\Truckee River Claims\" FilesInPath = Dir(myPath & "DTR*.xl*") If FilesInPath = "" Then MsgBox "No Files Found" Exit Sub End If Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set myBook = Nothing On Error Resume Next Set myBook = Workbooks.Open(myPath & MyFiles(Fnum)) On Error GoTo 0 Call ASaveNewFormat Next Fnum End If End Sub --------------------------------------------------- I have two primary issues. 1- This requires me to process all of the files in my directory, and I do not want to re-process them, once I've done so if I get caught up in an error. As such, I tried changing the starting number of Fnum. I think that this is where my error is stemming from, because it worked fine before I changed the start # for Fnum. 2- How can I keep it from starting out at 0, and actually start at the file number of my choosing, in the event I find that there is a file that the routine will not process? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ron DeBruin Macro - Moving Sheet Name from Last Column to Column A | Excel Worksheet Functions | |||
auto safe macro for workbook | Excel Programming | |||
auto run macro on workbook open | Excel Programming | |||
auto run macro at workbook open | Excel Programming | |||
VBA Excel 2000 - one shot multiple cells modification | Excel Programming |