I have an Excel file that contains columns with mixed data formats (i.e., the first row heading is text, the remaining values are percentage, the first row heading is text the remaining rows are currency etc.




I am trying to retrieve these values as their actual displayed percentage, currency values via an ADODB connection:



strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & dbPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"



But it isn't working. I am getting their text equivalents e.g, $12.00 is returned as 12
5.00% is returned as .05


Is there a way to get the actual values from the Excel record fields? Thanks


Cross posted at: ADODB Get actual displayed values

Attached Images

Sample Data.png (19.7 KB, 13 views)

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

You are getting numbers which are the actual values. If you want exactly what is displayed (which is produced by cell formatting), that would be text. Why? Use linked table in a query or report and do formatting there.

Why? I thought that would be obvious. I want percentage and currency values in the data source and when I extract that data to use in a Word form, I want the percentage value and currency value (not simply the numbers). Yes, I have used an Access query in the manner you have described, but I am not familiar with Excel queries or how to format them so that their displayed value (not the underlying number) is extracted. I have some homework to do. Thank you.

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Cross-post ADODB Get actual displayed values

This issue is resolved see:
[SOLVED] ADODB Get actual displayed values


The solution is a workaround using and Excel Power Query

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

select format(0.05,"0.00%");
select format(12,"$0.00");

I am not sure what question your reply is supposed to be trying to answer.


I could have one or a thousand records with percentages or currency values. All will not be 0.05. What am I supposed to select how would selecting one item resolve all 1 or 1000?


I think the answer to the question asked is that column formatting applied by Excel or Access determines what is displayed in the columns but does not alter the underlying value. ADODB get the underlying value so there is no way to get the displayed value unless the displayed value is the underlying value. We have done that using the queries.



Thank you just the same.

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/