VBA - Copy and paste in MSHFlexgrid

Hello all

I want to be able to copy multiple row in excel and paste the data in my MSHFlexgrid1.

I tried like this but no error and does not paste the data:
 Dim ARRAYLINES As Variant    ' array with the lines
    Dim ARRAYCELLS As Variant    ' array with the cells of 1 line to count the cols needed
    Dim ARRAYLINESidx As Integer
    '§ put clipboard in textbox
    With Text2
        .Text = PutInClipboard
        If .Text = "" Then
            MsgBox "No data in clipboard"
            Exit Sub
        Else
            ARRAYLINES = Split(.Text, vbNewLine)
        End If
    End With
    '§ put textbox in grid
    If ARRAYLINES(0) = "" Then
        MsgBox "No data"
        Exit Sub
    Else
        ARRAYCELLS = Split(ARRAYLINES(0), vbTab)
        With MSHFlexGrid1
            .Cols = UBound(ARRAYCELLS) + 1
            .Rows = 0    '§ keep header
            For ARRAYLINESidx = LBound(ARRAYLINES) To UBound(ARRAYLINES) - 1
                .AddItem (ARRAYLINES(ARRAYLINESidx))
                '.AddItem Trim(ARRAYLINES(ARRAYLINESidx))
            Next
        End With
    End If
    '§ clear array
    ReDim ARRAYLINES(0)
    ReDim ARRAYCELLS(0)

Open in new window


How can i do that?


EX:
this is from excel copy to clipboard:
copy from excel
When i past in MSHFlexgrid1:
paste in grid
Thanks
test-copy-paste.xlsm
LVL 11
Wilder1626Asked:
Who is Participating?
 
terencinoConnect With a Mentor Commented:
My comment above refers to the first set of code. For the second set, VBA doesn't use Clipboard, so instead of Clipboard.GetText just use Text2.Text to use the data pasted into your textbox
0
 
Wilder1626Author Commented:
I have also tried like this but now i also get an error:

Runtime error 424 object require

Dim jCol, Values_Col, iRow, Values_Text
MSHFlexGrid1.Rows = 0
MSHFlexGrid1.Cols = 0

Values_Col = Split(Clipboard.GetText, vbCr)

For jCol = 0 To UBound(Values_Col) - 1
    MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
    Values_Text = Split(Values_Col(jCol), vbTab)
    For iRow = 0 To UBound(Values_Text)
        If jCol = 0 Then
            MSHFlexGrid1.Cols = MSHFlexGrid1.Cols + 1
        End If
        MSHFlexGrid1.TextMatrix(jCol, iRow) = Trim(Values_Text(iRow))
    Next
Next

Open in new window

0
 
terencinoCommented:
Hi Wilder1626, I don't have the flex grid control, but the initial problem with the code is the line .Text = PutInClipboard which effectively clears the textbox, therefore there is nothing to put in the flex grid.

Since your userform and the data are both in Excel, you can assign the data directly to the ARRAYCELLS array like this:

ARRAYCELLS = Feuil2.UsedRange.Value

So you can bypass the Text2 textbox and the ARRAYLINES array, and just work with the ARRAYCELLS array.

Does that make sense?
...Terry
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Wilder1626Author Commented:
The thing is that i will copy the data from another excel file and paste it in the grid from another excel file.
0
 
terencinoCommented:
In that case then
ARRAYCELLS = Workbooks("otherworkbook.xls").Sheets("datasheet").UsedRange.Value
The thing is you don't need to copy and paste now, just assign the range to an array.

There are other ways of doing it, for example using a RefEdit control on your form to navigate to the range.
0
 
Wilder1626Author Commented:
So if i understand, i cannot copy to clipboard from 1 excel file and pate it in another excel file grid without putting a link between the 2 of them?

The thing is that what i copy to clipboard comes from multiple excel file send by email that i want to paste in a master file i have.
0
 
terencinoCommented:
Well it's just that the copy & paste process is not required. You are not linking the files, just assigning a range in one file to an array in another, which subsequently populates your flex grid. The effect is the same, just the clipboard is not involved.

But you can still copy and paste to the textbox if you want. First you need to make your textbox multi-line, it only accepts the first line of the pasted data at the moment. So set the Multiline property to True. Then delete this line from your code
.Text = PutInClipboard

Let me know how it goes
0
 
Wilder1626Author Commented:
wow, now it works great.

Now i paste in Text2 and use this macro bellow and all work great.

Thanks again for your help.

Dim ARRAYLINES As Variant    ' array with the lines
    Dim ARRAYCELLS As Variant    ' array with the cells of 1 line to count the cols needed
    Dim ARRAYLINESidx As Integer
    '§ put clipboard in textbox
    With Text2
        .Text = Text2
        If .Text = "" Then
            MsgBox "No data in the textbox"
            Exit Sub
        Else
            ARRAYLINES = Split(.Text, vbNewLine)
        End If
    End With
    '§ put textbox in grid
    If ARRAYLINES(0) = "" Then
        MsgBox "No data"
        Exit Sub
    Else
        ARRAYCELLS = Split(ARRAYLINES(0), vbTab)
        With MSHFlexGrid1
            .Cols = UBound(ARRAYCELLS) + 1
            .Rows = 0    '§ keep header
            For ARRAYLINESidx = LBound(ARRAYLINES) To UBound(ARRAYLINES) - 1
                .AddItem (ARRAYLINES(ARRAYLINESidx))
                '.AddItem Trim(ARRAYLINES(ARRAYLINESidx))
            Next
        End With
    End If
    '§ clear array
    ReDim ARRAYLINES(0)
    ReDim ARRAYCELLS(0)

    'Trim all grid cell
    Dim lngRow As Long
    Dim lngCol As Long

    For lngRow = 0 To MSHFlexGrid1.Rows - 1
        For lngCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.TextMatrix(lngRow, lngCol) = Trim(MSHFlexGrid1.TextMatrix(lngRow, lngCol))
            MSHFlexGrid1.TextMatrix(lngRow, lngCol) = UCase(MSHFlexGrid1.TextMatrix(lngRow, lngCol))
        Next
    Next
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.