ADODB Get actual displayed values

[gmaxey]

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:
Sample Data.jpg

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: https://www.msofficeforums.com/excel...al-values.html

[June7]

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.

[gmaxey]

June7

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.

[June7]

Okay, pulling into Word (my Access-centric brain was thinking Access link since your OP did not mention Word).

Not familiar with Word form. Can you do formatting in it?

Use Format() function in query.

SELECT Code, Name, Rank, Format(Percentage, "Percent") AS Pct, Format(Cost, "Currency") AS Cst FROM [the excel sheet];

[gmaxey]

June7

Thank you for your reply. I am doing something like that when using Access as my data source. What I am trying to figure out is how to use Word and Excel exclusively or Word and Access exclusively. Based on your earlier post, I have discovered that Excel does have a query feature. I've been wandering aimlessly through it and some YouTube videos but can't figure out how to achieve the same result that I can using Access.

Yes, using VBA after the data in extracted to the Word form, I can reapply formatting to match the formatting in the data source.

For some reason, I was under the impression that IMEX=1 in the connection string would ensure that the "literal" string value displayed in the fields would be retrieved via ADODB. Apparently I am wrong.


Please see: https://www.msofficeforums.com/excel-programming/51670-power-query-formatted-text.html

[Paul_Hossler]

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

FWIW, if you could read the Excel file and retreive the .Text property instead of the .Value property, you would get $12.00 instead of 12

I don't know OLEDB so I don't know if it's possilble

[June7]

Why do you need Word?

I think Word can pull from Access query object.

Since you already understand how to do this in Access query, why look to Excel?

What I did:

In Excel Power Query, click Add Column > Custom Column - enter alias field name in place of "Custom" then expression: =Number.ToText([Cost], "C")
Remove unformatted column.

Review
Number.ToText - Function | Power Query How
How do you cast an integer to a string in Excel's PowerQuery - Stack Overflow

[gmaxey]

Why did the chicken cross the road?

Some people are more familiar with Access and some people are more familiar with Excel. I am trying to accommodate both.

[June7]

I just edited my previous post as you were posting. Might review again.

[gmaxey]

June7,

This is very helpful. Thank you. With this information, I have been able to transform the long date, percentage and cost data type to their respective formatted text:

Date.ToText([ApplicationDate], "dddd, MMMM d, yyyy")
Number.ToText([Percentage], "P")
Number.ToText([Cost], "C")

I am still trying to get my head around the Power Query processes as it doesn't seem to be as straight forward as the Access queries. I mean I added a row to my base worksheet and it took me 15 minutes to figure out how to refresh the query. Oh well. I'll learn.

Thanks again.