Error and Save as question
 

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

Error and Save as question
 

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

Error and Save as question
 

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

Error and Save as question
 

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

Error and Save as question
 

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

Error and Save as question
 

Dave,

I'll try and see if I can use your suggestions to get it to work. I'll also
read up on pearson's page.

Thanks for the help

"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

Error and Save as question
 

I wrote that last reply before I got your second message. I'm really going
to have to take some time and go through your code to see if I can figure it
out.

I sort of just fell into the "Macro guy" position here at work and I have
very little experience. Mainly for the past few years I've been able to just
search this site and piece together stuff to do what I want. I'm sure I'm
doing things wrong or backwards.

Do you think you might have time to put a few more notes in the code you
just sent? I did read Pearson's error page and I now I know why I was getting
that error.

Thanks

"DrewWil" wrote:

Dave,

I'll try and see if I can use your suggestions to get it to work. I'll also
read up on pearson's page.

Thanks for the help

"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

Error and Save as question
 

Option Explicit
Function SaveWithParens(SomeWorkbook As Workbook, myPath As String, _
myFileName As String) As Boolean

'some variables
Dim myNewFileName As String
Dim iCtr As Long
Dim TestStr As String

'add a backslash if you didn't include it
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

'Check to see if it's a valid folder
'every folder has a nul(l) device associated with it.
'if Nul isn't returned, then the path wasn't correct
'either a typo or the folder or drive didn't really exist
'clean out a string variable
TestStr = ""
'Mr. VBA, ignore any error, I'll handle it myself
On Error Resume Next
TestStr = Dir(myPath & "nul")
'Go back to checking Mr. VBA!
On Error GoTo 0

'if that test variable is still "", then the drive/folder wasn't found
If TestStr = "" Then
'MsgBox "Invalid drive/folder!"
'send the bad news back to the calling procedure
SaveWithParens = False
'and get the heck out
Exit Function
End If

'initialize my counter to 0
iCtr = 0
Do
'start with 1, then increment in each loop
iCtr = iCtr + 1
'build that new filename
myNewFileName = myPath & myFileName & "(" & iCtr & ")" & ".xls"
'check to see if the with that name already exists
'teststr will return the filename (w/o the path if it's there)
TestStr = ""
'Never mind any error, Mr. VBA
On Error Resume Next
TestStr = Dir(myNewFileName)
'go back to checking, Mr. VBA
On Error GoTo 0

If TestStr = "" Then
'that file name isn't used
'but who knows if the save will work
'maybe it's on a restricted access drive
'maybe the drive is out of space
'mr vba, you know what not to care about!
On Error Resume Next
'try saving the workbook that was passed to the function
'using the new filename
SomeWorkbook.SaveAs Filename:=myNewFileName, _
FileFormat:=xlWorkbookNormal
'was there an error
If Err.Number < 0 Then
'clear the error
Err.Clear
'return the bad news
SaveWithParens = False
Else
'it worked ok!!!
'send the good news back
SaveWithParens = True
End If
'back to you mr. VBA
On Error Goto 0
'we're done here, get the heck out of the loop
Exit Do
End If
Loop

End Function
Sub testme()
'define some variables
Dim myFN As String
Dim myDefaultName As String 'must be good!
Dim ThisPath As String
Dim WorkedOk As Boolean

'where do you want to save it?
ThisPath = "C:\my documents\excel\test\"

'this has to be a good name--I'm not sure how you got:
'CT08-0001_Invalid Name (1)
myDefaultName = "somenamehere"

'here's an invalid name (in windows, at least)
myFN = "qqewr:::.xls"

'the saveas could fail, but I don't want the code to stop.
'I'll check for errors myself.
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=ThisPath & myFN, _
FileFormat:=xlWorkbookNormal

'was there an error?
If Err.Number < 0 Then
'clear the error
Err.Clear
'call savewithparens() with all the stuff I need to know
'what workbook should be save, where, and pass it a good filename
WorkedOk = SaveWithParens(SomeWorkbook:=ActiveWorkbook, _
myPath:=ThisPath, myFileName:=myDefaultName)

'did the function work ok
If WorkedOk Then
MsgBox "Worked ok!"
Else
MsgBox "Something else went wrong"
End If
End If
'If there are any errors after this, I want to know!
On Error GoTo 0
End Sub

DrewWil wrote:

I wrote that last reply before I got your second message. I'm really going
to have to take some time and go through your code to see if I can figure it
out.

I sort of just fell into the "Macro guy" position here at work and I have
very little experience. Mainly for the past few years I've been able to just
search this site and piece together stuff to do what I want. I'm sure I'm
doing things wrong or backwards.

Do you think you might have time to put a few more notes in the code you
just sent? I did read Pearson's error page and I now I know why I was getting
that error.

