Solved

VBA - Copy  and paste in MSHFlexgrid

Posted on 2013-01-07
8
568 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now