Avatar of Buck Beasom
Buck Beasom
Flag 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?

Visual Basic ClassicMicrosoft Access

Avatar of undefined
Last Comment
Buck Beasom

8/22/2022 - Mon
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

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?
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.

Jeffrey Coachman

<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"

Your help has saved me hundreds of hours of internet surfing.
Buck Beasom

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.

Buck Beasom

Worked like a charm! Thanks.