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

"Diggy" <Diggy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:698D68B0-8E3D-4FA9-B6A8-41D0D0BBBA01@xxxxxxxxxxxxxxxx
> My Sheet1 is named Blad1 below. I have tried everthing, but it still
> doesn't
> work.
> Please, any suggestions?
>
> im aCon As ADODB.Connection
> Dim aRec As ADODB.Recordset
> Dim sqlQ As String
> Dim aDbf As String
> aDbf = "C:\Documents and Settings\Pappa\Mina
> dokument\SVOInventeringar\RAHLP\Data\FastighetskartanBladNamn.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
> 'Make a Query over the connection
> sqlQ = "SELECT Kartindex, Ortnamn, Kartblad FROM [Blad1$] where
> Kartindex='"
> & MapIndex & "'"
> End With
> '
> 'Define and open the recordset
> Set aRec = New ADODB.Recordset
> 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
> If aRec.Fields(0) = MapIndex Then
> Me.txtFastighetskarta.Text = CLng((aRec.Fields(2)))
> End If
> aRec.MoveNext
> Loop
> '
> aRec.Close
> aCon.Close
> '
> Set aRec = Nothing
> Set aCon = Nothing
> '
> If txtFastighetskarta = "" Then MsgBox "No Kartblad Found"
> '
> Err_Label:
> If Err.Number <> 0 Then
> MsgBox "Error Description: " & Err.Description
> End If

Try changing

.ConnectionString = "Data Source=aDbf;" & "Extended Properties=Excel 8.0;"

to

.ConnectionString = "Data Source=" & aDbf & ";Extended Properties=Excel
8.0;"

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


.