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

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

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

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

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

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

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, 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.

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