Fix Excel Stock Data Types #VALUE! Error in Visual Basic
Question details
The user encounters a #VALUE! error when querying Excel Stock data types via an external Visual Basic program using the ACE OLEDB provider.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Accessing a macro-enabled workbook with Stock data types using Visual Studio and the ACE OLEDB provider.
- Observed behavior
- Rich data types like Stock data return a #VALUE! error instead of the actual values when retrieved externally.
Ensure you have an active Microsoft 365 subscription, are signed into your account, and have your editing language set to English, as these are prerequisites for Stock data types to function natively.
Use Excel Automation (Interop) Instead of ACE OLEDB
ACE OLEDB does not fully support modern rich data types. Use Excel Interop to read the displayed values directly from the running application.
The ACE OLEDB provider was designed for traditional tabular data and does not fully recognize Microsoft 365's connected data types (like Stocks and Geography). When it attempts to read these complex objects, it defaults to a #VALUE! error.
To bypass this, you can use Excel COM Automation (Interop) in your Visual Basic application, which interacts with the Excel application itself rather than just parsing the file structure.
In Visual Studio, go to Project > Add Reference > COM tab, and select 'Microsoft Excel Object Library'.
Create a new instance of the Excel Application object in your VB code to open the target workbook in the background.
Instead of querying via SQL, read the 'Text' or 'Value2' property of the specific range containing the Stock data to extract the resolved string rather than the underlying data type object.

Convert Stock Data to Static Text in Excel
If you must use ACE OLEDB for performance or environment reasons, convert the linked data to static text within Excel first.
Try WPS Office for Seamless Spreadsheet Management
Since external database querying of proprietary connected data types often causes compatibility issues, consider a more lightweight, universally compatible office suite. WPS Office provides excellent performance for managing traditional datasets without the overhead of complex, proprietary data objects that can break external scripts.
- 1. Download and Install: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Open Your Workbooks: Launch WPS Spreadsheets and directly open your existing .xlsx or .xls files without altering their formatting.
- 3. Manage Data Easily: Use standard formulas and data connections that are widely supported across standard database querying tools.

Frequently Asked Questions
Why does ACE OLEDB not support Excel Stock data types?
The ACE OLEDB engine is an older technology built for standard relational tabular data. Microsoft 365's Stock data types are complex, connected objects (rich data types) that the OLEDB parser cannot interpret, resulting in a #VALUE! error when queried.
Can I fix the #VALUE! error by updating my OLEDB drivers?
No, currently no version of the Microsoft Access Database Engine (ACE OLEDB) fully supports reading connected rich data types from Excel. You must either use Excel Automation (Interop) or convert the data to static text.
Are there prerequisites for using Stock data types natively in Excel?
Yes, to use Stock data types within Excel itself, you must have an active Microsoft 365 subscription, be signed in to your account, have a reliable internet connection, and ideally use English as the primary editing language for full market coverage.




