How to FIND of FILTER in an excel-sheet

[wijenjoh]

I want to select several records (or rows) with the same cell value in column 1 and put them in a combobox. What can I do best? Filter the sheet for these rows (A)? Or use Find and then AddItem within a loop (B)?

(A) 'filter on brand which is first column
strFilter = "Brand = " & strBrandSelected
rsToplease.Filter = strFilter
'fill combobox
Do Until rsToplease.EOF
varTypeSelection = rsToplease!Type
'Type is 2nd column
cboType.AddItem varTypeSelection
rsToplease.MoveNext
Loop
'close filter
rsToplease.Filter = ""
(A)>>> Run-time error '91'
Object variable of With block variable not set

(B) strFind = "Brand = " & strBrandSelected
Do Until rsToplease.EOF
rsToplease.Find strFind
varTypeSelection = rsToplease!Type
cboType.AddItem varTypeSelection
Loop
(B)>>>Compile error, Method or datamendber not found

John Wijen

 

[MichaelRed]

I completly do not understand.

You ask "which is better" and show code w/ comments indicating errors. Yet code does not show the declaration and instantation of objects related to errors.

Please elaborate somewhat on your wnats/needs/desires.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[wijenjoh]

My question is more: how to select data in an excel sheet and then put them in a combobox. I tried to filter records but that didn't work.
Variables were declared as following:

In Private sub cboBrand_Click():
dim strFilter as string
dim strBrandSelected as string
dim varTypeSelected as variant

In General Declarations:
Option Explicit
Private rsToplease As Recordset

Then I tried to use the Find function. This also didn't work. Variables were again declared in the same way.(strFind instead of strFilter)

I hope that you can help me now. If you do not understand it yet, I will be glad to explain better.

John Wijen

 

[MichaelRed]

Well, I still do not understand.

But my not understanding may be somewhat less.

"rsToplease" is the Spreadsheet? Somewhere? this must be declared as part of some database - unless you are using ADO Redordsets, but even then you need to have some statement regarding the 'more-or-less' source. It has the exact and explicit and complete path to the spreadsheet(?).

The other approach would be simply to declare the Excel as an application object and "open" the specific Spreadsheet/workbook object in Excel. then, you could retrieve the "Cells" in several other approaches.

The code you posted suggest that you are opneing the Excel Workbook/sheet as a recordset object in Ms. Access (or at least using Ms. Access Database functions).

In this later vein, you should have a declaration of the database or the ADO declaration of the connection. In either instance there needs to be some statement specifying the 'path' to the data 'residence' - e.g. the Spreasdsheet.

While there are additional considerations, I think it is necessary to calrify this point before porceeding.

Perhaps others understand your question better than I?




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[wijenjoh]

We are currently using an ADO coneection to connect with the excel worksheet. THe connection is working fine and it is possible to extract data from the worksheet and put it in a flexgrid. However, now we want to select specific data (a specific brand) from 1 column (which would be like searching a column from the worksheet) and put this in a combobox. One method we tried was filtering (as you can see from the first post) but this gives and error. Another method we tried was using find, this also gives an error. Hope this gives u a better explanation and that u can help us solve this problem.

this is the code used to declare and make the coneection:
(it works fine!)

strSoortVerbinding = "Microsoft.jet.OLEDB.4.0"
strBestandsDirectory = "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\"
strBestand = "autoprijzen_mei01.xls;"
strExtraEigenschappen = "Extended Properties=Excel 8.0;" '8.0 = excel office 97, 7.0 = office 95

Set cnExcelConnectie = New ADODB.Connection 'create new instance
'make a connection with the excelsheet
With cnExcelConnectie
.Provider = strSoortVerbinding
.ConnectionString = strBestandsDirectory & strBestand & strExtraEigenschappen
.Open
End With

Set rsToplease = New ADODB.Recordset 'create a new recordset

'open recordset

rsToplease.Open "select [toplease$].[Merk] as Merk, [toplease$].[type] as Type from [toplease$]", cnExcelConnectie, adOpenDynamic, adLockOptimistic

John Wijen, Sujesh Kalloe

 

[wijenjoh]

After carefull consideration we reshuffled our program and now it works. We now have a uniform declaration of connections and recordsets. Thank you for your time and effort.