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