Hello,
When executing DoCmd.TransferSpreadsheet to an .xls file that's being
used as a datasource for a Word mail merge document, if that Word
document is currently open, the TransferSpreadsheet action fails with
an 'Error 0'. I have tried different methods of trapping that error,
and I still cannot get my app to indicate that the user should exit
Word: I have tried disabling the error handler before DoCmd is called,
looking for an Err object after the call, and then using Err.Raise to
generate a custom error:
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
tblName, xlsPath, , "DP_table"
If Err Then
Err.Raise 811, "Document pump", Err.description & vbCrLf &
"(note: Word may have crashed, leaving the file locked; killing the
WINWORD.EXE task may solve this)."
End If
On Error GoTo error_exit
.... but this does not work: still Error 0. If I do not disable the
error handler, DoCmd never reaches the Err.Raise statement, leaving me
with Error 0 again.
I have tried determining whether or not the Word document is open
(using methods of the Word automation objects) but it didn't work for a
non-admin user (which is a problem).
Please note that I am properly exiting my function before the error
handler (most posts on this board regarding Error 0 were due to this).
Does anyone have any suggestions?
Thanking you kindly in advance,
Robert Waters