Checking for open files in Excel before export (Access 97 Win2k)

Topic

New Reply

Hello again

I’m working on some code to export selected queries to excel, I was quite happy with the code, nicely error trapped I thought…. Then I tried to export the queries to without closing Excel, or the file within Excel and got an error message. This should have been expected, Excel wont let Access over write an open file. How do I either provide an error message saying ‘shut excel’ (or similar).

I think I’ve seen somewhere you can write VBA to react to error messages? If so how? If I can’t do this then how do I check for an open excel file? If I can do that the rest is simple if … then … else stuff.

Thanks for the help

Ian

Reply | Quote

Replies

You can use Automation to see if Excel is running, and if so, check for open workbooks.

For the following code to run, you must set a reference to the Microsoft Excel 8.0 Object Library (assuming that you’re using Excel 97) in Tools/References

Reply | Quote

Hans

Thanks, this is exactly what I thought was possible but couldn’t figure out how to do.

Your comments on the ‘VBA reacting to error messages’ is also what I was on about. If it hadn’t been possible to check the files in Excel then I had hoped to use the error message number to generate a text box saying ‘shut excel please’ or similar.

So, two out of two.

Thank You very much Hans.

Ian

Reply | Quote