problem with saving a workbook using VBA with the file is open bef

[Guest]

Hi experts,
My program saves a Excel file after some operations. It works well normally.
However, If the file is opened before running the VBA, It fails and provides
such messages like "The file aleady exist, do you want to replace it"?

I think somehow the program is working on a copy of that file if the file is
opened by someone. How can I ignore such annoying message using VBA? I have
to deal with this case since it is normal that the user want to see the file
while the program is running.

Thanks a lot and regards
Shu

 

[STEVE BELL]

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will have to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module that is
automaticatically
firing when the workbook is opened. Check this out and design it to fire
only when you want it to
fire.

hth...

 

[Guest]

Hi Steve,
What do you mean "It also sounds like there is some code in the
"ThisWorkbook" module that is automaticatically firing when the workbook is
opened."

I run my code in Outlook automatically at a fixed interval, but there is no
code fired in "ThisWorkbook".

By using your code, the alert message disappears but the problem still
existing. The new change of the file is not saved as it is supposed to.

My program called a function

Private Sub logInfo(ByRef xlt As Excel.Worksheet, ByVal r As Integer, _
ByVal c1 As String, ByRef arrC() As String)
With xlt
..Cells(r, 7) = arrC(7)
..Cells(r, 8) = arrC(8)

xlt.Range("A:R").WrapText = True
End With
End Sub

The changes can not showed in the opened file when I traced it. I tried to
use byval to pass the parameter xlt, still failed.

Do you see anything is wrong.

Thanks a lot for your help.

Shu

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will have to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module that is
automaticatically
firing when the workbook is opened. Check this out and design it to fire
only when you want it to
fire.

hth...

--
steveB

Remove "AYN" from email to respond

Hi experts,
My program saves a Excel file after some operations. It works well
normally.
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace it"?

I think somehow the program is working on a copy of that file if the file
is
opened by someone. How can I ignore such annoying message using VBA? I
have
to deal with this case since it is normal that the user want to see the
file
while the program is running.

Thanks a lot and regards
Shu

 

[STEVE BELL]

Without seeing your code - it is difficult to say what is happening. You
mention thatWhich sounds like an event macro is firing when the workbook is opened...
Event code is
found in a worksheet module or in the "ThisWorkbook" module.

And I did say that my code would stop the messages, but wouldn't stop the
code...

I don't see where your code is getting values for the variables. Sounds
like your code is
being called from another macro. You'll have to look at how your variables
are being passed.

Seems like I don't really understand your problem. Sorry...
--
steveB

Remove "AYN" from email to respond

Hi Steve,
What do you mean "It also sounds like there is some code in the
"ThisWorkbook" module that is automaticatically firing when the workbook
is
opened."

I run my code in Outlook automatically at a fixed interval, but there is
no
code fired in "ThisWorkbook".

By using your code, the alert message disappears but the problem still
existing. The new change of the file is not saved as it is supposed to.

My program called a function

Private Sub logInfo(ByRef xlt As Excel.Worksheet, ByVal r As Integer, _
ByVal c1 As String, ByRef arrC() As String)
With xlt
.Cells(r, 7) = arrC(7)
.Cells(r, 8) = arrC(8)

xlt.Range("A:R").WrapText = True
End With
End Sub

The changes can not showed in the opened file when I traced it. I tried to
use byval to pass the parameter xlt, still failed.

Do you see anything is wrong.

Thanks a lot for your help.

Shu

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will have to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module that
is
automaticatically
firing when the workbook is opened. Check this out and design it to fire
only when you want it to
fire.

hth...

--
steveB

Remove "AYN" from email to respond

Hi experts,
My program saves a Excel file after some operations. It works well
normally.
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace it"?

I think somehow the program is working on a copy of that file if the
file
is
opened by someone. How can I ignore such annoying message using VBA? I
have
to deal with this case since it is normal that the user want to see the
file
while the program is running.

Thanks a lot and regards
Shu

 

[Guest]

Sorry I did not make my problem clear. I will do more test to decrease the
problem range.

Thanks very much.
Shu

“STEVE BELLâ€

Without seeing your code - it is difficult to say what is happening. You
mention thatWhich sounds like an event macro is firing when the workbook is opened...
Event code is
found in a worksheet module or in the "ThisWorkbook" module.

And I did say that my code would stop the messages, but wouldn't stop the
code...

I don't see where your code is getting values for the variables. Sounds
like your code is
being called from another macro. You'll have to look at how your variables
are being passed.

Seems like I don't really understand your problem. Sorry...
--
steveB

Remove "AYN" from email to respond

Hi Steve,
What do you mean "It also sounds like there is some code in the
"ThisWorkbook" module that is automaticatically firing when the workbook
is
opened."

I run my code in Outlook automatically at a fixed interval, but there is
no
code fired in "ThisWorkbook".

By using your code, the alert message disappears but the problem still
existing. The new change of the file is not saved as it is supposed to.

My program called a function

Private Sub logInfo(ByRef xlt As Excel.Worksheet, ByVal r As Integer, _
ByVal c1 As String, ByRef arrC() As String)
With xlt
.Cells(r, 7) = arrC(7)
.Cells(r, 8) = arrC(8)

xlt.Range("A:R").WrapText = True
End With
End Sub

The changes can not showed in the opened file when I traced it. I tried to
use byval to pass the parameter xlt, still failed.

Do you see anything is wrong.

Thanks a lot for your help.

Shu

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will have to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module that
is
automaticatically
firing when the workbook is opened. Check this out and design it to fire
only when you want it to
fire.

hth...

--
steveB

Remove "AYN" from email to respond
Hi experts,
My program saves a Excel file after some operations. It works well
normally.
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace it"?

I think somehow the program is working on a copy of that file if the
file
is
opened by someone. How can I ignore such annoying message using VBA? I
have
to deal with this case since it is normal that the user want to see the
file
while the program is running.

Thanks a lot and regards
Shu

 

[STEVE BELL]

No problem!

Let us know when you have more info...

--
steveB

Remove "AYN" from email to respond

Sorry I did not make my problem clear. I will do more test to decrease the
problem range.

Thanks very much.
Shu

"STEVE BELL"

Without seeing your code - it is difficult to say what is happening. You
mention that

However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace
it"?

Which sounds like an event macro is firing when the workbook is opened...
Event code is
found in a worksheet module or in the "ThisWorkbook" module.

And I did say that my code would stop the messages, but wouldn't stop the
code...

I don't see where your code is getting values for the variables. Sounds
like your code is
being called from another macro. You'll have to look at how your
variables
are being passed.

Seems like I don't really understand your problem. Sorry...
--
steveB

Remove "AYN" from email to respond

Hi Steve,
What do you mean "It also sounds like there is some code in the
"ThisWorkbook" module that is automaticatically firing when the
workbook
is
opened."

I run my code in Outlook automatically at a fixed interval, but there
is
no
code fired in "ThisWorkbook".

By using your code, the alert message disappears but the problem still
existing. The new change of the file is not saved as it is supposed to.

My program called a function

Private Sub logInfo(ByRef xlt As Excel.Worksheet, ByVal r As Integer, _
ByVal c1 As String, ByRef arrC() As String)
With xlt
.Cells(r, 7) = arrC(7)
.Cells(r, 8) = arrC(8)

xlt.Range("A:R").WrapText = True
End With
End Sub

The changes can not showed in the opened file when I traced it. I tried
to
use byval to pass the parameter xlt, still failed.

Do you see anything is wrong.

Thanks a lot for your help.

Shu

:

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will have
to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module
that
is
automaticatically
firing when the workbook is opened. Check this out and design it to
fire
only when you want it to
fire.

hth...

--
steveB

Remove "AYN" from email to respond
message
Hi experts,
My program saves a Excel file after some operations. It works well
normally.
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace
it"?

I think somehow the program is working on a copy of that file if the
file
is
opened by someone. How can I ignore such annoying message using VBA?
I
have
to deal with this case since it is normal that the user want to see
the
file
while the program is running.

Thanks a lot and regards
Shu

 

[Guest]

I finally find the reason. It is because I should first use
getobject(,"Excel.application")
instead of
createobject("Excel.application").

When there is already an work sheet open, the program create another Excel
application, which causing confliction. If I use getobject first, this would
not happen.

Thanks Steve's reply, without it I can not get this problem solved fast.

Shu


“STEVE BELLâ€ç¼–写：

No problem!

Let us know when you have more info...

--
steveB

Remove "AYN" from email to respond

Sorry I did not make my problem clear. I will do more test to decrease the
problem range.

Thanks very much.
Shu

"STEVE BELL"

Without seeing your code - it is difficult to say what is happening. You
mention that
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace
it"?
Which sounds like an event macro is firing when the workbook is opened...
Event code is
found in a worksheet module or in the "ThisWorkbook" module.

And I did say that my code would stop the messages, but wouldn't stop the
code...

I don't see where your code is getting values for the variables. Sounds
like your code is
being called from another macro. You'll have to look at how your
variables
are being passed.

Seems like I don't really understand your problem. Sorry...
--
steveB

