Test For File Open In Access 2007

Buck_Beasom
Buck_Beasom used Ask the Experts™
on
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?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<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.>>

 You want in-line error handling:

On Error Resume Next
Err = 0
'attempt operation

 If Err<>0 then
   ' Error occured
 End If

 On Error goto <handler>

Jim.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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_BeasomDatabase Designer

Author

Commented:
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.

Thanks.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

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

JeffCoachman
Buck_BeasomDatabase Designer

Author

Commented:
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.

Thanks.
Buck_BeasomDatabase Designer

Author

Commented:
Worked like a charm! Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial