Jump to content
dpcompower

Help: _ExcelReadSheetToArray with Time value

Recommended Posts

dpcompower

When I used the function, cell that has value as time (HH:mm, ex: 10:38:00 AM) showed in array as decimal (ex: 0.443055555555556.)

There's no parameter in the function to ask it to treat as text at least.

This line of code is to load the cell value into the returned array:

$avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value

So how can I change it so that it would return the value as Time which is what it was formatted on Excel sheet; or at least text?

I did change it to:

$avRET[$r][$c] = String($oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value)

But didn't work, I guess it was because of how Excel object deal with cell value.

Please help! Thanks.

Share this post


Link to post
Share on other sites
Subz

Change ".value" to ".text" should work

Share this post


Link to post
Share on other sites
dpcompower

Thanks a lot! Never thought of that simple.

I modified the UDF on my PC to have another function to read all cells as text as you told, that way I can use the original one when I do need value.

I thought about modify the original function to have another parameter, but I think keep it as is is better way to do.

Share this post


Link to post
Share on other sites
water

Seems you are using a quite old version of the Excel UDF. The Excel UDF has been rewritten and is now faster and easier to use.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×