Link to home
Avatar of Buck Beasom
Buck BeasomFlag for United States of America

asked on

Test For File Open In Access 2007

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?

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Just curious...

<generate reports from my Access applications as they are more useful than the reports that come out of Access.>

Can you define "more useful"

In other words, ...what is this Excel "report" doing that cannot be done in Access?
Avatar of Buck Beasom


A report generated in Access does not create a file that can be manipulated or emailed. I have found that my internal customers much prefer a report populated into Excel where sorting, calculating, additional formating, etc. can be done "on the fly."

I've actually reached a point where I use Access and Excel as a single "super app." This allows creation of Excel templates that users can populate for later import to Excel, and also allows me to leverage the database power of Access to generate an end product that is an Excel workbook.

I will try the solution above in a few minutes and hopefully clear it and award the points.

<A report generated in Access does not create a file that can be manipulated or emailed.>
"Does Not", ..or are you saying that you don't know how?

You can output almost any type of Access data to a spreadsheet.
But to export an Access report, you would use code like this:

DoCmd.OutputTo acOutputReport, "YourReport", acFormatXLS, "C:\YourFolder\Yourfile.xls"

I know you can output a report to Excel. But it does not format the way I want (at least it didn't when I was using 2003, and once I mastered the manipulation of Excel from within Access I abandoned Access report usage.) Some other reasons I use this approach:

1) As part of the report setup, I can rename tabs based on user selected criteria for the current instance of the report.

2) Quite often I am using Access as a rapid deployment development tool to solve an immediate productivity or data management problem. This is usually an iterative process when I whip something up, give it to a few users who then come back with additional needs or wishes. When the Access application stabilizes - if it is going to be used going forward - I will then re-develop it in VB 2010. This improves performance, security and eliminates the need for the user to have all of the unused Access functionality draining computer resources. Code used to assemble the Excel reports can be migrated into the VB 2010 application with very little additional labor.

Worked like a charm! Thanks.