About Us

Ron de Bruin

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Ron DeBruin Macro - Moving Sheet Name from Last Column to Column A	ScottMSP	Excel Worksheet Functions	7	December 12th 08 06:07 PM
auto safe macro for workbook	pswanie	Excel Programming	6	June 17th 07 05:54 PM
auto run macro on workbook open	Mcasteel[_43_]	Excel Programming	2	November 15th 04 02:57 PM
auto run macro at workbook open	Juan Guemes	Excel Programming	2	August 28th 04 10:55 PM
VBA Excel 2000 - one shot multiple cells modification	Conceptor	Excel Programming	1	July 21st 03 05:40 PM

Menu

About Us