a file opening problem pulling the data from closed Excel with ADO method

[ODSCm]

Hello all,

I am pulling the data from closed excel file to my current excel with ADO connection string for excels and I use the following statement to open that query. Everything is working perfectly until someone opens the sourced excel file at the background during that pulling process. If someone else open the excel file at the same time, when I run the ADO macro, this source excel from which I pull the data, is starting to be opened firstly in my computer as well and then data is pulled to my own excel. I do not want that source file to be opened in any case. I have tested and see that if the excel file is shared format, despite being opened by someone else it is still not be opened with my ADO macro. But I can not apply this solution to every excel. Because they should not be shared file.

rs.open.query, connection, adOpenKeyset, adLockPessimistic
(I have tried all options above code which are optimistic, readonly and so on - there is no change)


Therefore I am asking here what should I have change in my code if there is a solution for it?

 

[6StringJazzer]

I don't have experience using ADO on a file that someone else can open, but the way I would approach this is to make a temporary copy of the file, then do the ADO operation on the temporary file, then Kill the temporary file. Otherwise you are going to get into preventing other users from opening the file which is going to be inconvenient for them and I'm not even sure it's possible.

 

[ODSCm]

I don't have experience using ADO on a file that someone else can open, but the way I would approach this is to make a temporary copy of the file, then do the ADO operation on the temporary file, then Kill the temporary file. Otherwise you are going to get into preventing other users from opening the file which is going to be inconvenient for them and I'm not even sure it's possible.

Hello thank you for answer. How can I make a temporary copy of the file? You mean copy the original file to somewhere else and pull the data from that with ADO? it does not sound a reasonable solutions to me as it requires extra effort for each time when I want to pull data.

 

[6StringJazzer]

How can I make a temporary copy of the file?

Please see the link I provided.

You mean copy the original file to somewhere else and pull the data from that with ADO? it does not sound a reasonable solutions to me as it requires extra effort for each time when I want to pull data.

You do it all in the code. The VBA does the extra effort, not you.

 

[ODSCm]

Please see the link I provided.

You do it all in the code. The VBA does the extra effort, not you.

Thanks I will be searching. I think this will be a good solution. Now only concern to me is the time that will be spent for copying of the excel file before ADO macro works. And I think I need to decide in advance where temporary file will be copied so that I write the address of the excel file in ADO properly beforehand.

Besides I still did not understand why it is requiring opening the file. Because ADO uses the file just as read only to retrieve the data. Any other comments are very welcomed.

 

[6StringJazzer]

I don't think it is requiring opening the file. It sounds like when someone else opens the file, Windows locks the file against other access. But I have not done this same scenario so I'm not sure.