Hello all,

I have pasted some of my code below. It runs fine the first time but if the
user runs it again it hangs up on the .SaveAs line. If anyone can give me
any insight as to why this is happening and/or suggestions to fix it I would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

first pass creates a file with that name, second pass is trying to overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but if the
user runs it again it hangs up on the .SaveAs line. If anyone can give me
any insight as to why this is happening and/or suggestions to fix it I would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Thanks! I will check that out.

"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but if the
user runs it again it hangs up on the .SaveAs line. If anyone can give me
any insight as to why this is happening and/or suggestions to fix it I would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Your input was helpful but I am still stuck. Officename is a variable that
is a combination of the office and a serial date. I must use this name. If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but if the
user runs it again it hangs up on the .SaveAs line. If anyone can give me
any insight as to why this is happening and/or suggestions to fix it I would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

the only thing I would see as problematic is if OfficeName is not defined at
that point or if there is some network or file setting that is preventing
you from overwriting the existing file.

--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to
overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this
name.
If
the file is closed then re-opened it runs ok and just prompts the user
to
overwrite the file.

The name is unique to each week and I would always want to overwite
the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time
but
if the
user runs it again it hangs up on the .SaveAs line. If anyone
can
give me
any insight as to why this is happening and/or suggestions to fix
it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security
pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Following up on Tom's comment:

In the VBE use the debugger to step through the code and put a watch on your
variable so you can see what value it has when the line crashes.

"Christy" wrote:

Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Thank you both so much for the help. I used the degugger and when the line
crashes the variable is defined correctly. Stepping into the code causes
failure at the same line and this is the error message I get:

Runtime error 1004

Microsoft Excel cannot access the file 'C:\Program Files\Common Files\
system\Mapi\1033.' There are several possible reasons:

The file name or path name does does not exist

The file you are trying to open is being used by another program
(Close the document in the other program and try again)

The name of the workbook you're trying to save is the same as the .....(the
rest is cut off in the window can't be seen)

I want the file to save in the users default location - I can't really set a
direct path because users computers are all set up differently and I don't
know what would work for sure on all of them.

Christy ;)


"Vacation's Over" wrote:

Following up on Tom's comment:

In the VBE use the debugger to step through the code and put a watch on your
variable so you can see what value it has when the line crashes.

"Christy" wrote:

Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Christy -
i'll help you diagnose.

put an "exit sub" line right before "with newbook"
run your sub
then try to "save as" manually
and see what the problem is....

Also i forget but there is a way to get to users default folder. Start a
new post with that specific question and some one will tell you / us.

"Christy" wrote:

Thank you both so much for the help. I used the degugger and when the line
crashes the variable is defined correctly. Stepping into the code causes
failure at the same line and this is the error message I get:

Runtime error 1004

Microsoft Excel cannot access the file 'C:\Program Files\Common Files\
system\Mapi\1033.' There are several possible reasons:

The file name or path name does does not exist

The file you are trying to open is being used by another program
(Close the document in the other program and try again)

The name of the workbook you're trying to save is the same as the .....(the
rest is cut off in the window can't be seen)

I want the file to save in the users default location - I can't really set a
direct path because users computers are all set up differently and I don't
know what would work for sure on all of them.

Christy ;)


"Vacation's Over" wrote:

Following up on Tom's comment:

In the VBE use the debugger to step through the code and put a watch on your
variable so you can see what value it has when the line crashes.

"Christy" wrote:

Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

I tried what you said and I get a new workbook left open with the default
name (Book#). When I did a manual saveas on that file it open the save
dialog with Desktop selected prompting me to save?

I tried it several time and it kept opening new workbook and a manual saveas
was the same on all of them.


"Vacation's Over" wrote:

Christy -
i'll help you diagnose.

put an "exit sub" line right before "with newbook"
run your sub
then try to "save as" manually
and see what the problem is....

Also i forget but there is a way to get to users default folder. Start a
new post with that specific question and some one will tell you / us.

"Christy" wrote:

Thank you both so much for the help. I used the degugger and when the line
crashes the variable is defined correctly. Stepping into the code causes
failure at the same line and this is the error message I get:

Runtime error 1004

Microsoft Excel cannot access the file 'C:\Program Files\Common Files\
system\Mapi\1033.' There are several possible reasons:

The file name or path name does does not exist

The file you are trying to open is being used by another program
(Close the document in the other program and try again)

The name of the workbook you're trying to save is the same as the .....(the
rest is cut off in the window can't be seen)

I want the file to save in the users default location - I can't really set a
direct path because users computers are all set up differently and I don't
know what would work for sure on all of them.

Christy ;)


