U
u158291
Guest
Hello,
The scenario:
I am using Access 97 and VBA. I have a form that is used to import excel files into access tables. The user sees a sample of the sheets and cells on the excel file so they choose which column contains which field. This is because the excel files could be sent to us with the required data in any of the columns.
There is also a button that will allow the user to view the excel file and because they can then close the file I handle the beforeclose of the workbook using with events and cancel that event.
I declare the app and workbook as
Public v_excel_app As New Excel.Application
Public WithEvents v_workbook As Excel.Workbook
I set it on loading of the form with
Set v_workbook = v_excel_app.Workbooks.Open(v_file_name)
The v_excel_app remains hidden, I only set visible to true when the user clicks the 'view' button.
I close the workbook and quit v_excel_app at a later stage in the code.
My Problem:
This all works perfectly fine except when the same excel file is open from Windows Explorer. If the excel app is hidden it doesn't appear to have done anything however this seems to confuse the v_workbook object and I get the following error message only when I try to do something with the object like loop through all the sheets or close it (with code).
Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.
Any help or advice on how to either reconnect the workbook or stop the user opening the file via windows explorer would be welcome, I have been searching on the net for while but to no avail.
Thank you
Mark
The scenario:
I am using Access 97 and VBA. I have a form that is used to import excel files into access tables. The user sees a sample of the sheets and cells on the excel file so they choose which column contains which field. This is because the excel files could be sent to us with the required data in any of the columns.
There is also a button that will allow the user to view the excel file and because they can then close the file I handle the beforeclose of the workbook using with events and cancel that event.
I declare the app and workbook as
Public v_excel_app As New Excel.Application
Public WithEvents v_workbook As Excel.Workbook
I set it on loading of the form with
Set v_workbook = v_excel_app.Workbooks.Open(v_file_name)
The v_excel_app remains hidden, I only set visible to true when the user clicks the 'view' button.
I close the workbook and quit v_excel_app at a later stage in the code.
My Problem:
This all works perfectly fine except when the same excel file is open from Windows Explorer. If the excel app is hidden it doesn't appear to have done anything however this seems to confuse the v_workbook object and I get the following error message only when I try to do something with the object like loop through all the sheets or close it (with code).
Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.
Any help or advice on how to either reconnect the workbook or stop the user opening the file via windows explorer would be welcome, I have been searching on the net for while but to no avail.
Thank you
Mark
