Excel WriteCell not working

[the123punch]

Hi all,

Any help with this will be greatly appreciated, since I am spending way more time than I originally had planned for this.

I am creating a script which is supposed to create a GUI and allow user input to select multiple Excel files, provide columns numbers to copy, and extract all the contents from these Excel files into 1 final Excel destination file.

The GUI creation has already been taken care of and works great.

However, in my manipulation of multiple Excel documents, I am failing at the writing process of the final Excel document (the line which writes is in Bold and Italic so you can find it in the below code). I always obtain the following error message (which is attached).

Can you please help!?

Thanks!

the123punch

expandcollapse popup

Func Process()
    $destRow = 1
    $curSrcCells = ""
    Dim $allSrcCells[1][1]
    $curDestCell = ""
    $destSheetName = ""
    $destFileName = ""
    
    If validateData() Then
        $array = StringSplit(GUICtrlRead ($txtFiles2Extract), "|")
        $destFileName = GUICtrlRead ($txtFile2Create)
        $destExcelFile = _ExcelBookOpen($destFileName, 1)   

        ;_ArrayDisplay($array)
        $destSheetName = StringMid($array[2],1,30)
        _ExcelSheetDelete($destExcelFile, $destSheetName)
        _ExcelSheetAddNew($destExcelFile, $destSheetName)
        ;MsgBox(0,"TEST1234124", "TESTING");GUICtrlRead ($txtColumns2Extract))
        $colsArray = StringSplit(GUICtrlRead ($txtColumns2Extract), ",")
        ;_ArrayDisplay($colsArray)
        
        ;;looping through array variable to open source file names one by one
        For $i=2 to $array[0] Step 1 
            $srcRow = 1
            ;MsgBox(0, "TEST", $array[1] & "\" & $array[$i])
            $srcExcelFile = _ExcelBookOpen($array[1] & "\" & $array[$i], 0)
            ;_ExcelSheetActivate($srcExcelFile, 1)
            $curSrcCells = _ExcelReadSheetToArray($srcExcelFile)
            ;_ArrayDisplay($curSrcCells)
            _ExcelBookClose($srcExcelFile, 0)
            
            ;;;;;looping through file to get information from specified cells;;;;
            For $j=1 to $curSrcCells[0][0] Step 1   
                ;verifying whether the current row has data at the specified columns
                For $k = 1 to $colsArray[0] Step 1                      
                    ;writing from the source document to the destination document   
                    ;MsgBox(0, "Variables", "$j=" & $j & " $curSrcCells[0][0]=" & $curSrcCells[0][0] & " $k=" & $k & " $colsArray[0]=" & $colsArray[0] & " $srcRow= " & $srcRow & " $destRow= " & $destRow)
                    ;MsgBox(0, "Variables", "$curSrcCells[$j][$colsArray[$k]]=" & $curSrcCells[$j][$colsArray[$k]])
                    
                    $destExcelFileAttached = _ExcelBookAttach($destFileName)
                    ;_ExcelSheetActivate($destExcelFileAttached, $destSheetName)
                    MsgBox(0, "Writing", "Writing cell")
                    [i][b]_ExcelWriteCell($destExcelFileAttached, $curSrcCells[$j][$colsArray[$k]], $destRow, $colsArray[$k]) [/b] [/i]  
                Next
                ;incrementing row variables
                $srcRow = $srcRow + 1
                $destRow = $destRow + 1
            Next
            ;;;;;end loop;;;;
        Next
        ;_ArrayDisplay($array)
        _ExcelBookClose($destExcelFile, 1)  ;saving the file before closing
        _ExcelBookOpen(GUICtrlRead ($txtFile2Create), 1)    ;re-opening the saved file (and making it visible)
        ;MsgBox (0, "YES!", "TEST YES!")
    EndIf
EndFunc

[sleepydvdr]

Not enough code to effectively help you.

[MrMitchell]

However, in my manipulation of multiple Excel documents, I am failing at the writing process of the final Excel document (the line which writes is in Bold and Italic so you can find it in the below code). I always obtain the following error message (which is attached).

What sleepy said, but...

On this line: $destExcelFileAttached = _ExcelBookAttach($destFileName)

Have you tried testing $destExcelFileAttached before trying to use it? For example, "If IsObj($destExcelFileAttached) Then"

[the123punch]

ExcelCombine.au3

Not enough code to effectively help you.

I have attached my entire code. Hopefully that helps better.

You can compile and see what happens.

the123punch

Edited July 22, 2011 by the123punch

[MrMitchell]

I am failing at the writing process of the final Excel document (the line which writes is in Bold and Italic so you can find it in the below code). I always obtain the following error message (which is attached).

Make your last parameter a number in the following:

_ExcelWriteCell($destExcelFileAttached, $curSrcCells[$j][$colsArray[$k]], $destRow, $colsArray[$k])

By changing it to:

_ExcelWriteCell($destExcelFileAttached, $curSrcCells[$j][$colsArray[$k]], $destRow, Number($colsArray[$k]))

[skin27]

Make your last parameter a number in the following:

_ExcelWriteCell($destExcelFileAttached, $curSrcCells[$j][$colsArray[$k]], $destRow, $colsArray[$k])

By changing it to:

_ExcelWriteCell($destExcelFileAttached, $curSrcCells[$j][$colsArray[$k]], $destRow, Number($colsArray[$k]))

I had the same problem and your solution worked for me. Edited August 16, 2011 by skin27