Sign in to follow this  
Followers 0
Gerry

Excel and array UDF problems

7 posts in this topic

#1 ·  Posted (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.

:think:

; ********************************************

; 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

; ***************************************

:lmao:

Gerry

Edited by Gerry

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (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. :lmao:

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 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

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

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 :lmao:
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

I 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

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