Thanks

"DrewWil" wrote:

Dave,

I'll try and see if I can use your suggestions to get it to work. I'll also
read up on pearson's page.

Thanks for the help

"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

Error and Save as question
 

Thanks. I'll let you know how it goes

"Dave Peterson" wrote:

Option Explicit
Function SaveWithParens(SomeWorkbook As Workbook, myPath As String, _
myFileName As String) As Boolean

'some variables
Dim myNewFileName As String
Dim iCtr As Long
Dim TestStr As String

'add a backslash if you didn't include it
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

'Check to see if it's a valid folder
'every folder has a nul(l) device associated with it.
'if Nul isn't returned, then the path wasn't correct
'either a typo or the folder or drive didn't really exist
'clean out a string variable
TestStr = ""
'Mr. VBA, ignore any error, I'll handle it myself
On Error Resume Next
TestStr = Dir(myPath & "nul")
'Go back to checking Mr. VBA!
On Error GoTo 0

'if that test variable is still "", then the drive/folder wasn't found
If TestStr = "" Then
'MsgBox "Invalid drive/folder!"
'send the bad news back to the calling procedure
SaveWithParens = False
'and get the heck out
Exit Function
End If

'initialize my counter to 0
iCtr = 0
Do
'start with 1, then increment in each loop
iCtr = iCtr + 1
'build that new filename
myNewFileName = myPath & myFileName & "(" & iCtr & ")" & ".xls"
'check to see if the with that name already exists
'teststr will return the filename (w/o the path if it's there)
TestStr = ""
'Never mind any error, Mr. VBA
On Error Resume Next
TestStr = Dir(myNewFileName)
'go back to checking, Mr. VBA
On Error GoTo 0

If TestStr = "" Then
'that file name isn't used
'but who knows if the save will work
'maybe it's on a restricted access drive
'maybe the drive is out of space
'mr vba, you know what not to care about!
On Error Resume Next
'try saving the workbook that was passed to the function
'using the new filename
SomeWorkbook.SaveAs Filename:=myNewFileName, _
FileFormat:=xlWorkbookNormal
'was there an error
If Err.Number < 0 Then
'clear the error
Err.Clear
'return the bad news
SaveWithParens = False
Else
'it worked ok!!!
'send the good news back
SaveWithParens = True
End If
'back to you mr. VBA
On Error Goto 0
'we're done here, get the heck out of the loop
Exit Do
End If
Loop

End Function
Sub testme()
'define some variables
Dim myFN As String
Dim myDefaultName As String 'must be good!
Dim ThisPath As String
Dim WorkedOk As Boolean

'where do you want to save it?
ThisPath = "C:\my documents\excel\test\"

'this has to be a good name--I'm not sure how you got:
'CT08-0001_Invalid Name (1)
myDefaultName = "somenamehere"

'here's an invalid name (in windows, at least)
myFN = "qqewr:::.xls"

'the saveas could fail, but I don't want the code to stop.
'I'll check for errors myself.
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=ThisPath & myFN, _
FileFormat:=xlWorkbookNormal

'was there an error?
If Err.Number < 0 Then
'clear the error
Err.Clear
'call savewithparens() with all the stuff I need to know
'what workbook should be save, where, and pass it a good filename
WorkedOk = SaveWithParens(SomeWorkbook:=ActiveWorkbook, _
myPath:=ThisPath, myFileName:=myDefaultName)

'did the function work ok
If WorkedOk Then
MsgBox "Worked ok!"
Else
MsgBox "Something else went wrong"
End If
End If
'If there are any errors after this, I want to know!
On Error GoTo 0
End Sub

DrewWil wrote:

I wrote that last reply before I got your second message. I'm really going
to have to take some time and go through your code to see if I can figure it
out.

I sort of just fell into the "Macro guy" position here at work and I have
very little experience. Mainly for the past few years I've been able to just
search this site and piece together stuff to do what I want. I'm sure I'm
doing things wrong or backwards.

Do you think you might have time to put a few more notes in the code you
just sent? I did read Pearson's error page and I now I know why I was getting
that error.

Thanks

"DrewWil" wrote:

Dave,

I'll try and see if I can use your suggestions to get it to work. I'll also
read up on pearson's page.

Thanks for the help

"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

Error and Save as question
 

It worked brilliantly!

Thanks so much for taking the time to write some notes. They were very
helpful.

"DrewWil" wrote:

Thanks. I'll let you know how it goes

"Dave Peterson" wrote:

Option Explicit
Function SaveWithParens(SomeWorkbook As Workbook, myPath As String, _
myFileName As String) As Boolean

'some variables
Dim myNewFileName As String
Dim iCtr As Long
Dim TestStr As String

'add a backslash if you didn't include it
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

