loading values from spreadsheet to msflexgrid.

Posted on 2005-04-18
Last Modified: 2010-05-02
I want to load values from spreadsheet to mshflexgrid through a command button.

My spreadsheet has data in columns start at row3. Someone please let me know if I need to predefine the columns in msflexgrid before loading the data or its simply setting the mshflex grid matrix to spreadsheet cells.

Thank you in advance.

Question by:Aiysha
    1 Comment
    LVL 4

    Accepted Solution

    Dim oExApp  As New Excel.Application
    Dim oExWb   As New Excel.Workbook
    Dim oExWs   As New Excel.Worksheet

    Private Sub Command2_Click()
    On Error GoTo ErrorHandler
        Dim strFName    As String
        Dim lngR        As Long, lngRowNum    As Long, lngC As Long
        Dim lngTotalCols    As Long
        Dim bolTemp As Boolean
        strFName = App.Path & "\Reports.xls"
        Set oExApp = CreateObject("Excel.Application")
        Set oExWb = oExApp.Workbooks.Open(strFName)
        Set oExWs = oExWb.Sheets("Sheet1")

        With oExWs
            lngRowNum = 1   'Starting Row Number
            For lngC = 1 To 256 ' Max no of cols in excel, here you can set any number
                'here i check the empty columns. if continuosly two columns comes to blank i stop that loop
                'and set the maximum no of cols in grid
                If Trim(.Cells(lngRowNum, lngC)) = "" Then
                    If bolTemp Then lngTotalCols = lngC - 2: Exit For
                    lngC = lngC + 1
                    bolTemp = True
                    GoTo checkNextcol
                End If
            Next lngC
            'Assign the grid properties
            MSFlexGrid1.Rows = 1
            MSFlexGrid1.Cols = lngTotalCols
            lngR = 0
            lngRowNum = 1   'Starting Row Number
            bolTemp = False
                MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
                lngR = lngR + 1
                'Assign the excel contents into grid
                For lngC = 1 To lngTotalCols
                    MSFlexGrid1.TextMatrix(lngR, lngC - 1) = .Cells(lngRowNum, lngC)
                Next lngC
                For lngC = 1 To lngTotalCols
                    'Here if any empty row has come, it will stop the excel to grid conversion
                    If Trim(.Cells(lngRowNum, lngC)) = "" Then
                        If lngC >= lngTotalCols Then Exit For
                        lngC = lngC + 1
                        bolTemp = True
                        GoTo checkAllcol
                        bolTemp = False
                        Exit For
                    End If
                Next lngC
                If bolTemp = True Then Exit Do
                lngRowNum = lngRowNum + 1
        End With
        Set oExWs = Nothing
        Set oExWb = Nothing
        Set oExApp = Nothing
    Exit Sub
        MsgBox Err.Number & " - " & Err.Description, vbInformation
    end sub

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    729 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

    22 Experts available now in Live!

    Get 1:1 Help Now