cgasb Posted December 19, 2007 Share Posted December 19, 2007 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! Link to comment Share on other sites More sharing options...
cgasb Posted December 20, 2007 Author Share Posted December 20, 2007 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. Link to comment Share on other sites More sharing options...
Kerros Posted December 20, 2007 Share Posted December 20, 2007 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. Link to comment Share on other sites More sharing options...
Haugaard Posted December 20, 2007 Share Posted December 20, 2007 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 ?? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now