Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •