Gerry Posted January 21, 2009 Share Posted January 21, 2009 (edited) Hi All I am currently using the Excel UDF in my program, earlier I used the Excelcom UDF but with same results. I need to read a column from Excel which contains data in the Date format, i.e. 2008/10/12 etc. When I display this using _ArrayDisplay, the "/" are gone from this particular column. When one writes the data from this column back to the original file, it causes a error on opening the saved Excel file. I think "/" has or had a meaning for spreadsheet users before, other than as a maths operator. Is there some cure for this behaviour or will I have to check each value and reformat it back to the original? (Very frustrating since I'm no fundi at proramming) Also found _ArraySearch() and _ArrayFindAll() do not find search values in 2D arrays that was created using _ExcelReadSheetToArray() and _ExcelReadArray(). _ArrayDisplay() shows the values in the array I'm searching for. Does anyone know if this sort of problems will be sorted out in future in the Excel and Array UDF's? I have attached a Excel file to demonstrate my problem. Here follows a script to demonstrate the problem. Save in the same folder as the Test.xls file. ; ******************************************** ; Test Script to demonstrate my problem start #include <Excel.au3> #include <Array.au3> $File = FileOpenDialog("Please select file", @ScriptDir & "\", "(Test*.xls)", 1) ; Select Test file If @error Then ;Error if no daily file was selected MsgBox(4096, "", "No file Selected!") Exit EndIf $oExcel = _ExcelBookOpen($File, 1) ; Open spreadsheet $aArraySheet = _ExcelReadSheetToArray($oExcel) ; Read spreadsheet to array $aArrayColumn = _ExcelReadArray($oExcel, 1, 1, $aArraySheet[0][0], 1) ; Read Column 2 $VarRow2Col2 = _ExcelReadCell($oExcel, 2, 2) MsgBox(0, "Values in Array vs. File", _ "File Row 2, Column 2 : " & $VarRow2Col2 & " vs. Array of sheet value : " & $aArraySheet[2][2] & @CR & @CRLF & _ 'Real value is actualy 26/11/2008 08:43"') _ArrayDisplay($aArraySheet, "Note Column date formatting slashes are gone.") $sSearch = _ExcelReadCell($oExcel, 2, 1) $iIndex = _ArraySearch($aArraySheet, $sSearch, 0, 0, 0, 0, 1) If @error Then MsgBox(0, "Not Found, $aArraySheet", '"' & $sSearch & '" was not found in 2D array, $aArraySheet.') Else MsgBox(0, "Found, $aArraySheet", '"' & $sSearch & '" was found in 2D array, $aArraySheet at position ' & $iIndex & ".") EndIf $iIndex = _ArraySearch($aArrayColumn, $sSearch) If @error Then MsgBox(0, "Not Found , $aArrayColumn", '"' & $sSearch & '" was not found in $aArrayColumn.') Else MsgBox(0, "Found, $aArrayColumn", '"' & $sSearch & '" was found in $aArrayColumn at position ' & $iIndex & ".") EndIf _ExcelWriteSheetFromArray($oExcel, $aArraySheet, 1, 1, 0, 1) _ExcelBookSaveAs($oExcel, @ScriptDir & "\TestBackup", "xls") _ExcelBookClose($oExcel) ; Open spreadsheet MsgBox(0, "Backup file format", "Please open the backed up file manually" & @CRLF & _ "to check column 2 or the date column. Mote the change from the original." & @CRLF & @CRLF & _ "After closing this file, open Test.au3 and then select the backup file." & @CRLF & _ "Note how the Test.au3 file bombs out!") ; End of my demo script ; *************************************** Gerry Edited January 21, 2009 by Gerry Link to comment Share on other sites More sharing options...
Gerry Posted January 21, 2009 Author Share Posted January 21, 2009 Hi All It would seem I'm unable to add the demo Excel file. Wonder if I text to this and then it could be saved as Test.xls ? Here goes: 3 colums, 5 lines: Order No Create Date Description CH0000000180462 26/11/2008 08:43 Please supply Printer CH0000000180468 26/11/2008 09:35 Please upgrade 1 x PC CH0000000180471 26/11/2008 09:41 PLEASE UPGRADE 1 X PC IP CH0000000180474 26/11/2008 09:46 Install 8 port Trendnet switch. Sorry for this amateurish way of sending my demo Excel file. Can't blame anyone if you don't respond to this. Gerry Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 21, 2009 Share Posted January 21, 2009 (edited) Hi All It would seem I'm unable to add the demo Excel file. Wonder if I text to this and then it could be saved as Test.xls ? Here goes: 3 colums, 5 lines: Order No Create Date Description CH0000000180462 26/11/2008 08:43 Please supply Printer CH0000000180468 26/11/2008 09:35 Please upgrade 1 x PC CH0000000180471 26/11/2008 09:41 PLEASE UPGRADE 1 X PC IP CH0000000180474 26/11/2008 09:46 Install 8 port Trendnet switch. Sorry for this amateurish way of sending my demo Excel file. Can't blame anyone if you don't respond to this. Gerry I'm not seeing it. This demo creates the file, save it, reopens it, reads the column to an array, then writes it back, saves and reopens it again to see the results: #include <Excel.au3> #include <Array.au3>; Only for _ArrayDisplay() Global $sFile = @ScriptDir & "\Test.xls" Global $avSheet, $avCol2, $oExcel Global $avData[5][5] = [[4, 3, "", ""], _ ["", "CH0000000180462", "26/11/2008 08:43", "Please supply Printer"], _ ["", "CH0000000180468", "26/11/2008 09:35", "Please upgrade 1 x PC"], _ ["", "CH0000000180471", "26/11/2008 09:41", "PLEASE UPGRADE 1 X PC IP"], _ ["", "CH0000000180474", "26/11/2008 09:46", "Install 8 port Trendnet switch"]] ; Create file $oExcel = _ExcelBookNew() _ExcelWriteSheetFromArray($oExcel, $avData) _ExcelBookSaveAs($oExcel, $sFile, "xls", 0, 1) MsgBox(64, "Debug", "New File Data") _ExcelBookClose($oExcel) Sleep(1000) ; Open file and change dates $oExcel = _ExcelBookOpen($sFile) $avCol2 = _ExcelReadArray($oExcel, 1, 2, $avData[0][0], 1) _ArrayDisplay($avCol2, "Debug Before: $avCol2") _ExcelWriteArray($oExcel, 1, 2, $avCol2, 1) _ExcelBookSave($oExcel) MsgBox(64, "Debug", "Changed File Data") _ExcelBookClose($oExcel) Sleep(1000) ; Open file to see changes $oExcel = _ExcelBookOpen($sFile) No errors and the data look unharmed. AutoIt 3.3.0.0, XP Pro SP2, and Excel 2002. Edited January 21, 2009 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Gerry Posted January 22, 2009 Author Share Posted January 22, 2009 Thanks for your response and the easy way to create a test file, I incorparated part of your script in to my demo. Please try the new demo and tell me if you get the same problems as I do. ; ******************************************** ; Test Script to demonstrate my problem start #include <Excel.au3> #include <Array.au3> Global $sFile = @ScriptDir & "\Test.xls" Global $avSheet, $avCol2, $oExcel Global $avData[5][5] = [[4, 3, "", ""], _ ["", "CH000180462", "26/11/2008 08:43", "Please supply Printer"], _ ["", "CH000180468", "26/11/2008 09:35", "Please upgrade 1 x PC"], _ ["", "CH000180471", "26/11/2008 09:41", "PLEASE UPGRADE 1 X PC IP"], _ ["", "CH000180474", "26/11/2008 09:46", "Install 8 port Trendnet switch"]] If Not IsDeclared("iMsgBoxAnswer") Then Local $iMsgBoxAnswer $iMsgBoxAnswer = MsgBox(260,"Create Test file","Would you like to create a new test file?") Select Case $iMsgBoxAnswer = 6 ;Yes _CreateFile() Case $iMsgBoxAnswer = 7 ;No EndSelect Func _CreateFile() ; Create file $oExcel = _ExcelBookNew() _ExcelWriteSheetFromArray($oExcel, $avData) _ExcelBookSaveAs($oExcel, $sFile, "xls", 0, 1) _ExcelBookClose($oExcel) EndFunc $File = FileOpenDialog("Please select a file to open", @ScriptDir & "\", "(Test*.xls)", 1) ; Select Test file If @error Then ;Error if no daily file was selected MsgBox(4096, "", "No file Selected!") Exit EndIf $oExcel = _ExcelBookOpen($File, 1) ; Open spreadsheet $aArraySheet = _ExcelReadSheetToArray($oExcel) ; Read spreadsheet to array $aArrayColumn = _ExcelReadArray($oExcel, 1, 1, $aArraySheet[0][0], 1) ; Read Column 2 $VarRow2Col2 = _ExcelReadCell($oExcel, 2, 2) MsgBox(0, "Values in Array vs. File", _ "File Row 2, Column 2 : " & $VarRow2Col2 & " vs. Array of sheet value : " & $aArraySheet[2][2] & @CR & @CRLF & _ 'Real value is actualy 26/11/2008 08:43"') _ArrayDisplay($aArraySheet, "Note Column date formatting slashes are gone.") $sSearch = _ExcelReadCell($oExcel, 2, 1) $iIndex = _ArraySearch($aArraySheet, $sSearch, 0, 0, 0, 0, 1) If @error Then MsgBox(0, "Not Found, $aArraySheet", '"' & $sSearch & '" was not found in 2D array, $aArraySheet.') Else MsgBox(0, "Found, $aArraySheet", '"' & $sSearch & '" was found in 2D array, $aArraySheet at position ' & $iIndex & ".") EndIf $iIndex = _ArraySearch($aArrayColumn, $sSearch) If @error Then MsgBox(0, "Not Found , $aArrayColumn", '"' & $sSearch & '" was not found in $aArrayColumn.') Else MsgBox(0, "Found, $aArrayColumn", '"' & $sSearch & '" was found in $aArrayColumn at position ' & $iIndex & ".") EndIf _ExcelWriteSheetFromArray($oExcel, $aArraySheet, 1, 1, 0, 1) _ExcelBookSaveAs($oExcel, @ScriptDir & "\TestBackup", "xls") _ExcelBookClose($oExcel) ; Open spreadsheet MsgBox(0, "Backup file format", "Please open the backed up file manually" & @CRLF & _ "to check column 2 or the date column. Mote the change from the original." & @CRLF & @CRLF & _ "After closing this file, open Test.au3 and then select the backup file." & @CRLF & _ "Note how the Test.au3 file bombs out!") ; End of my demo script ; *************************************** Gerry Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 22, 2009 Share Posted January 22, 2009 Thanks for your response and the easy way to create a test file, I incorparated part of your script in to my demo. Please try the new demo and tell me if you get the same problems as I do. CODE; ******************************************** ; Test Script to demonstrate my problem start #include <Excel.au3> #include <Array.au3> Global $sFile = @ScriptDir & "\Test.xls" Global $avSheet, $avCol2, $oExcel Global $avData[5][5] = [[4, 3, "", ""], _ ["", "CH000180462", "26/11/2008 08:43", "Please supply Printer"], _ ["", "CH000180468", "26/11/2008 09:35", "Please upgrade 1 x PC"], _ ["", "CH000180471", "26/11/2008 09:41", "PLEASE UPGRADE 1 X PC IP"], _ ["", "CH000180474", "26/11/2008 09:46", "Install 8 port Trendnet switch"]] If Not IsDeclared("iMsgBoxAnswer") Then Local $iMsgBoxAnswer $iMsgBoxAnswer = MsgBox(260,"Create Test file","Would you like to create a new test file?") Select Case $iMsgBoxAnswer = 6 ;Yes _CreateFile() Case $iMsgBoxAnswer = 7 ;No EndSelect Func _CreateFile() ; Create file $oExcel = _ExcelBookNew() _ExcelWriteSheetFromArray($oExcel, $avData) _ExcelBookSaveAs($oExcel, $sFile, "xls", 0, 1) _ExcelBookClose($oExcel) EndFunc $File = FileOpenDialog("Please select a file to open", @ScriptDir & "\", "(Test*.xls)", 1) ; Select Test file If @error Then ;Error if no daily file was selected MsgBox(4096, "", "No file Selected!") Exit EndIf $oExcel = _ExcelBookOpen($File, 1) ; Open spreadsheet $aArraySheet = _ExcelReadSheetToArray($oExcel) ; Read spreadsheet to array $aArrayColumn = _ExcelReadArray($oExcel, 1, 1, $aArraySheet[0][0], 1) ; Read Column 2 $VarRow2Col2 = _ExcelReadCell($oExcel, 2, 2) MsgBox(0, "Values in Array vs. File", _ "File Row 2, Column 2 : " & $VarRow2Col2 & " vs. Array of sheet value : " & $aArraySheet[2][2] & @CR & @CRLF & _ 'Real value is actualy 26/11/2008 08:43"') _ArrayDisplay($aArraySheet, "Note Column date formatting slashes are gone.") $sSearch = _ExcelReadCell($oExcel, 2, 1) $iIndex = _ArraySearch($aArraySheet, $sSearch, 0, 0, 0, 0, 1) If @error Then MsgBox(0, "Not Found, $aArraySheet", '"' & $sSearch & '" was not found in 2D array, $aArraySheet.') Else MsgBox(0, "Found, $aArraySheet", '"' & $sSearch & '" was found in 2D array, $aArraySheet at position ' & $iIndex & ".") EndIf $iIndex = _ArraySearch($aArrayColumn, $sSearch) If @error Then MsgBox(0, "Not Found , $aArrayColumn", '"' & $sSearch & '" was not found in $aArrayColumn.') Else MsgBox(0, "Found, $aArrayColumn", '"' & $sSearch & '" was found in $aArrayColumn at position ' & $iIndex & ".") EndIf _ExcelWriteSheetFromArray($oExcel, $aArraySheet, 1, 1, 0, 1) _ExcelBookSaveAs($oExcel, @ScriptDir & "\TestBackup", "xls") _ExcelBookClose($oExcel) ; Open spreadsheet MsgBox(0, "Backup file format", "Please open the backed up file manually" & @CRLF & _ "to check column 2 or the date column. Mote the change from the original." & @CRLF & @CRLF & _ "After closing this file, open Test.au3 and then select the backup file." & @CRLF & _ "Note how the Test.au3 file bombs out!") ; End of my demo script ; *************************************** Gerry There is stuff in there that is wrong. For example the MsgBox() text "Real value is actualy 26/11/2008 08:43" is wrong. Cell R2C2 is "26/11/2008 09:35". Running a corrected version, it works fine, everything matches up, and the slashes are not missing from the dates: #include <Excel.au3> #include <Array.au3> Global $sFile = @ScriptDir & "\Test.xls" Global $avSheet, $avCol2, $oExcel Global $avData[5][5] = [[4, 3, "", ""], _ ["", "CH000180462", "26/11/2008 08:43", "Please supply Printer"], _ ["", "CH000180468", "26/11/2008 09:35", "Please upgrade 1 x PC"], _ ["", "CH000180471", "26/11/2008 09:41", "PLEASE UPGRADE 1 X PC IP"], _ ["", "CH000180474", "26/11/2008 09:46", "Install 8 port Trendnet switch"]] $oExcel = _ExcelBookNew() _ExcelWriteSheetFromArray($oExcel, $avData) _ExcelBookSaveAs($oExcel, $sFile, "xls", 0, 1) _ExcelBookClose($oExcel) $oExcel = _ExcelBookOpen($sFile, 1); Open spreadsheet $aArraySheet = _ExcelReadSheetToArray($oExcel); Read spreadsheet to array _ArrayDisplay($aArraySheet, "Debug: $aArraySheet") $aArrayColumn = _ExcelReadArray($oExcel, 1, 1, $aArraySheet[0][0], 1); Read Column 1 _ArrayDisplay($aArrayColumn, "Debug: $aArrayColumn") $VarRow2Col2 = _ExcelReadCell($oExcel, 2, 2) MsgBox(0, "Values in Array vs. File", _ "$aArraySheet[2][2] = " & $aArraySheet[2][2] & @CRLF & _ "$VarRow2Col2 = " & $VarRow2Col2 & @CRLF & _ "Value should be = 26/11/2008 09:35") Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Gerry Posted January 23, 2009 Author Share Posted January 23, 2009 Hi Thanks again for your response. Ok, the value I put there was wrong, sorry. 1. The point is when I do _ArrayDisplay the "/" are gonein the Date column. 2. Once saved to "TestBackup.xls", the "/" are gone too. 3. Opening "TestBackup.xls" bombs out from the excel udf. 4. Searching a 2D array does not show values which are in the array. I am using XP Pro, SP3, Office 2003 with all current updates in place. Could it have something to do with my locale settings because at home with XP Home, I get the same results? Thanks for your assistance. Gerry Link to comment Share on other sites More sharing options...
PsaltyDS Posted January 23, 2009 Share Posted January 23, 2009 HiThanks again for your response. Ok, the value I put there was wrong, sorry. 1. The point is when I do _ArrayDisplay the "/" are gonein the Date column. 2. Once saved to "TestBackup.xls", the "/" are gone too.3. Opening "TestBackup.xls" bombs out from the excel udf.4. Searching a 2D array does not show values which are in the array.I am using XP Pro, SP3, Office 2003 with all current updates in place.Could it have something to do with my locale settings because at home with XP Home,I get the same results?Thanks for your assistance.GerryI don't know. I can't reproduce the symptoms. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law 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