[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

loading values from spreadsheet to msflexgrid.

Posted on 2005-04-18
1
Medium Priority
?
216 Views
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.

0
Comment
Question by:Aiysha
1 Comment
 
LVL 4

Accepted Solution

by:
senthil_msv earned 2000 total points
ID: 13814630
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 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