• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

loading values from spreadsheet to msflexgrid.

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.

0
Aiysha
Asked:
Aiysha
1 Solution
 
senthil_msvCommented:
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
checkNextcol:
            '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
        Do
            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
checkAllcol:
                If Trim(.Cells(lngRowNum, lngC)) = "" Then
                    If lngC >= lngTotalCols Then Exit For
                    lngC = lngC + 1
                    bolTemp = True
                    GoTo checkAllcol
                Else
                    bolTemp = False
                    Exit For
                End If
            Next lngC
           
            If bolTemp = True Then Exit Do
           
            lngRowNum = lngRowNum + 1
        Loop
    End With
    oExWb.Close
    oExApp.Quit
    Set oExWs = Nothing
    Set oExWb = Nothing
    Set oExApp = Nothing
Exit Sub
ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description, vbInformation
end sub
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now