You could turn that subroutine into a function with something like:
Option Explicit
Function SaveWithParens(SomeWorkbook As Workbook, myPath As String, _
myFileName As String) As Boolean
Dim myNewFileName As String
Dim iCtr As Long
Dim TestStr As String
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If
TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0
If TestStr = "" Then
'MsgBox "Invalid drive/folder!"
SaveWithParens = False
Exit Function
End If
iCtr = 0
Do
iCtr = iCtr + 1
myNewFileName = myPath & myFileName & "(" & iCtr & ")" & ".xls"
TestStr = ""
On Error Resume Next
TestStr = Dir(myNewFileName)
On Error GoTo 0
If TestStr = "" Then
'not already there
On Error Resume Next
SomeWorkbook.SaveAs Filename:=myNewFileName, _
FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
Err.Clear
SaveWithParens = False
Else
SaveWithParens = True
End If
On Error Goto 0
Exit Do
End If
Loop
End Function
Sub testme()
Dim myFN As String
Dim myDefaultName As String 'must be good!
Dim ThisPath As String
Dim WorkedOk As Boolean
ThisPath = "C:\my documents\excel\test\"
myDefaultName = "somenamehere"
myFN = "qqewr:::.xls"
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=ThisPath & myFN, _
FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
Err.Clear
WorkedOk = SaveWithParens(SomeWorkbook:=ActiveWorkbook, _
myPath:=ThisPath, myFileName:=myDefaultName)
If WorkedOk Then
MsgBox "Worked ok!"
Else
MsgBox "Something else went wrong"
End If
End If
On Error GoTo 0
End Sub
Dave Peterson wrote:
Chip Pearson has a whole page dedicated to error handling:
http://cpearson.com/excel/ErrorHandling.htm
Pay particular attention to this section:
Enabled And Active Error Handlers
=====
Another option is to use something like:
on error resume next
someworkbook.saveas filename:=invalidcharactershere, _
fileformat:=xlworkbooknormal
if err.number < 0 then
err.clear
some kind of error message???
or
do the CT08-0001_Invalid Name (1).xls stuff here
end if
on error goto 0
DrewWil wrote:
Thanks Dave! I'll try and incorporate what you wrote in my macro.
I'm not surprised that the 2nd question doesn't make sense. I'm slightly
more advanced than a beginner but not profecient by any means.
Let me try and explain what I'm doing and hopefully you can tell me if it
makes sense.
Each file contains data from different tests. I'm saving the file based on
two cell contents. ("G2") has a job number (CT08-0001) and ("D5") has a test
name (Leakage @ 85C) Sometimes the text in ("D5") has invalid characters
which gives me an error when I try and save. So I put an Error Goto function
so that when the error happens it goes to a part of the code that makes a new
file name based on "G2" (which never changes) and my own text. "Invalid name"
The problem is that it works fine with the first error I get but if another
file comes along with an invalid file name it won't Goto the label again. It
just stops.
That's also why I needed hekp with the saving so that if two invalid files
with the same number come along it would name the first "CT08-0001_Invalid
Name" and the next "CT08-0001_Invalid Name (1)"
Is there a better way to do that? (or maybe the right way. lol)
Thanks so much for taking the time to read all of this
"Dave Peterson" wrote:
I don't understand the second question, but this kind of thing worked ok for me
for the first question:
Option Explicit
Sub testme()
Dim myPath As String
Dim myFileName As String 'no extension here!
Dim myNewFileName As String
Dim iCtr As Long
Dim TestStr As String
myPath = "C:\my documents\excel\test\"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If
TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0
If TestStr = "" Then
MsgBox "Invalid drive/folder!"
Exit Sub
End If
myFileName = "filenamehere"
iCtr = 0
Do
iCtr = iCtr + 1
myNewFileName = myPath & myFileName & "(" & iCtr & ")" & ".xls"
TestStr = ""
On Error Resume Next
TestStr = Dir(myNewFileName)
On Error GoTo 0
If TestStr = "" Then
'not already there
ActiveWorkbook.SaveAs Filename:=myNewFileName, _
FileFormat:=xlWorkbookNormal
Exit Do
End If
Loop
End Sub
I think I'd use something more like:
myNewFileName = myPath & myFileName & "(" & format(iCtr, "0000") & ")" & ".xls"
to get names like book(0001).xls and book(9999).xls
And if you have filenames like
book(1).xls
book(2).xls
book(3).xls
book(4).xls
And delete book(3).xls, then the next run will use (3) in the filename.
DrewWil wrote:
Hello. I have two questions. I have a macro that opens a bunch of files,
does stuff to them and then saves them based on the contents in a few cells.
Is there a way to automatically append a "(1)" to a file if the file name
already exist? And then increment the number accordingly?
My second ? is that I have an "On Error Goto" statement but it only runs one
time. The macro loops as many times as there are files in the folder but if
the specific error happens more than once it doesn't execute the "On Error
Goto" command again.
Thanks in advance for the help
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson