Need help with .ini to .xls converter :)

[r3dbull]

Hello guys,

in the following the code of my script that takes as input a .ini file and transform it in a .xls file:

EDIT:// Added COM Error Handler as PsaltyDS suggested

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

Func MyErrFunc()
     Local $HexNumber, $strMsg = ""
     
     $HexNumber = Hex($oMyError.Number, 8)
     $strMsg = "Error Number: " & $HexNumber & @CRLF
     $strMsg &= "Source: " & $oMyError.source & @CRLF
     $strMsg &= "Description: " & $oMyError.description & @CRLF
     $strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
     $strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
     MsgBox(16, "COM ERROR", $strMsg)
     SetError(1)
Endfunc

$oExcel=ObjCreate("Excel.Application")
$oExcel.Visible=0
$oExcel.WorkBooks.Open("D:\Trash\AutoIT Scripts\Database_Test.xls")
If @error=1 Then
    ConsoleWrite("Error! - Unable to Create the Excel Object!"& @CRLF)
ElseIf @error=2 Then
    ConsoleWrite("Error! - File does not exist!" & @CRLF)
EndIf

Global $avData = _ExcelReadArray($oExcel,1,1,1000,1,1)
Global $iLastUsed =0, $iNextRow=0, $i=0, $var[100000]
$var=IniReadSectionNames("Database.ini")

For $i=1 To $var[0]
    _ExcelWriteCell($oExcel,$var[$i],$i,1)
    $var2=IniReadSection("Database.ini",$var[$i])

    For $x=1 to $var2[0][0]
        _ExcelWriteCell($oExcel,$var2[$x][0],$i,$x+2)   
    Next
$avData = _ExcelReadArray($oExcel,1,1,1000,1,1)
Next
_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel)

The .ini file, in other words, the input of this "To XLS" converter is being populated every day by new records, that may be new Section Names or new records inside sections.

That means that every time I run this converter, I get my .xls file more bigger in meaning of more number of rows and columns.

The actual problem is that when I try to convert now, I get the following error:

>"D:\Trash\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "D:\Trash\AutoIT Scripts\Excel_Test.au3"

D:\Trash\AutoIt3\Include\Excel.au3 (451) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn)^ ERROR

>Exit code: 1 Time: 3.029

I hope someone of you guys can help me with this.

The format of the .ini file is like in the following:

["Some characters1"]

"Number1"="Http Link1"

"Number2"="Http Link2"

...

["Some characters2"]

"Number3"="Http Link3"

"Number4"="Http Link4"

and so on...

and the Excel output is:

| Col 1 | Col 2 | Col3 |

Row 1 |"Some characters1" | "Number1" | "Number2" |

Row 2 |"Some characters2" | "Number3" | "Number4" |

and so on...

Best regards,

Luciano

Edited May 20, 2011 by r3dbull

[PsaltyDS]

Add a COM Error handler (see help file) and you should get more info on the problem. Perhaps the referenced cell in _ExcelWriteCell() is write-protected or invalid for some reason.

[r3dbull]

Added a COM Error Handler at the very start of my piece of code:

Func MyErrFunc()
Local $HexNumber
Local $strMsg

$HexNumber = Hex($oMyError.Number, 8)
$strMsg = "Error Number: " & $HexNumber & @CRLF
$strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
$strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
MsgBox(0, "ERROR", $strMsg)
SetError(1)
Endfunc

Check the attached image for the error output, which is:

Error Number: 80020009

WinDescription:

Script Line: 451

Uploaded with ImageShack.us

Now, I sincerely don't know how to step on from here...

Edited May 17, 2011 by r3dbull

[PsaltyDS]

Include the .source and .description in your error handler:

Func MyErrFunc()
     Local $HexNumber, $strMsg = ""
     
     $HexNumber = Hex($oMyError.Number, 8)
     $strMsg = "Error Number: " & $HexNumber & @CRLF
     $strMsg &= "Source: " & $oMyError.source & @CRLF
     $strMsg &= "Description: " & $oMyError.description & @CRLF
     $strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF
     $strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF
     MsgBox(16, "COM ERROR", $strMsg)
     SetError(1)
Endfunc

[r3dbull]

I haven't got that far from where I was

Uploaded with ImageShack.us

[PsaltyDS]

Well, that didn't help as much as hoped.

You'll have to post small examples of Database_Test.xls and Database.ini that will allow us to duplicate your symptoms with the code already posted.

[r3dbull]

I think I may have found the problem...

I just tried to create a new fresh and clean Excel DB and it is generating only 2 rows, and the 2° has got too many colums...

Is there a way to modify my source up there to make like a limit on the number of possible columns?

[JoHanatCent]

Try _ExcelReadSheetToArray

See help for detail and then set your limits?

[r3dbull]

I finally solved this issue, the problem was the number of colums that Excel allows you to have.

I solved by modifying the script and make it take from .ini file only the last 30 records for every sector, which is fine for what I need.

This topic can be closed, thanks to everyone