p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

pro_vb thread: RE: Efficiently Retrieve Excel Spreadsheet Data into FlexGrid


Message #1 by Sasha Damjanovic <damjanovic2@y...> on Sat, 31 Mar 2001 14:10:28 -0800 (PST)
Hi Sasha

Just after the End With in your code, try this line

Set MSHFlexGrid1.DataSource = ADODBrsWkSht

Here's the complete code for opening an ADO recordset from an Access
database and binding it to a flexgrid, which you find helpful as a model to
use as a test.

Regards

Gordon



Public Sub LoadProduct()
    Dim adoConnection As ADODB.Connection
    Dim adoRsFile As ADODB.Recordset
    Set adoConnection = New ADODB.Connection
    Set adoRsFile = New ADODB.Recordset
    adoConnection.CursorLocation = adUseClient
'Assemble connect string for supplier database
    sFullConnStrMDB = strConnectMDB & "MyDataBase.mdb"
    adoConnection.Open sFullConnStrMDB
'Assemble SQL statement
    sSQL = "Select * From " & MyTable
    adoRsFile.Open sSQL, adoConnection, adOpenKeyset, adLockOptimistic,
adCmdText
    Set MSHFlexGrid1.DataSource = adoRsFile
'Do nothings
    adoRsFile.Close
    Set adoRsFile = Nothing
    adoConnection.Close
    Set adoConnection = Nothing
End Sub




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


Dr,

Your code works wonderful!  I'm able to get a
recordset object and print the data.

What I can't get to work is binding the recordset to
the flexgrid.

I tried a dataGrid too.  Any ideas? I'm probably
overlooking something fundamental.

The error I get is # 91 'object variable or with
variable not set'

Thanks,

Sasha

Here's what I wrote:

Function DisplayWorksheetInGrid()
    'FUNCTION TO LOAD MSFLEXGRID WITH WORKSHEET DATA
PREVIOUSLY IMPORTED FOR END USER REFERENCE.

    Set cXL_V = basAllObjRef.GetObjRef(IsWorkbook)
    strShtName = cXL_V.GetSheetName
    Set shtV = cXL_V.GetSheetObj
    'LOAD GRID.
    'cXL_V.LoadGrid msfxWorkSheet, strShtName, 20, 250

    Set ADODBrsWkSht 
cXL_V.WorkSheetRangeToDataSource

    With ADODBrsWkSht
        .MoveFirst
        Do While Not .EOF
            Debug.Print .Fields(0) & "  " & .Fields(1)
& "  " & .Fields(2) & "  " & .Fields(3) & "  " &
.Fields(4) & "  " & .Fields(5)
            .MoveNext
        Loop
    End With

'>>>>>>>>>>>>>>>    ERROR   
<<<<<<<<<<<<<<<<<
    Set msfxWorkSheet.DataSource = ADODBrsWkSht

End Function


>>>THIS IS THE FUNCTION IN A CLASS MODULE REFERENCED
AS cXL_V.

Function WorkSheetRangeToDataSource() As
ADODB.Recordset
    '============TEMPORARY
REMOVE============================


    Dim cnnDB As ADODB.Connection
    Dim strConn As String, strExtProp As String

    Set cnnDB = New ADODB.Connection

    ' Specify Excel 8.0 by using the Extended
Properties
    ' property, and then open the Excel file specified
    ' by sFilePath.

    strExtProp = "Excel 8.0"
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strConn = strConn & "Data Source="
    strConn = strConn & sFilePath & ";"
    strConn = strConn & "Extended Properties=" &
strExtProp & ";"

    cnnDB.Open strConn

    Set ADODBrsWkbk = New ADODB.Recordset

    ADODBrsWkbk.Open "D_Range", cnnDB, adOpenKeyset,
adLockReadOnly

    Set WorkSheetRangeToDataSource = ADODBrsWkbk



End Function

--- Dr Gordon Atherley <atherley@s...> wrote:
> 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
> 

  Return to Index