Sasha

Here's some code I use to load an ADO recordset from an Excel spreadsheet.
First, go into the spreadsheet, highlight everything to be copied, name the
highlighted range "MyTable", then use the following code snippet, adapted as
required. If you include the column names in the highlight, these will
appear in the first row of the recordset.

	strExtProp = "Excel 8.0" (or "Excel 97")
	FileName =  Excel file you want to copy from

    	Set adoConnection = New ADODB.Connection
    	Set adoRsFile = New ADODB.Recordset
    	strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
    	strConn = strConn & "Data Source="
    	strConn = strConn & FileName & ";"
   	strConn = strConn & "Extended Properties=" & strExtProp & ";"
'Open the connection
    adoConnection.Open strConn
'Open recordset
    	adoRsFile.Open "MyTable", adoConnection, adOpenKeyset, adLockReadOnly
'Show recordset in flexgrid
	MSHFlexGrid1.DataSource = adoRsFile

I hope this is some help. Of course, this technique is no help if you can't
get to name the range in the spreadsheet before the connection is made.



-----Original Message-----
From: Sasha Damjanovic [mailto:damjanovic2@y...]
Sent: March 13, 2001 16:29
To: professional vb
Subject: [pro_vb] Efficiently Retrieve Excel Spreadsheet Data into
FlexGrid



Hey All,

One of the forms in my app has a MSFlexGrid which is used for view only
purposes.  The function that loads the data into the grid has to loop thru
the rows and columns of the spreadsheet.  The time it takes to load this
is like 30+ seconds - Way too long!.  Any ideas as to how I could
implement this another way?  I'm thinking I should try and create a
recordset object from the spreadsheet and then bind it to the
MSFlexGrid.datasource property?  Now how do I do that...I have dataGrid's
that are bound to recordset's through ADO to get data from Access - that's
easy enough.

Well here's the function that loads the grid - (Don't laugh, it works).

-SD

 sht is declared as Excel.Worksheet in my module.


Function LoadGrid(msfxGrid As MSFlexGrid, shtName As String, iColCnt As
Integer, iRowCnt As Integer)
    'FUNCTION LOADS FLEX GRID CONTROL MSFlexGrid WITH DATA FROM XL
SPREADSHEET shtName.

    Dim j As Integer, K As Integer, intAscii As Integer
    Dim strFormat As String, strRow As String

    'IGNORE===========================
    'For j = 1 To UBound(v)
    '    If v(j) = cboSelectSheet Then
    '===========================END IGNORE

    Set sht = wkbk.Worksheets(shtName)

    'IGNORE===========================
    '        Exit For
    '    End If
    'Next j
    '===========================END IGNORE

    '===================================
    'LOAD DATA INTO FLEX GRID CONTROL.
    '===================================
    With msfxGrid
        .Clear
        .Rows = 1
        .Cols = iColCnt
    End With

    'CREATE HEADER ROW FOR COLUMN DESCRIPTION.

    strFormat = ""
    'BUILD MsFlexGrid1.FormatString.
    intAscii = 65
    For K = 1 To iColCnt
        strFormat = strFormat & "|" & Chr(intAscii)
        intAscii = intAscii + 1
    Next K
    msfxGrid.FormatString = strFormat

    strRow = ""

    For j = 1 To iRowCnt
    'BUILD THE STRING FOR THE ROW CELLS AND ADD TO GRID.
        strRow = j
        For K = 1 To iColCnt

            'ERROR HANDLER IN CASE CELL CONTAINS #N/A.
            On Error Resume Next
            strRow = strRow & vbTab & sht.Cells(j, K)
            If (Err.Number = 13) And (sht.Cells(j, K) = "#N/A") Then
                strRow = strRow & vbTab & "N/A"
                Err.Clear
            End If

        Next K

        msfxGrid.AddItem strRow
    Next j


    '===================================
    'ADJUST WIDTH OF COLUMNS.
    '===================================
    msfxGrid.ColWidth(0) = 400

    For j = 1 To iColCnt
        msfxGrid.ColWidth(j) = 1700
    Next j

    'CAPTURE THE WORKSHEET NAME (shtName) FOR FUTURE USE.
    strSelectedSheet = shtName

End Function