[RESOLVED] How to load whole data from excel to msflexgrid

I refer to the example from here: http://www.vbforums.com/showthread.php?t=393082

The (msflexgrid to excel) was working well. The problem is (excel to msflexgrid).

Problem:
--------
1. My msflexgrid default as 20 row. If the excel contain more than 20 row of data. When i load the data. The number of row of msflexgrid did not increase, it mantains 20 row of data only.

2..Range("A1:F7").Copy set the limit of data load from excel to msflexgrid. How do i load all the data from excel to msflexgrid without range limited?

3.Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") default the direction of the excel file. Can i manually choosing the file myself like using browse?

Moved From The CodeBank

Good point about the Range, i don't know why i didn't use UsedRange in the first place. So, about your Questions (1) and (2), the solution is using UsedRange, try this new version..

Code:

---

Private Sub ExcelToFlexgrid()
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
Dim xSheet      As Excel.Worksheet

    Set xlObject = New Excel.Application
    Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here

    Clipboard.Clear
    Set xSheet = xlObject.ActiveWorkbook.ActiveSheet
    MSFlexGrid1.Rows = xSheet.UsedRange.Rows.Count
    MSFlexGrid1.Cols = xSheet.UsedRange.Columns.Count
    xSheet.UsedRange.Copy
   
    With MSFlexGrid1
        .Redraw = False 'Dont draw until the end, so we avoid that flash
        .Row = 0 'Paste from first cell
        .Col = 0
        .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
        .ColSel = .Cols - 1
        .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
        .Col = 1 'Just to remove that blue selection from Flexgrid
        .Redraw = True 'Now draw
    End With
   
    xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
   
    'Close Excel
    xlWB.Close
    xlObject.Application.Quit
    Set xSheet = Nothing
    Set xlWB = Nothing
    Set xlObject = Nothing
End Sub

---

About your question (3) you just need to add the component "Microsoft Common Dialog" to your project and some code, there many examples about Common Dialog here in the forums you'll find them with a Search. When you finally have the path where the xls file is, you just need to replace part in red in this line with the path selected in Common Dialog (CommonDialog1.Filename)

Code:

---

Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls")

---

jcis, thanks a lot for your guide. It's really help me.