Brad Marks
BradM at blackforestltd.com
Mon Feb 11 08:07:20 CST 2013
All, Thanks to everyone who posted their ideas and insights on this problem. I now have several more potential approaches for future Access/Excel adventures. For the current problem that I am dealing with, it looks like simply making a copy of the Excel file before the data is read (via an Access linked table), will work just fine. Thanks again, Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com on behalf of Mark Simms Sent: Sat 2/9/2013 3:53 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Application Obtaining Data fromExcel -Another User Has Excel File Opened Ah...a better solution given this is situation to detect when critical sheet changes are made and then save an entire COPY of the workbook when that occurs. Then set that file's attributes to Hidden and ReadOnly. This will require some coding on the Excel side. Alternatively, you could do this via OLE automation from Access...just open their workbook ReadOnly and then save it out hidden from their view. Now I wonder: Can't you just do a File Copy here ? > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto:accessd- > bounces at databaseadvisors.com] On Behalf Of Brad Marks > Sent: Saturday, February 09, 2013 12:19 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Access Application Obtaining Data from Excel - > Another User Has Excel File Opened > > 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. > -- 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.