Brad Marks
BradM at blackforestltd.com
Sat Feb 9 11:18:51 CST 2013
Mark, Thanks for sharing your ideas. In the situation that I am dealing with, the users control the Excel file and they update it several times per day. The Access application that I am working on simply needs to read the data from the Excel file. I believe that your approach will work in some situations but I don't think that I will be able to lock cells or password protect the file in this case. Thanks, Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com on behalf of Mark Simms Sent: Sat 2/9/2013 10:04 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Application Obtaining Data from Excel -Another User Has Excel File Opened Just insure all cells are Locked on that worksheet and then pass-word protect it. If the user must make changes, unlock the sheet via a Form's button or check box setting. Keep the form up in vbModeLess mode.....and when it is closed, then pass-word protect the sheet again. This will effectively keep the sheet in a ReadOnly state most of the time. > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto:accessd- > bounces at databaseadvisors.com] On Behalf Of Brad Marks > Sent: Friday, February 08, 2013 4:27 PM > To: Access Developers discussion and problem solving > Subject: [AccessD] Access Application Obtaining Data from Excel - > Another User Has Excel File Opened > > All, > > I am struggling with a small Access 2007 application that obtains data > from an Excel file. > > The Excel file is defined as a Linked Table in Access. There is a > single query which is used via Record Set processing. > > The data is returned perfectly when no one else is using the Excel > file. > > If another user is looking at the Excel file but has not clicked on any > cells, the data is returned to Access properly. > > However, if another user has the Excel file opened and has clicked on a > cell, the Access application runs into a problem. > > The Access VBA code that does the Record Set processing has error > handling code, but the error that is returned is not trapped by the > error handling code. Instead a message is shown that says "This action > cannot be completed because the Microsoft Excel - (the Excel file name) > is busy. > > I would like to either be able to catch this error with the normal > error > handling code > > Or > > Invoke some sort of routine to determine if the Excel file is tied up > before trying to obtain the data via Access. > > Any ideas? > > Thanks, > Brad > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.