working with Excel 97 in vb

[Thai]

Hello, I am trying to write a program that can read the data in excel and make sense of the data. The files are formatted so it looks like an access table (ie. column header/records). What is the best way to approach accessing the excel file to read the column headers/data? Do you connect to it like you do with a db? Any input/suggestions/links to examples would be greatly appreciated. Thank you!

Thai

[Gen-X]

You basically use "OLE" (Object Linking Environment).

What this does is allow you to act as if the Excel document is actually inside Excel and you are using the VBA as if you were really there.

You can do it one of 2 ways but I would recommend starting by referencing Excel in your VB code and using it directly.

If you go to Tools/References and then find the "Excel Library" and tick it.

Now you can do code like this :

Dim oExcel as Excel.Application

oExcel.Open "name of file"

MsgBox oExcel.ActiveWorksheet.Cells(1,2).Value

oExcel.Quit False ' False means don't save changes

Set oExcel = nothing

It works just like you were writing a VBA macro in excel itself and funny enough actually opens the excel application in the background.

Do some research and use your help guides to talk you through this. Do searches for things like "CreateObject" and "GetObject" if it will help

[Mongo]

Another route would be to use the Excel spreadsheet as if it were a database using ADO or RDO. Here's a quick dirty ADO example:

Sub OpenExcelDatabase(strDBPath As String)
Dim cnnDB As ADODB.Connection
  Set cnnDB = New ADODB.Connection
  ' Specify Excel 8.0 by using Extended Properties
  ' property, and then open Excel file specified by strDBPath.
  With cnnDB
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties") = "Excel 8.0"
    .Open strDBPath
    Debug.Print .ConnectionString
    .Close
  End With
  Set cnnDB = Nothing
End Sub