'Check to see if it's a valid folder
'every folder has a nul(l) device associated with it.
'if Nul isn't returned, then the path wasn't correct
'either a typo or the folder or drive didn't really exist
'clean out a string variable
TestStr = ""
'Mr. VBA, ignore any error, I'll handle it myself
On Error Resume Next
TestStr = Dir(myPath & "nul")
'Go back to checking Mr. VBA!
On Error GoTo 0

'if that test variable is still "", then the drive/folder wasn't found
If TestStr = "" Then
'MsgBox "Invalid drive/folder!"
'send the bad news back to the calling procedure
SaveWithParens = False
'and get the heck out
Exit Function
End If

'initialize my counter to 0
iCtr = 0
Do
'start with 1, then increment in each loop
iCtr = iCtr + 1
'build that new filename
myNewFileName = myPath & myFileName & "(" & iCtr & ")" & ".xls"
'check to see if the with that name already exists
'teststr will return the filename (w/o the path if it's there)
TestStr = ""
'Never mind any error, Mr. VBA
On Error Resume Next
TestStr = Dir(myNewFileName)
'go back to checking, Mr. VBA
On Error GoTo 0

If TestStr = "" Then
'that file name isn't used
'but who knows if the save will work
'maybe it's on a restricted access drive
'maybe the drive is out of space
'mr vba, you know what not to care about!
On Error Resume Next
'try saving the workbook that was passed to the function
'using the new filename
SomeWorkbook.SaveAs Filename:=myNewFileName, _
FileFormat:=xlWorkbookNormal
'was there an error
If Err.Number < 0 Then
'clear the error
Err.Clear
'return the bad news
SaveWithParens = False
Else
'it worked ok!!!
'send the good news back
SaveWithParens = True
End If
'back to you mr. VBA
On Error Goto 0
'we're done here, get the heck out of the loop
Exit Do
End If
Loop

End Function
Sub testme()
'define some variables
Dim myFN As String
Dim myDefaultName As String 'must be good!
Dim ThisPath As String
Dim WorkedOk As Boolean

'where do you want to save it?
ThisPath = "C:\my documents\excel\test\"

'this has to be a good name--I'm not sure how you got:
'CT08-0001_Invalid Name (1)
myDefaultName = "somenamehere"

'here's an invalid name (in windows, at least)
myFN = "qqewr:::.xls"

'the saveas could fail, but I don't want the code to stop.
'I'll check for errors myself.
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=ThisPath & myFN, _
FileFormat:=xlWorkbookNormal

'was there an error?
If Err.Number < 0 Then
'clear the error
Err.Clear
'call savewithparens() with all the stuff I need to know
'what workbook should be save, where, and pass it a good filename
WorkedOk = SaveWithParens(SomeWorkbook:=ActiveWorkbook, _
myPath:=ThisPath, myFileName:=myDefaultName)

'did the function work ok
If WorkedOk Then
MsgBox "Worked ok!"
Else
MsgBox "Something else went wrong"
End If
End If
'If there are any errors after this, I want to know!
On Error GoTo 0
End Sub

DrewWil wrote:

I wrote that last reply before I got your second message. I'm really going
to have to take some time and go through your code to see if I can figure it
out.

I sort of just fell into the "Macro guy" position here at work and I have
very little experience. Mainly for the past few years I've been able to just
search this site and piece together stuff to do what I want. I'm sure I'm
doing things wrong or backwards.

Do you think you might have time to put a few more notes in the code you
just sent? I did read Pearson's error page and I now I know why I was getting
that error.

Thanks

"DrewWil" wrote:

Dave,

I'll try and see if I can use your suggestions to get it to work. I'll also
read up on pearson's page.

Thanks for the help

"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

Error and Save as question
 

Glad they helped.

DrewWil wrote:

It worked brilliantly!

Thanks so much for taking the time to write some notes. They were very
helpful.

<<snipped

Incremental files saves to a URL
 

Dave, your code worked great for incremental file saves, however, I am having trouble with it when I try to save to a URL. I get a message Invalid Drive/Folder.
I am tring to save reports to our corporate website, and I want to make it a easy and painless for operators to upload a file on a daily basis.
I edited the Pathname to the URL but no go.
Any Thoughts on what I might be doing wrong.

Incremental files saves to a URL
 

If this is meant for me, then I don't have a guess. I don't work with web
sites.

If this is meant for another Dave, sorry.

Neil, Holmes wrote:

Dave, your code worked great for incremental file saves, however, I am having trouble with it when I try to save to a URL. I get a message Invalid Drive/Folder.
I am tring to save reports to our corporate website, and I want to make it a easy and painless for operators to upload a file on a daily basis.
I edited the Pathname to the URL but no go.
Any Thoughts on what I might be doing wrong.

--

Dave Peterson