Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA - Copy  and paste in MSHFlexgrid

Posted on 2013-01-07
8
Medium Priority
?
640 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:Wilder1626
  • 4
  • 4
8 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 38754524
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
 
LVL 16

Expert Comment

by:terencino
ID: 38754570
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38754578
The thing is that i will copy the data from another excel file and paste it in the grid from another excel file.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Expert Comment

by:terencino
ID: 38757361
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38757400
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
 
LVL 16

Expert Comment

by:terencino
ID: 38757439
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
 
LVL 16

Accepted Solution

by:
terencino earned 2000 total points
ID: 38757450
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38758573
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question