Re: Cant find Sheet1. Whats wrong with this code?

"Diggy" <Diggy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7618745C-63FD-43B5-AEA0-78C976619008@xxxxxxxxxxxxxxxx
> Hi Frank, I've changed the code to
>
> Do Until aRec.EOF
> If Str(aRec.Fields(0)) = MapIndex Then
> Me.txtFastighetskarta.Text = Str(aRec.Fields(2))
> End If
> aRec.MoveNext
> Loop
>
> Still doesn't work, but now I get "The object could not be found in the.."
> could there be something wrong with the sql string?
>
>

This works fine for me but I made assumptions on some of the data types

Private Sub Test(MapIndex As Long)
Dim aCon As ADODB.Connection
Dim aRec As ADODB.Recordset
Dim sqlQ As String
Dim aDbf As String

aDbf = "C:\Documents and Settings\FHickman\My Documents\Book2.xls"
'
On Error GoTo Err_Label
'
'Define and open the connection
Set aCon = New ADODB.Connection
With aCon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & aDbf & ";Extended
Properties=Excel 8.0;"
.Open
End With
'
'Define and open the recordset
Set aRec = New ADODB.Recordset

'Make a Query over the connection
sqlQ = "SELECT Kartindex, Ortnamn, Kartblad FROM [Blad1$] WHERE
Kartindex=" & MapIndex & ""
aRec.Open sqlQ, aCon, adOpenStatic, adLockReadOnly, adCmdText
'
'Note: Column A is Fields(0), column B is Fields(1), column C is
Fields(2)
'MsgBox "Search the recordset"
Do Until aRec.EOF
' Note that this check is not really needed because you are using
the WHERE clause...
If aRec.Fields(0) = MapIndex Then
Me.txtFastighetskarta.Text = CStr(aRec.Fields(2))
End If
aRec.MoveNext
Loop
'
aRec.Close
aCon.Close
'
Set aRec = Nothing
Set aCon = Nothing
'
If txtFastighetskarta.Text = "" Then MsgBox "No Kartblad Found"
'
' When using an error message label like this do not forget the Exit
' Sub or you will end up displaying an erroneous error message.
Exit Sub

Err_Label:
If Err.Number <> 0 Then
MsgBox "Error Description: " & Err.Description
End If
End Sub


In my Excel spread*** the data is laid out like this...

Kartindex Ortnamn Kartblad
1 15 30
2 16 32
3 17 34
4 18 36
5 19 38
6 20 40


Which assumes all numeric data.

--
============
Frank Hickman
Microsoft MVP
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.


.