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.

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.

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.

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.

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.

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.

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.