Access: Trapping an error to see if Excel is open
Posted on 2004-09-24
Hello again experts,
Now I'm trying to learn how to trap errors in Access while loading Excel files.
A user types in search criteria in a textbox, it is passed to a query that creates a table, and then the table is passed to Excel, and then Excel is loaded. If I have already run the code once and the excel file is open, however, I get:
Run-time Error '3010' table already exists ...but everything works fine if the Excel file isn't already loaded.
DoCmd.OpenQuery "qryKSB1SearchMakeTable", acViewNormal, acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblKSB1Search", "\\Jess\DB Project\search_output.xls", False, ""
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open "\\Jess\DB Project\search_output.xls"
XL.Application.Visible = True
Is there some generic code I can use to see if the Excel file is already loaded? If so, I'm thinking it either needs to be closed and reopened unless someone has a fancier idea...