Excel Reading with VBA

[BuskyLusky]

Hello, I have a problem concerning the use of VBA to read an excel data sheet. I have searched many forums, but I feel this one is the best qualified. As I understand one should use ADO to read the excel file. Is there anyone who could really give me a good example in how it is done?

From an earlier thread:

http://www.tek-tips.com/viewthread.cfm?qid=1332917&page=1

The code is quite clearly, but don't you need a module (class, etc...) beside the connection to the excel sheet. A module who describes the excel functions.

Any reply will be highly appreciated

Busky

 

[LARiot]

Busky Lusky what do you mean by needing a module who describes the excel functions?

You just type the code from the post in a module (or a control which automatically puts it in a module) and it retrieves the data from excel.

 

[BuskyLusky]

Ok. Forget about ADO and the question above.
I have an excel sheet with 10 collumns and 10 rows.

I wish to read one cell at the time and use them in a formula programmed in VBA.

How can this be done?

 

[LARiot]

You can set it to a range of one. Starting with cell A and ending with cell A

strSQL = "SELECT * FROM [Summary$A:A]"

I think this is what you're asking. If you need instructions, you'll have to go to the link you posted. It has all the information you need.

If you're brand new and need step by step instructions, let me know.

 

[BuskyLusky]

Thanks I figured it out, simply by typing this code:
----------------------------------------
Dim xlTmp As Excel.Application
Dim xlSht As Excel.Worksheet
----------------------------------------
Private Sub CommandButton5_Click()
Label1.Caption = xlSht.Cells(5, 5)
xlTmp.Workbooks.Close
xlTmp.Quit
End Sub
-----------------------------------------
Public Sub Form_Initialize()
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open "\database.xls"
Set xlSht = xlTmp.Sheets(1)
End Sub
-------------------------------------------

This code will read the cell E5 from database.xls. All you have to remeber is to click off MS Execl 11.0 as references under project

 

[LARiot]

Yeah, I guess if you don't need a recordset this is a much better way.