I wrote an Access 2003 VBA routine that reads upwards of 20 Excel files into an Access table (one at a time) using the DOCMD.TransferSpreadsheet method (DAO).
The code works great, except when one of the spreadsheets is open for editing by someone else, at which time I get an error message and my code stops running. I have absolutely no control over this happening, or any way to know ahead of time when it is happening.
I want only to read the spreadsheet data into an Access table, I don't want to write to it, or look at it visually. I don't even really want to open it, in the normal sense, just get in, read from it, and get out.
Is there a way to access the spreadsheet in Read Only mode?
I found a response here that appears to be close:
Private Sub CmdOpenExcel_Click()
'WillR - opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
.Visible = True
Set xlWB = .Workbooks.Open("R:\path\path2\path3\xlfilename.xl s", , False)
(I changed the False to a True to get the spreadsheet in Read Only)
There's a lot I don't understand about this method; for instance, when it opens the spreadsheet, how do I get the data to my access table? My TransferSpreadsheet method just puts it right in there.
Also, how do I close Excel once I've opened it like this? I put the command '.Workbooks.Close' in the With block, and it closes the spreadsheet but not Excel. Every time I exercise the routine, I get another instance of Excel.
This is a strange grouping of users. Mid management puts resource forecast information into the spreadsheets and upper management uses the database for analysis. Lots of users on both sides, very dynamic situation.
Besides, when I start up the database, I don’t know which spreadsheets are going to be read. Maybe one, maybe some, maybe all.
Don't know if this would work but I thought that I would throw it out there.
Is it possible to put in some code to copy all the spreadsheets into a temp folder where you could read them in from there and then delete them when done? I don't know if you can copy an open file or not but if you could this might be a work around that wouldn't take a total overhaul of your app.