Sign in to follow this  
Followers 0
cgasb

ExcelCOM_UDF and reading dates and formulas

4 posts in this topic

When I read a column of dates in Excel using

_ExcelReadArray
and then write those values to a new sheet I get "20050128000000" instead of "1/28/2005". What should I be looking at to get the preferred formatting back?

My second question is about reading cells that contain formulas. When I use

_ExcelReadArray
on a column of formulas I get "0" when I insert the new cell. Is that a limitation of ExcelCOM_UDF or is there a way to carry the resultant value over. Thanks for the advice!

Share this post


Link to post
Share on other sites



I figured out why I was getting zeros instead of formula results (typo on the R1C1 location). I could still use some help with the dates though. This is a snippet:

#include <ExcelCOM_UDF.au3>  ; Include the Excel collection
#include <Array.au3> ; Include the Array collection

$FilePathONE = FileOpenDialog("Select the first file", "", "Excel files (*.xls;*.xlsx;*.csv)", 1) ;browse for the XLS file
    If @error Then
        MsgBox(4096,"","No File chosen")
        exit
    EndIf
$oExcelONE = _ExcelBookOpen($FilePathONE, 0) ;open the chosen XLS file
$sExcelTitleONE = $oExcelONE.Name & " - " & $oExcelONE.ActiveWorkbook.Name ;Get window title
$sExcelSheetONE = _ExcelSheetNameGet($oExcelONE) ;get the active sheet name
$val = _ExcelReadCell($oExcelONE, 3, 4)
Msgbox(0,'Cell value as seen by AutoIT',$val)
_ExcelBookClose($oExcelONE, 0,1)

If I read a cell with a date, for example 1/15/2005, then $val will = 20020115000000

Is there a way when using _ExcelWriteCell to write the date back using slashes, rather than 20020115000000? Thanks for the advice.

Share this post


Link to post
Share on other sites

I don't have a good answer for you on this one. I use the same ExcelCOM_UDF, but don't usually look at dates. You could post process anything that you know is a date as the format seems to remain the same whatever date I tried.


Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites

I figured out why I was getting zeros instead of formula results (typo on the R1C1 location). I could still use some help with the dates though. This is a snippet:

#include <ExcelCOM_UDF.au3>  ; Include the Excel collection
#include <Array.au3> ; Include the Array collection

$FilePathONE = FileOpenDialog("Select the first file", "", "Excel files (*.xls;*.xlsx;*.csv)", 1) ;browse for the XLS file
    If @error Then
        MsgBox(4096,"","No File chosen")
        exit
    EndIf
$oExcelONE = _ExcelBookOpen($FilePathONE, 0) ;open the chosen XLS file
$sExcelTitleONE = $oExcelONE.Name & " - " & $oExcelONE.ActiveWorkbook.Name ;Get window title
$sExcelSheetONE = _ExcelSheetNameGet($oExcelONE) ;get the active sheet name
$val = _ExcelReadCell($oExcelONE, 3, 4)
Msgbox(0,'Cell value as seen by AutoIT',$val)
_ExcelBookClose($oExcelONE, 0,1)

If I read a cell with a date, for example 1/15/2005, then $val will = 20020115000000

Is there a way when using _ExcelWriteCell to write the date back using slashes, rather than 20020115000000? Thanks for the advice.

I might not be to any use (cause im a nub) but this is still an idea

Cant you format it back with _ExcelNumberFormatR1C1 ???

Or could you use ex. 10.06.1981 or 10-06-1981 ??

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
Sign in to follow this  
Followers 0