Sign in to follow this  
Followers 0
the123punch

Excel WriteCell not working

6 posts in this topic

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

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

post-24479-0-99310200-1311359101_thumb.j

Share this post


Link to post
Share on other sites



Not enough code to effectively help you.


#include <ByteMe.au3>

Share this post


Link to post
Share on other sites

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"

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

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