Copy-Paste between Excel and MSFlxGrd.ocx-VBForums
Results 1 to 5 of 5

Thread: Copy-Paste between Excel and MSFlxGrd.ocx

  1. #1

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    2,494

    Copy-Paste between Excel and MSFlxGrd.ocx

    Okay, with a bit of encouragement, I've got a project I'm going to embark on. I've got huge chunks of it lying around, but I'm going to try and pull it all together into something coherent. Alright, I'll spill the beans. I'm going to (slowly) start on pulling together a reasonable alternative to SPSS all written in VB6. But some caveats are in order. I'm certainly not going to try and replicate every SPSS feature. In fact, for the most part, I do all my data massaging in Excel, and then copy-paste into SPSS to generate p-values and confidence intervals. And that's the approach I'll take here.

    But the very first task is a way to reasonably manage datasets. As I said, I think Excel is uniquely suited to do this. However, we must get these datasets into VB6 to be able to analyze them.

    My thought is to use the FlexGrid. I think it'll serve the purpose quite well. No need for editing of data (as that'll be done in Excel). But I do need a way to easily move data between Excel and the FlexGrid. I was thinking of just some buttons that say something like "Clear Grid", "Copy Grid as Tab/CrLf Delimited", and "Paste Tab/CrLf Delimited (Excel) to Grid". I'm pretty sure I can manage this, but I thought I'd bounce it off the crowd.

    Many Thanks,
    Elroy

    p.s. Once some chucks are completed into semi-polished segments, I'll make an entry into the codebank.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    38,950

    Re: Copy-Paste between Excel and MSFlxGrd.ocx

    In terms of Copy+Paste, simply use the .Clip property (I think I got the name right), as it converts the cells to+from Tab/CrLf Delimited.

    Editing with a FlexGrid isn't too hard, you just need a textbox (or other apt controls) which you then show in the right place, I'm pretty sure the CodeBank has examples.

  3. #3

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    2,494

    Re: Copy-Paste between Excel and MSFlxGrd.ocx

    Hmmm, well, I'm assuming the user can figure out how to do Ctrl-C and Ctrl-V in Excel. It's the VB6 side I'm more worried about.

    And, since my OP, I've charged off and done it. Here's the code I've come up with, and it's also attached as a ZIP. I'm open to people hammering on it with "anything" in the clipboard. I think I've made it pretty bulletproof, and it does seem to work well for my version of Excel.

    Code:
    Option Explicit
    
    Private Sub cmdClear_Click()
        ClearData
    End Sub
    
    Private Sub cmdCopy_Click()
        Dim iRet As Long
        Dim bHeader As Boolean
        Dim s As String
        Dim rGridPtr As Long
        Dim cGridPtr As Long
        Dim rGridStart As Long
        '
        ' Include header row?
        iRet = MsgBox("Include the header row (variable names) in the clipboard?", vbYesNoCancel Or vbQuestion, App.Title)
        If iRet = vbCancel Then Exit Sub
        bHeader = (iRet = vbYes)
        If bHeader Then rGridStart = 0 Else rGridStart = 1
        '
        ' Build a string.
        For rGridPtr = rGridStart To Data.Rows - 1
            For cGridPtr = 1 To Data.Cols - 2
                s = s & Data.TextMatrix(rGridPtr, cGridPtr) & vbTab
            Next cGridPtr
            ' Get the last column, but don't add a vbTab.  Add vbCrLf instead.
            s = s & Data.TextMatrix(rGridPtr, Data.Cols - 1) & vbCrLf
        Next rGridPtr
        '
        ' Place into clipboard.
        Clipboard.Clear
        Clipboard.SetText s
    End Sub
    
    Private Sub cmdPaste_Click()
        Dim iRet As Long
        Dim bHeader As Boolean
        Dim s As String
        Dim sClipRows() As String
        Dim sClipCols() As String
        Dim iColCount As Long ' Including record number col.
        Dim iRowCount As Long ' Including header.
        Dim rGridPtr As Long
        Dim cGridPtr As Long
        Dim rClipPtr As Long
        Dim cClipPtr As Long
        Dim i As Long
        Dim rClipStart As Long
        Dim d As Double
        '
        ' Is there a header row?
        iRet = MsgBox("Note that only valid numeric data will paste." & vbCrLf & vbCrLf & "Is there a header row (variable names) included?" & vbCrLf & "You are responsible for making sure the variable names are unique.", vbYesNoCancel Or vbQuestion, App.Title)
        If iRet = vbCancel Then Exit Sub
        bHeader = (iRet = vbYes)
        '
        ' Start with scrubbed grid.
        ClearData
        '
        ' Parse clipboard into rows.
        s = Clipboard.GetText
        sClipRows = Split(s, vbCrLf)
        If Not IsDimmedStr(sClipRows) Then Exit Sub
        '
        If sClipRows(UBound(sClipRows)) = "" Then ReDim Preserve sClipRows(0 To UBound(sClipRows) - 1)
        iRowCount = UBound(sClipRows) - LBound(sClipRows) + 1
        If Not bHeader Then iRowCount = iRowCount + 1
        '
        ' Figure out max of columns.
        For rClipPtr = LBound(sClipRows) To UBound(sClipRows)
            i = CountOf(vbTab, sClipRows(rClipPtr))
            If i > iColCount Then iColCount = i
        Next rClipPtr
        iColCount = iColCount + 2 ' One for record number column, and one for trailing data after last vbTab.
        '
        ' Make a grid.
        Data.Rows = iRowCount
        Data.Cols = iColCount
        '
        ' Put header row into grid.
        If bHeader Then
            sClipCols = Split(sClipRows(0), vbTab)
            If UBound(sClipCols) = -1 Then ReDim sClipCols(0, iColCount - 2)
            If UBound(sClipCols) < (iColCount - 2) Then ReDim Preserve sClipCols(0, iColCount - 2)
            For cClipPtr = 0 To iColCount - 2
                CellAlign 0, cClipPtr + 1, flexAlignCenterCenter
                If Trim$(sClipCols(cClipPtr)) <> "" Then
                    Data.TextMatrix(0, cClipPtr + 1) = Trim$(sClipCols(cClipPtr))
                Else
                    Data.TextMatrix(0, cClipPtr + 1) = Num2Let(cClipPtr + 1)
                End If
            Next cClipPtr
            rClipStart = 1
        Else
            For cGridPtr = 1 To iColCount - 1
                CellAlign 0, cGridPtr, flexAlignCenterCenter
                Data.TextMatrix(0, cGridPtr) = Num2Let(cGridPtr)
            Next cGridPtr
            rClipStart = 0
        End If
        '
        ' Put record numbers into first column.
        For rGridPtr = 1 To iRowCount - 1
            CellAlign rGridPtr, 0, flexAlignCenterCenter
            Data.TextMatrix(rGridPtr, 0) = Format$(rGridPtr)
        Next rGridPtr
        '
        ' Put data into grid.
        rGridPtr = 1
        For rClipPtr = rClipStart To UBound(sClipRows)
            sClipCols = Split(sClipRows(rClipPtr), vbTab)
            If UBound(sClipCols) = -1 Then ReDim sClipCols(0, iColCount - 2)
            If UBound(sClipCols) < (iColCount - 2) Then ReDim Preserve sClipCols(0, iColCount - 2)
            For cClipPtr = 0 To UBound(sClipCols)
                CellAlign rGridPtr, cClipPtr + 1, flexAlignRightCenter
                If Val(Trim$(sClipCols(cClipPtr))) <> 0 Or Left$(Trim$(sClipCols(cClipPtr)), 1) = "0" Then
                    d = Val(Trim$(sClipCols(cClipPtr)))
                    If d = Int(d) Then
                        Data.TextMatrix(rGridPtr, cClipPtr + 1) = Format$(d, "#0")
                    Else
                        Data.TextMatrix(rGridPtr, cClipPtr + 1) = Format$(d, "#0.0############################")
                    End If
                End If
            Next cClipPtr
            rGridPtr = rGridPtr + 1
        Next rClipPtr
    End Sub
    
    Private Sub ClearData()
        Data.Clear
        Data.Rows = 1
        Data.Cols = 1
    End Sub
    
    Private Function CountOf(sNeedle As String, sHay As String) As Long
        Dim iStart As Long
        Dim iPos As Long
        '
        iStart = 1
        Do
            iPos = InStr(iStart, sHay, sNeedle)
            If iPos = 0 Then Exit Function
            CountOf = CountOf + 1
            iStart = iPos + 1
        Loop
    End Function
    
    Private Function IsDimmedStr(s() As String) As Boolean
        On Error GoTo AnError
        IsDimmedStr = (UBound(s) >= LBound(s))
    AnError:
    End Function
    
    Private Function Let2Num(sLetter As String) As Long
        Select Case Len(sLetter)
        Case 1
            Let2Num = Asc(UCase$(sLetter)) - 64
        Case 2
            Let2Num = ((Asc(UCase$(Left$(sLetter, 1))) - 64) * 26) + Asc(UCase$(Right$(sLetter, 1))) - 64
        End Select
    End Function
    
    Private Function Num2Let(iNumber As Long) As String
        If iNumber > 26 * 26 Then Exit Function
        If iNumber <= 26 Then
            Num2Let = Chr$(iNumber + 64)
        Else
            Num2Let = Chr$((iNumber - 1) \ 26 + 64) & Chr$(((iNumber - 1) Mod 26) + 65)
        End If
    End Function
    
    Private Sub CellAlign(iRow As Long, iCol As Long, iAlign As MSFlexGridLib.AlignmentSettings)
        ' Not that cell selection isn't reset.
        Data.Row = iRow
        Data.Col = iCol
        Data.CellAlignment = iAlign
    End Sub
    I just feel that it's imperative that I give the user a way to peruse their data in VB6 in an organized way. Also, organized in a grid control will make getting to it for analysis fairly easy.

    Again, any suggestions are welcome.

    Regards,
    Elroy

    EDIT1: Ahhh, Si, I re-read your post. I do have code in production I use for editing the FlexGrid, but I'm going to defer that whole thing for a while. For now, I'll assume that all editing will be done on the Excel side. Once I open up the whole editing-bag-of-worms, I'm opening myself up to actually massaging the dataset from within VB6, and that takes me down a very deep and windy rabbit-hole. I'm more interested (at least initially) in being able to analyze the data, and not actually massage it.


    Name:  Image1.jpg
