In my previous blog post, I have demonstrated how to use Microsoft.ACE.OLEDB.12.0 to join two worksheets. Today, I am going to reproduce an issue where the data provider reads saved data from file instead of live data from memory and show you a way to work around it.
Let’s reuse the Excel file we’ve created in the previous blog post. I will start by going through the normal behaviour.
8. You should be able to see something like the screen shot below.
We have successfully reproduced the issue. The data provider failed to read the most recent change from Sheet2. The read-only property on the file has a profound impact on the behaviour of the data provider.
The observed behaviour sort of makes sense as the data source was not allowed to be modified. However, if left unhandled, unsuspecting users could be presented with misleading results.
One way of working around the issue is to save the current workbook to a temp folder and make the data provider read from the temp file. Let me show you how.
2. Now you should be able to change the data in either Sheet1 and Sheet2 and display the newly changed values in Sheet3 using the macro.