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

Saving to the same file has always resolved this type of problem for me in
the situation I describe. So I guess that's not the problem. Nice try
though, eh?

Paul

"Diggy" <Diggy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:35875C71-3BE6-4937-AC69-5FD694D1E8F3@xxxxxxxxxxxxxxxx
> Hello Paul, thanks for your fast reply.
> The excel file was created with ms excel. And yes, I have opened the file
> and used "save as", however to the same file name. Still the same problem.
> When I run the code to the lines prior to the msgbox "Search the
> recordset",
> it seems to work. But, when I run the could to the end of that msgbox, the
> problem reveals... So, I am in deep trouble, and haven't got the any idea!
>
> Regards Diggy
>
> "Paul Baker" wrote:
>
>> How was the Excel file created? Does opening the file in Excel, then
>> saving
>> a copy of it resolve the problem?
>>
>> There are some Excel files created by third party software that can be
>> opened by Excel but not using the Microsoft Jet 4.0 OLE DB Provider,
>> becaise
>> ot doesn't see the "tables". My best guess is that there is an OLE DB
>> table
>> schema in the header of an Excel file that is not maintained by third
>> party
>> software.
>>
>> Paul
>>
>> "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
>>
>>
>>


.