Sign in to follow this  
Followers 0
drbyte

Insert copied columns in excel

20 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

@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 by drbyte

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

@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.

excel.jpg

Edited by drbyte

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

@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.....

array.jpg

Edited by drbyte

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I'll wait for your solution  :idea:

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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]

excel_2.jpg

Edited by drbyte

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

excel_2.jpg

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

Both your solutions work like a charm.

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

Thanks a lot !

Edited by drbyte

Share this post


Link to post
Share on other sites

Glad it works for you!

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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)

 

excel_3.jpg

Edited by drbyte

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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