How do I import data from a locked Excel file?

[Khriskin]

I have searched the forums (as well as msdn and other websites) and have not been able to find a solution. Any and all suggestions would be greatly appreciated!

Problem
I have two Excel 2003 files from which I need to import/pull data, however these two files are almost always open/in use by the Scheduling Department.

Current Workaround
Currently I have a warning on the user interface that prompts the user to open the Excel spreadsheets before running the import function. This establishes the read-only connection manually prior to the import call.

Question
Is it possible to use TransferSpreadsheet to open the link to the spreadsheets as read-only? If so, how? (I've been digging around in the external file documentations and I can't find anything that looks like it would work.) If not, is there another option for pulling the data from these files? The workaround is functional, but I'd like to get as much of the programs automated as I can.

THANKS! ^_^

[cori25]

Hello...

No, you can not use a transfer spreadsheet for an excel file that is read only.


My suggestion is to re-create the document on your c drive and pull it in to the database like that. It would be best just to have a set time where everyone is aware not to enter the document at a set time and run your automation during this time period.

Other then that, there is no other way around it.

Hope this helps

[Delerna]

I may be missing the point here but what about incorporating the functionality
of the spreadsheet within access and do away with the spreadsheet altogether.
The users then use a front end database to the access file containing the data instead. The front end can be made to look like the spreadsheet.

I don't know your full details so I'm just making a suggestion.

[Khriskin]

I may be missing the point here but what about incorporating the functionality of the spreadsheet within access and do away with the spreadsheet altogether. The users then use a front end database to the access file containing the data instead. The front end can be made to look like the spreadsheet.

I don't know your full details so I'm just making a suggestion.

Ah, if only I could wean them off Excel so easily... *wistful sigh* Actually, that is my end goal (I'm already prying them out of Lotus slowly but surely), but it'll be another year or so before I will be able to convince them the database is easier. :P

[Khriskin]

No, you can not use a transfer spreadsheet for an excel file that is read only.

Darnit. *sighs* Oh well, at least the workaround works for now.

THANKS! ^_^

[NeoPa]

Is it possible that a DOS COPY command could be used on the file even if locked (It seems to work for me)?
You could do a copy, then import the copied file. Delete it when finished.