Remove "AYN" from email to respond
Hi Steve,
What do you mean "It also sounds like there is some code in the
"ThisWorkbook" module that is automaticatically firing when the
workbook
is
opened."

I run my code in Outlook automatically at a fixed interval, but there
is
no
code fired in "ThisWorkbook".

By using your code, the alert message disappears but the problem still
existing. The new change of the file is not saved as it is supposed to.

My program called a function

Private Sub logInfo(ByRef xlt As Excel.Worksheet, ByVal r As Integer, _
ByVal c1 As String, ByRef arrC() As String)
With xlt
.Cells(r, 7) = arrC(7)
.Cells(r, 8) = arrC(8)

xlt.Range("A:R").WrapText = True
End With
End Sub

The changes can not showed in the opened file when I traced it. I tried
to
use byval to pass the parameter xlt, still failed.

Do you see anything is wrong.

Thanks a lot for your help.

Shu

:

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will have
to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module
that
is
automaticatically
firing when the workbook is opened. Check this out and design it to
fire
only when you want it to
fire.

hth...

--
steveB

Remove "AYN" from email to respond
message
Hi experts,
My program saves a Excel file after some operations. It works well
normally.
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace
it"?

I think somehow the program is working on a copy of that file if the
file
is
opened by someone. How can I ignore such annoying message using VBA?
I
have
to deal with this case since it is normal that the user want to see
the
file
while the program is running.

Thanks a lot and regards
Shu

 

[STEVE BELL]

Shu,

Don't know what I said - but congratulations for finding a solution!

--
steveB

Remove "AYN" from email to respond

I finally find the reason. It is because I should first use
getobject(,"Excel.application")
instead of
createobject("Excel.application").

When there is already an work sheet open, the program create another Excel
application, which causing confliction. If I use getobject first, this
would
not happen.

Thanks Steve's reply, without it I can not get this problem solved fast.

Shu


"STEVE BELL"??:

No problem!

Let us know when you have more info...

--
steveB

Remove "AYN" from email to respond

Sorry I did not make my problem clear. I will do more test to decrease
the
problem range.

Thanks very much.
Shu

"STEVE BELL"
Without seeing your code - it is difficult to say what is happening.
You
mention that
However, If the file is opened before running the VBA, It fails and
provides
such messages like "The file aleady exist, do you want to replace
it"?
Which sounds like an event macro is firing when the workbook is
opened...
Event code is
found in a worksheet module or in the "ThisWorkbook" module.

And I did say that my code would stop the messages, but wouldn't stop
the
code...

I don't see where your code is getting values for the variables.
Sounds
like your code is
being called from another macro. You'll have to look at how your
variables
are being passed.

Seems like I don't really understand your problem. Sorry...
--
steveB

Remove "AYN" from email to respond
message
Hi Steve,
What do you mean "It also sounds like there is some code in the
"ThisWorkbook" module that is automaticatically firing when the
workbook
is
opened."

I run my code in Outlook automatically at a fixed interval, but
there
is
no
code fired in "ThisWorkbook".

By using your code, the alert message disappears but the problem
still
existing. The new change of the file is not saved as it is supposed
to.

My program called a function

Private Sub logInfo(ByRef xlt As Excel.Worksheet, ByVal r As
Integer, _
ByVal c1 As String, ByRef arrC() As String)
With xlt
.Cells(r, 7) = arrC(7)
.Cells(r, 8) = arrC(8)

xlt.Range("A:R").WrapText = True
End With
End Sub

The changes can not showed in the opened file when I traced it. I
tried
to
use byval to pass the parameter xlt, still failed.

Do you see anything is wrong.

Thanks a lot for your help.

Shu

:

There may be a lot happening here - so proceed with caution.

To turn off those messages - surround the code with:
Application.DisplayAlerts = False
[ code ]
Application.DisplayAlerts = True.

But this won't stop the code from executing. To do that you will
have
to
build
checks and balances to determine whether or not to run the code.

It also sounds like there is some code in the "ThisWorkbook" module
that
is
automaticatically
firing when the workbook is opened. Check this out and design it
to
fire
only when you want it to
fire.

hth...

--
steveB

Remove "AYN" from email to respond
message
Hi experts,
My program saves a Excel file after some operations. It works
well
normally.
However, If the file is opened before running the VBA, It fails
and
provides
such messages like "The file aleady exist, do you want to replace
it"?

I think somehow the program is working on a copy of that file if
the
file
is
opened by someone. How can I ignore such annoying message using
VBA?
I
have
to deal with this case since it is normal that the user want to
see
the
file
while the program is running.

Thanks a lot and regards
Shu