Views: 43
Size:  25.7 KB
    Attached Files Attached Files
    Last edited by Elroy; Jan 11th, 2017 at 05:39 PM.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    38,950

    Re: Copy-Paste between Excel and MSFlxGrd.ocx

    Quote Originally Posted by Elroy View Post
    Hmmm, well, I'm assuming the user can figure out how to do Ctrl-C and Ctrl-V in Excel. It's the VB6 side I'm more worried about.
    I wasn't referring to Excel, but the FlexGrid.

    This code:
    Code:
        If bHeader Then rGridStart = 0 Else rGridStart = 1
        '
        ' Build a string.
        For rGridPtr = rGridStart To Data.Rows - 1
            For cGridPtr = 1 To Data.Cols - 2
                s = s & Data.TextMatrix(rGridPtr, cGridPtr) & vbTab
            Next cGridPtr
            ' Get the last column, but don't add a vbTab.  Add vbCrLf instead.
            s = s & Data.TextMatrix(rGridPtr, Data.Cols - 1) & vbCrLf
        Next rGridPtr
    ...if memory serves correctly, could be replaced by:
    Code:
        ' Get a string.
        Data.Row = IIf(bHeader, 0, 1)
        Data.Col = 1
        Data.RowSel = Data.Rows
        Data.ColSel = Data.Cols
        s = Data.Clip

  5. #5

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    2,494

    Re: Copy-Paste between Excel and MSFlxGrd.ocx

    Ahhhh, coolbeans Si.

    I don't use the flexgrid that often, and I'm still brushing up on all the magic it's got. I'm a bit nervous about how it uses vbCr instead of vbCrLf, but I could easily do a Replace to fix that problem.

    I'll change it. Thanks.

    Elroy


    EDIT1: Hmmmm, I may change it for the "Copy" into the clipboard, but I think I'll leave what I have for the "Paste" from clipboard into the grid. I just feel compelled to do data validation before stuffing things into the grid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.