troubleshooting Question

Test For File Open In Access 2007

Avatar of Buck Beasom
Buck BeasomFlag for United States of America asked on
Microsoft AccessVisual Basic Classic
6 Comments1 Solution412 ViewsLast Modified:
I use Excel files to generate reports from my Access applications as they are more useful than the reports that come out of Access. Generally I build templates with the desired column widths and other desired formatting (for cells where it doesn't change). To protect the template files I do it like this:

If Dir(g_strDE_SnapshotTemplate_File) <> "" Then
        Kill (g_strDE_SnapshotTemplate_File)
    End If
    objExcel.Workbooks.Open modGlobal.g_strDE_SnapshotTemplate
    objExcel.ActiveWorkbook.SaveAs modGlobal.g_strDE_SnapshotTemplate_File

All those "g_str" items are global constants that store the path and file name.

The problem is that sometimes users try to run the report a second time without closing the first file. This throws a runtime error.

In VB 2010 I could manage this with a "Try/Catch" method that gave the user a message box if the attempt to open the file errored out.

Is there a way to test if that file is open in Access so I can give the user a Message Box warning and exit the subroutine rather than throwing the run time error?

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros