 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2006Using ADO to import Excel into Access Read Only - while file is open
Here's what I've got: I'm trying to grab some data out of an Excel spreadsheet and bring it into Access. I'm able to do it, but only if the Excel spreadsheet is not opened by any other users. I am connecting to the spreadsheet via ADO (Jet OLE 4.0 Provider). If the file is opened for editing by another user, I receive the "The Microsoft Jet database engine cannot open the file ". It is already opened exclusively by another user, or you need permission to view its data. This spreadsheet is used by many people in the work group, so it is very possible that it will be opened at any given time - I'd like to still be able to grab data out of it "Read-Only"... Is it possible to do this with ADO? Is there an "Extended Properties" parameter that must be set, or maybe a different addition to the connection string? Any help would be greatly appreciated! Thanks! -Terry > Here's what I've got: I'm trying to grab some data out of an Excel > spreadsheet and bring it into Access. I'm able to do it, but only if [quoted text clipped - 11 lines] > Any help would be greatly appreciated! Thanks! > -Terry Could you, or I might say, can you link that spreadsheet to your Access Database? File/GetExternalData/Link? See also TransferSpreadsheet Well, linking the spreadsheet isn't really an option for me, because I have to extract the data at a given point - programmatically. The TransferSpreadsheet method only works if I use automation to open the spreadsheet first. I cannot do this, because there is a chance that the user will have that same spreadsheet open on his/her desktop (Excel cannot open the same file on one machine). I'm starting to think that what I'm trying to do is not possible, if ADO cannot pull data from the spreadsheet while it's opened exclusively by a different user. Thanks for the reply, salad! > Well, linking the spreadsheet isn't really an option for me, because I > have to extract the data at a given point - programmatically. Well, if the spreadsheet is linked, it's like a table. But I'm missing the point from what you say. > The TransferSpreadsheet method only works if I use automation to open > the spreadsheet first. I cannot do this, because there is a chance [quoted text clipped - 3 lines] > data from the spreadsheet while it's opened exclusively by a different > user. Thanks for the reply, salad! How about this. Do a FileCopy of the spreadsheet. Now I'm not sure if "new" data will be captured during a filecopy. I doubt it...you'd probably get the data from the last time the sheet was saved/closed. Anyway, if you can live with that, filecopying it to another file and then processing the new one should work. How's that? It's a kludge, it's a possibility... See Dir Kill FileCopy in help for using this concept. I think you're on to something here - looks like I need to find out now if I can copy the file while it's already open. If that's possible, then I'll do a file copy to a temp directory so the user can import that data. What I meant by not being able to link to the spreadsheet is that I am only needing portions of the spreadsheet (namely only one column - starting at row 4). By linking the spreadsheet as a table, I could get the data, but I am not sure how to make sure that I ignore the top 3 rows in the spreadsheet (they just become records in the linked table). That's why I don't think I can use a linked table. However, I am going to play with this over the weekend also. Thanks for your help, salad - and if you think of any other possible solutions, please don't hesitate to let me know - I really appreciate the help! Thanks again, and have a great weekend! Oh, also - another reason linking wouldn't work for me is because there are about 12 "worksheets" within the spreadsheet file, and this would require 12 linked tables. No big deal, but another worksheet can be added anytime, and I need to be able to determine how many worksheets are within the spreadsheet at run-time and ensure that I import the data from all of them. That's why I thought ADO would be the best approach, because you can use the OpenSchema property to get the list of worksheets out of an excel sheet. BUT - the first approach you suggested (copying the file so the user can have the same file open simultaneously) would probably work with automation - as long as I can copy the file while it's already open. I'm going to go test that now. Thanks again! > Oh, also - another reason linking wouldn't work for me is because there > are about 12 "worksheets" within the spreadsheet file, and this would [quoted text clipped - 8 lines] > copy the file while it's already open. I'm going to go test that now. > Thanks again! OK. I guess I'm getting hung up on the word ADO. What about VBA? If you can use automation then perhaps you can work it out. Go to http://groups.google.com/advanced_search?hl=en and enter phrases like Excel object and in the groups enter *access* There's another reference site you might want to visit. http://www.mvps.org On the right hand side there's a frame. Scroll down and see Excel links. Of course, if you can't use VBA then above is useless. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.