Reading Data From Excel 1

[Skittle]

Is it possible to set up an ODBC or OLEDB link to an Excel document and read data from it into a VB recordset in the same way you would connect and read an Access database?

I have tried coding a program exactly the same way as I would code an Access program and changed the connection string to:-

ConDirect1.ConnectionString ="Provider=MSDASQL.1;Per" & _
"sist Security Info=False;" & _
"Data Source=Budgets;Mode=" & _
"Read;Initial Catalog=C:\" & _
"Program Files\Microsoft " & _
"Visual Studio\VB98" & _
"\ReadBudgets\\Examples.xls"

When I try to access the Excel file via an SQL command in VB I get the error:-


'The microsoft Jet database engine could not find the object ExampleNewFormat.'

Sooo... can it be done?
Should I be doing it a different way?



Dazed and confused

 

[Swi]

Dim CONN As ADODB.Connection
Dim RS As ADODB.Recordset
Private Sub Command1_Click()
Set CONN = New ADODB.Connection
Set RS = New ADODB.Recordset
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
With RS
.Open "SELECT * FROM [Sheet1$]", CONN, adOpenDynamic, adLockOptimistic
.MoveFirst
End With
Do Until RS.EOF
MsgBox RS.Fields(0)
RS.MoveNext
Loop
End Sub

Swi

 

[Skittle]

Thanks matey.

The bit I was missing was the '[', the ']' and the '$'.
Many thanks.

Dazed and confused

 

[pigpig504]

It's ok to run at the PC install with Office,
but failed to find the object [Sheet1$] if I run it at the PC WITHOUT Office.

Any idea to reslove it?? Thousand thanks!!

 

[Swi]

Are you sure the sheet is actually called Sheet1?

Swi

 

[pigpig504]

Swi,

Yes, the program DOES work fine on a PC WITH Office, but error occurs when I run it on another PC WITHOUT Office =(

It said cannot find the object [Sheet1$].

 

[SemperFiDownUnda]

ConDirect1.ConnectionString ="Provider=MSDASQL.1;Per" & _  
                             "sist Security Info=False;" & _
                             "Data Source=Budgets;Mode=" & _
                             "Read;Initial Catalog=C:\" & _
                             "Program Files\Microsoft " & _
                             "Visual Studio\VB98" & _
                             "\ReadBudgets\

\

Examples.xls"

is the 2nd \ supposed to be in the ConnectionString above?

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[durda]

Ive used Swi (Programmer) code for my program as an example and it works great.

The problem I am having is that I do not know how to specify a column, the way the code is written will place column A (in excel) into RS. I guess this is by default.

How do I specify a column, I actually need to read multiple columns.

any advice, thanks

 

[johnwm]

The code should pick up all data. Fields(0) refers to the first column of data, so Column C would be in Fields(2) for instance.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[durda]

hey johnwm,
if you ever see this, since im using adodb to connect to excel do you know if its possible to do the following. I can write to the file based of your reply, thanks.

the problem is that i do not know how to write the cell format structure, i can write to a cell but when i goto view the cell in excel its formatting is gone. im particulary concerned with font, fontsize and cell border.

can I write the pre-format condition of the cell (before i write to it) to a variable and then after writing its contents re-write the format info to format the cell or... you know, something like that.