"Vacation's Over" wrote:

Following up on Tom's comment:

In the VBE use the debugger to step through the code and put a watch on your
variable so you can see what value it has when the line crashes.

"Christy" wrote:

Thanks Tom, sorry for being unclear. The code is failing and when click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a variable
that
is a combination of the office and a serial date. I must use this name.
If
the file is closed then re-opened it runs ok and just prompts the user to
overwrite the file.

The name is unique to each week and I would always want to overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error ???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the first time but
if the
user runs it again it hangs up on the .SaveAs line. If anyone can
give me
any insight as to why this is happening and/or suggestions to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With

Then it sounds like you are trying to save a copy of your workbook or
perhaps a new blank workbook on top of the workbook you have just created
and saved - while it is still open. this, of course, would not be allowed.
Can you confirm that two workbooks are open at the point you describe, with
one being your original and one being the book#?



--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
I tried what you said and I get a new workbook left open with the default
name (Book#). When I did a manual saveas on that file it open the save
dialog with Desktop selected prompting me to save?

I tried it several time and it kept opening new workbook and a manual
saveas
was the same on all of them.


"Vacation's Over" wrote:

Christy -
i'll help you diagnose.

put an "exit sub" line right before "with newbook"
run your sub
then try to "save as" manually
and see what the problem is....

Also i forget but there is a way to get to users default folder. Start
a
new post with that specific question and some one will tell you / us.

"Christy" wrote:

Thank you both so much for the help. I used the degugger and when the
line
crashes the variable is defined correctly. Stepping into the code
causes
failure at the same line and this is the error message I get:

Runtime error 1004

Microsoft Excel cannot access the file 'C:\Program Files\Common Files\
system\Mapi\1033.' There are several possible reasons:

The file name or path name does does not exist

The file you are trying to open is being used by another program
(Close the document in the other program and try again)

The name of the workbook you're trying to save is the same as the
......(the
rest is cut off in the window can't be seen)

I want the file to save in the users default location - I can't really
set a
direct path because users computers are all set up differently and I
don't
know what would work for sure on all of them.

Christy ;)


"Vacation's Over" wrote:

Following up on Tom's comment:

In the VBE use the debugger to step through the code and put a watch
on your
variable so you can see what value it has when the line crashes.

"Christy" wrote:

Thanks Tom, sorry for being unclear. The code is failing and when
click
'debug' the saveas line is highlighted.

Christy ;)

"Tom Ogilvy" wrote:

There is a difference between failing/hanging and prompting to
overwrite.
Is you code halting/hanging with an error (failing?)

If you just don't want to be prompted

application.DisplayAlerts = False

NewBook.Save . . .

application.DisplayAlerts = True


--
Regards,
Tom Ogilvy

"Christy" wrote in message
...
Your input was helpful but I am still stuck. Officename is a
variable
that
is a combination of the office and a serial date. I must use
this name.
If
the file is closed then re-opened it runs ok and just prompts
the user to
overwrite the file.

The name is unique to each week and I would always want to
overwite the
file
if it already exists.

Thanks again
Christy ;)


"Vacation's Over" wrote:

first pass creates a file with that name, second pass is
trying to
overwrite
you need to differenciate between SaveAs and Save

Go to J-Walk.com and look at his file exists utility (free
code)

then you need something like this psudo code

If file exists
if newbook.name = Officename (open file has the desired
name0
newbook.save
else (some other file has the name you want)
"You need to descide what to do" rename, kill, error
???
end if
else (File Name not in use)
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal
end if



"Christy" wrote:

Hello all,

I have pasted some of my code below. It runs fine the
first time but
if the
user runs it again it hangs up on the .SaveAs line. If
anyone can
give me
any insight as to why this is happening and/or suggestions
to fix it I
would
be most gratful.

Christy ;)


With newbook.Sheets(1)
.Cells(1).PasteSpecial xlPasteValues, , False,
False
.Columns("A:F").AutoFit
.Range("A2,a19,a36,a53,a70,a87,a104").Select
With Selection
.NumberFormat = "m/d/yyyy"
End With
.Range("a1").Select
Application.CutCopyMode = False
End With

With newbook
.SaveAs FileName:=OfficeName, FileFormat:=xlNormal

'Handle the error if user clicks "NO" at Outlook
security pop-up
On Error Resume Next
.SendMail MyArr, Sheet1.Range("a99").Value
On Error GoTo 0

.Close True
End With