Jump to content

Excel WriteCell not working


Recommended Posts

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

Link to comment
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"

Link to comment
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]))
Link to comment
Share on other sites

  • 4 weeks later...

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...