ADO Excel - Slow to Read from Spreadsheet

[caerdydd]

Hello all
I have some code below which works via the right click menu of an internal application.
Whatever is right clicked (such as company name) takes the focus to the spreadsheet and then finds this name in the excel spreadsheet and returns a piece of information back to the internal app via the msg box.

The code works well and does its job but in returning the information i need takes around 1 minute. The exact area where the process slows down is when performing the sql statement.

Is there a possibility of speeding up the retrieval? I have tried DAO but nothing gets any quicker.

I guess this is the only way of doing this too as the workbook must remain closed as its accesses via an internal app and its used only for information. Or is there another way of doing this?

Any help or suggestions much appreciated.
---------------------------------------------------------
Private Sub Checker_Click(ByVal Button As CommandBarButton, Cancel As Boolean)

Set objConnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

mycompany = Checker.parameter
mysql = "select code from sch where proc_id='" & mycompany & "' and code_sch='SAM'"
result = PMY.INTData("COMPANY", mysql)

If IsArray(result) Then
myresult = result(1)
End If

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Y:\Temp\The_Sheet.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

objrecordset.Open "Select COMPANY FROM [Sheet1$] Where COMPANY='" & myresult & "'", _
objConnection

myvalue = objrecordset.Fields.Item("COMPANY").Value

MsgBox myvalue, vbOKOnly

End Sub

 

[SkipVought]

Hi,

It would seem that myresult & COMPANY will have identical values, or am I missing something?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[caerdydd]

Hi Skip
Yes you are right they will have the same values.

As a test i am trying to find the company in the spreadsheet and then pull back that company name in the message box, when this gets released though it will retrieve an id for the user.

 

[SkipVought]

So what is the added value of this step?

objrecordset.Open "Select COMPANY FROM [Sheet1$] Where COMPANY='" & myresult & "'", _
    objConnection

    myvalue = objrecordset.Fields.Item("COMPANY").Value

Absolutely NOTHING!

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[caerdydd]

As i said before its a test.

What will happen when this goes live is that the company name will be used as the search criteria, if that name is in the spreadsheet then it will retrieve a message to confirm this if not then another message will state that the company has not been found.

Myvalue will be passed to a message box or user form.

So it is crucial to the procedure otherwise I have nothing to lookup