Insert copied columns in excel

[drbyte]

I'am strugling to get the copied columns pasted in excel.

Below the code till so far.

$MyExcel.activesheet.range("C9:W9").value = ""
    $i = UBound($aArray1) - 1
    If $i > 11 Then
        For $j = ($i - 11) To 1 Step -1
        $MyExcel.activesheet.range("W:X").copy
;~              how to insert them on the same place W:X?
        Next
;~  fill cells with array - still finding a way to do it because 2 cells are merged every time      
    Else
;~  fill cells with array - still finding a way to do it because 2 cells are merged every time        
    EndIf

Any help would be fine.

Edited June 9, 2013 by drbyte

[water]

Please have a look at my rewrite of the Excel UDF (still an alpha version). If you just want to write an array to a range use function _Excel_RangeWrite.

[drbyte]

@water

i'am a little bit further now, i can insert the columns, but now i need to merge every two cells every "x" time.

When that is finished i need to solve the problem with inserting the array into  merged cells.

Is it possible with your rewritten Excel UDF? (Where to find it?)

$MyExcel.activesheet.range("C9:W9").value = ""
    $i = UBound($aArray1) - 1
    If $i > 11 Then
        For $j = ($i - 11) To 1 Step -1
;~          MsgBox(0, "Count down!", $j)
            _ExcelColumnInsert($MyExcel, 23, 2) ; insert from column 23, insert two columns at once
        Next
        $sCellValue = 1
        _ExcelWriteCell($MyExcel, $sCellValue, 9, 23) ;Write to the Cell
;~      _ExcelWriteArray($MyExcel, 9, 3, $aArray1)
    Else
;~      _ExcelWriteArray($MyExcel, 9, 3, $aArray1)
    EndIf

Edited June 9, 2013 by drbyte

[water]

If merge cells means: "The current content of the cell is 2 and after merging a cell with content 4 the new value is 6" then function _Excel_RangeCopyPaste can help.

It uses the PasteSpecial method.

[drbyte]

@water

See the picture to see what i mean with merged cells.

When i fill the merged cells with the array half of the array is missing, when i

split the cells, the array is inserted the correct way, but i need the merged cells.

Hope i'am clear now.

Edited June 10, 2013 by drbyte

[water]

I don't understand the script in your first post. You run a loop but never use the counter.

Where does the data you want to copy come from? Is it already in the clipboard? Can you give an example of the data you want to copy?

Which range do you want to fill (A3:X9 or A5:X9)?

[drbyte]

@water

The data i want to copy, comes from an excel sheet straight out of our ERP program.

The values are read into an Global  $aArray1 which i use in the code mentioned above.

The range i want to fill depends on the amount of values in the array.

The range always starts at cell C3 (which is merged with D3), the end depends on the amount of values in the array. 
For example C3:W3 when there are 11 values in the array.

Below an example of the data (screenshot of the array) i want to copy to the range C3:?9.....

Edited June 11, 2013 by drbyte

[water]

Can't test at the moment but I hope to come up with a solution tomorrow.

[drbyte]

I'll wait for your solution 

[drbyte]

I've been playing again and i came a little bit further now.

I managed to merge the extra inserted cells (filled blue), and also insert values of the array in cell C9 and E9.

Now i need to find a way to do this with a counter.

See code and screenshot.

$i = UBound($aArray1) - 1
    If $i > 11 Then
        For $j = ($i - 11) To 1 Step -1
;~          MsgBox(0, "Count down!", $j)
            _ExcelColumnInsert($MyExcel, 23, 2) ; insert from column 23, insert two columns at once
        Next
    EndIf

    If $i > 11 Then ; merge the inserted cells, how raise up W to AC with counter?
        For $j = ($i - 11) To 1 Step -1
            $MyExcel.activesheet.range("W9:X9").MergeCells = True;
            $MyExcel.activesheet.range("Y9:Z9").MergeCells = True;
            $MyExcel.activesheet.range("AA9:AB9").MergeCells = True;
            $MyExcel.activesheet.range("AC9:AD9").MergeCells = True;
        Next
    EndIf

    $MyExcel.activesheet.range("C9:D9").value = $aArray1[0] ; how to raise up C9 to end of array?
    $MyExcel.activesheet.range("E9:F9").value = $aArray1[1]

Edited June 12, 2013 by drbyte

[water]

Can you please add the picture directly to your post? DropBox is locked here.

[drbyte]

[water]

Instead of using A1 notation ("W9:X9") you could use R1C1 notation ("R9C23:R9C24").

So you could easily use the counter:

$MyExcel.ActiveSheet.Range("R9C" & $j & ":R9C" & $j + 1).MergeCells = True;

Edited June 15, 2013 by water

[drbyte]

Just tried your solution but i get an error.

See line's below.

I:DataAutoit privateVRM-NP-v1.01.au3 (448) : ==> The requested action with this object has failed.:

$MyExcel.ActiveSheet.Range("R9C" & $j & ":R9C" & $j + 1).MergeCells = True

$MyExcel.ActiveSheet.Range("R9C" & $j & ":R9C" & $j + 1)^ ERROR

 

Below the code i use at this moment.

 

If $i > 11 Then ; merge two inserted cells into one cell
        For $j = 23 to 30 Step 1
            MsgBox(0,"",$i)
            MsgBox(0,"",$j)
                    ;~  ConsoleWrite("R9C" & $j & ":R9C" & $j + 1)
            $MyExcel.ActiveSheet.Range("R9C" & $j & ":R9C" & $j + 1).MergeCells = True;
              $j=$j+1               
        Next
    EndIf

When i write the output to the console i get the result below.

R9C23:R9C24

R9C25:R9C26

R9C27:R9C28

R9C29:R9C30

This is the ouput i want and is correct.

 

Does it need to start and end with quotes?

"R9C23:R9C24" ?, just tried, still the error message?

Edited June 15, 2013 by drbyte

[water]

Oopps, my bad!

Should be

$j = 8
; Solution 1
$oAppl.ActiveSheet.Range($oAppl.ActiveSheet.Cells(9, $j), $oAppl.ActiveSheet.Cells(9, $j + 1)).MergeCells = True
; Solution 2
With $oAppl.ActiveSheet
    .Range(.Cells(10, $j), .Cells(10, $j + 1)).MergeCells = True
EndWith

[drbyte]

Both your solutions work like a charm.

With this knowledge i'am going to try to fill the sheet with data.

Thanks a lot !

Edited June 15, 2013 by drbyte

[water]

Glad it works for you!

If you have further questions I will be happy to assist.

[drbyte]

There is one small problem left.

I have problems with inserting the array into the merged cells.

The insertion needs to start at C9.

See the code below i use and the picture to explain what i want.

For $j = 1 To ($i - 1) Step 1
        $MyExcel.ActiveSheet.Cells(9, $j).value = $aArray1[$j]
    Next
    _ExcelWriteArray($MyExcel, 11, 3, $aArray1, 0)

 

Edited June 15, 2013 by drbyte

[water]

Looks like you can't use _ExcelWriteArray because the function tries to write to each cell in the specified range.

You can write data to the topleft cell of a merged range but data you write to other cells of a merged range is lost.

You need to loop through the table and use _ExcelWriteCell to write the data to each 2nd cell.

[drbyte]

The method _ExcelWriteCell works perfect.

Project almost finished now!

Below the code i use to fill the merged cells with the array.

$startcolumn = 3
    For $j = 0 To ($i - 1) Step 1
        _ExcelWriteCell($MyExcel, $aArray1[$j], 9, $startcolumn)
        $startcolumn = $startcolumn + 2
    Next