Excel filter limit of 10000 unique items

A few days ago I was working on an Excel spreadsheet and was looking for a value in the filter list. To my surprise I wasn’t able to find the entry which I knew must be there in the sheet. After locating the entry to prove I wasn’t dreaming, my usual curiosity kicked in.

A quick search revealed that it was a fairly well documented limitation. Now I would like to demonstrate a way to reproduce the scenario.

1. Open a new Excel work sheet, fill an entire column with 1.
image image

This can be achieved quite efficiently using a few short cuts.
a) Select cell A2.
b) Press Ctrl + Shift + Down Arrow (to select the entire range)
c) Type 1.
d) Press Ctrl + Enter (to fill the entire range)

2. Replace the ones with sequential numbers.
imageimage
a) Type the number 2 in cell A3. This establishes the pattern of a sequence.
b) Select range A2:A3.
c) Double click the little black square at the bottom left corner of the selection box (to fill the entire range with sequential numbers)

3. Turn on filtering.
image
a) Press Alt + A + T (I am using Excel 2007)

4. Confirm the filter limit.
a) Click on the down arrow to open the filter window.
b) Scroll down to the bottom.
You should only see 10000 down the bottom.

–oo00oo–
For more info, please refer to the following links:
Not all list items are displayed in the AutoFilter list in Excel

Microsoft.ACE.OLEDB.12.0 reads saved data from file instead of live data from memory

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.

1. Open the workbook, if there is data already in Sheet3, clear the content first.
image

2. Run macro to populate Sheet3.
image

3. You should see something like the screen shot below.
image

4. Let’s change LastName in Sheet2 from Bao to Bad and rerun the macro.
image
Everything is so far so good. The data provider is reading live data as expected.

5. Now we will do something to change the behaviour. Save the workbook, and set the file attribute to Read-only.
image

6. Open the workbook again and change LastName in Sheet2 back to Bao.
image

7. Clear Sheet3 and run macro.
image
Depending on your environment, you may or may not get the prompt. If you are prompted with the above dialog box, change Open Mode to DB_MODE_READ.

8. You should be able to see something like the screen shot below.
image
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.

1. Add the additional code to save to a temp file and read from it:
image
a) Construct the temp file name.
b) Save the workbook first.
c) Read from the temp file.

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.

Join two worksheets using Microsoft.ACE.OLEDB.12.0

Today, I am going to demonstrate how to join two Excel worksheets using the Access database engine – Microsoft.ACE.OLEDB.12.0. The environment I am using is Excel 2007 on Windows Vista.

1. Open a new Excel workbook. In Sheet1, enter the data as shown below: image

2. In Sheet2, enter the data as shown below:
image

3. Add the VBA code in Sheet3 to join the data from Sheet1 and Sheet2.
image
Tips: Press Alt + F11 to get to the Visual Basic editor.

4. Run the macro in Sheet3.
image

5. You should see something like the screen shot below.
image

A few things to note if you are not able to get the above example to work:

  • Make sure the connection string is prefixed with “OLEDB;”.
  • Make sure INNER JOIN is specified instead of just JOIN.
  • Make sure spaces are inserted in the appropriate places.

Whilst working with Microsoft.ACE.OLEDB.12.0, I have encountered an issue where the data provider reads saved data from file instead of live data from memory. In my next blog post, I will present a solution to the issue.

–oo00oo–

For more info, please refer to the following links:
Data Programming with Microsoft Access 2010
Microsoft.ACE.OLEDB.12.0 doesn’t want to read my “live” .xlsb datas, only the last saved one, to the countrary of Microsoft.Jet.OLEDB.4.0 …. is this a bug ?