error on excel export if file is already open

[Guest]

Hi

When I export to Excel, if the Excel file is already open, I get the error
message:
Table <name> already exists. (Error 3010).
I'm new to exporting..... is there any way this error can be avoided (other
than by having the Excel file closed?) Is it "safe" to just customise the
error message #3010 to something that says, "Please ensure the Excel file is
closed before exporting?" to alert the user to the problem and work around it
this way?

thanks
rich

 

[Guest]

Hi, Rich.

The safest method is to alert the user that the file cannot be overwritten
because it is currently in use. For example, in your error handler, try:

ErrHandler:

If (Err.Number = 3010) Then
MsgBox "The Excel file is currently open. Please ensure" & _
vbCrLf & "that the file is closed before exporting.", _
vbCritical + vbOKOnly, "Cannot Export!"
Else
MsgBox "Error in ExportBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
End If

Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hi again Gunny

I have tried your code which works nicely.
However, I noticed that when the Excel file is open and the user tries to
export (ie when this error occurs), the Excel file then appears to have been
wiped completely of all data and appears blank! When the user tries to close
the Excel file, they are asked if they want to save the changes and if they
click no, the file is intact when re-opened.
Naturally, this is not the most user-friendly experience! This situation
occurs both with and without your error code (ie is native behaviour in this
situation.) Do you know a way around this so that users don't get an
unneccessary shock?! If there is no way around, as a last resort I will just
have to include else in the error message something like, "The Excel file
will now appear blank but your data hasn't been deleted. Just click 'don't
save changes' when you close the file." Not very professional!

PS I love the layout/design of your messagebox - looks totally professional.
Where can I learn more about icons/ layout etc of message boxes?

rich

 

[Guest]

Do you know a way around this so that users don't get an

unneccessary shock?! If there is no way around, as a last resort I will just
have to include else in the error message something like, "The Excel file
will now appear blank but your data hasn't been deleted. Just click 'don't
save changes' when you close the file." Not very professional!

I've only had one user experience this phenomenon and it was indeed a shock
the first time she saw it happen. I explained to her that Excel is not a
multiuser application and Microsoft's penalty for attempting to overwrite the
file she currently had open was to blank the spreadsheet (but not overwrite
the current file, because she had it locked before Access could do so). (And
no, it's not an intentional penalty on Microsoft's programmers' part. It's
an unintended side effect that we can't avoid if the Excel file is open
during the export.)

I asked her if she wanted a reminder in the error message that the export
failed because she forgot to close the spreadsheet first. Something like:
"Export Failed! Please close the Excel file now, but don't attempt to save
the blank spreadsheet. Then try exporting again." She opted for the message
I suggested to you, because it didn't indicate that _she_ was the one who
forgot to close the spreadsheet first -- only that the file was currently
open, and she'd have to click the button again if she wanted to export the
current data to Excel.

As long as the users know what's going on and don't get blamed for their
slight boo-boos, they'll be happy to live with this type of side effect.
Your alternative is to use the Windows API to check whether the current user
has this file open in Excel and, if so, to close it without closing all other
files open in Excel. And if another user has the Excel file open, to do
exactly what you're currently doing: alert the user that the file is open
and the data wasn't exported.

The most cost-effective solution is to just alert the user, and not try to
fix the problem that may not even be the user's fault.

Where can I learn more about icons/ layout etc of message boxes?

Try:

http://msdn.microsoft.com/archive/d...e/en-us/office97/html/output/F1/D6/S5B272.asp

The information about the @ sign and bold text refers to Access 97 and
earlier versions, not Access 2000 and later versions.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks for the comprehensive reply.
rich

I've only had one user experience this phenomenon and it was indeed a shock
the first time she saw it happen. I explained to her that Excel is not a
multiuser application and Microsoft's penalty for attempting to overwrite the
file she currently had open was to blank the spreadsheet (but not overwrite
the current file, because she had it locked before Access could do so). (And
no, it's not an intentional penalty on Microsoft's programmers' part. It's
an unintended side effect that we can't avoid if the Excel file is open
during the export.)

I asked her if she wanted a reminder in the error message that the export
failed because she forgot to close the spreadsheet first. Something like:
"Export Failed! Please close the Excel file now, but don't attempt to save
the blank spreadsheet. Then try exporting again." She opted for the message
I suggested to you, because it didn't indicate that _she_ was the one who
forgot to close the spreadsheet first -- only that the file was currently
open, and she'd have to click the button again if she wanted to export the
current data to Excel.

As long as the users know what's going on and don't get blamed for their
slight boo-boos, they'll be happy to live with this type of side effect.
Your alternative is to use the Windows API to check whether the current user
has this file open in Excel and, if so, to close it without closing all other
files open in Excel. And if another user has the Excel file open, to do
exactly what you're currently doing: alert the user that the file is open
and the data wasn't exported.

The most cost-effective solution is to just alert the user, and not try to
fix the problem that may not even be the user's fault.


Try:

http://msdn.microsoft.com/archive/d...e/en-us/office97/html/output/F1/D6/S5B272.asp

The information about the @ sign and bold text refers to Access 97 and
earlier versions, not Access 2000 and later versions.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

You're welcome.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.