Access vs. Excel

[Aelanna]

Here's a thought that just occured to me.

How come in a VB program, if you want to place information into an Access database, all you need to do is directly link and write to the mdb file without calling Access, but to do the same thing in Excel you have to actually call up Excel and open the sheet rather than just pass the info directly to the xls file?

[RobDog888]

Thats because for Access you can read/write to it using Microsoft ActiveX Data Objects 2.x Library. These are like the drivers for communicating with Access without using the Access reference.

Now for Excel you can actually do the same. You can use ADO to connect to it but you dont get all the functions, methods, and property access like you do when using the EOM.

[Aelanna]

Hmm... would be nice if they made a library like that for Excel. I wonder why they didn't?

[RobDog888]

They did. You just need the connectionstring for connecting to an Excel Sheet and its the same as ADO and Access DBs. The only limitation is that either with Access or Excel you can not gain access to things like DoCmd for Access and Workbooks or Sheets collections for Excel, etc. So if your only wanting to do basic read/write to Excel the ADO can be used but if you want to gain access to a celss formula, for ex, you have to use the EOM.

[Aelanna]

Ahhh, I see. Seems to me accessing a spreadsheet without being able to access its formulas isn't very useful.

[RobDog888]

I have a few threads on accessing Excel using ADO. Try a search by my username and Excel+ADO for some code examples.

[dannymking]

You can easily access Excel's Formulas by using the WorksheetFunction inside the declaration of excel

[RobDog888]

Not if your using ADO to connect to the Excel sheet. That is the point of the thread. If you connect to Excel using ADO vs. connecting to the sheet using the EOM then you loose alot of Excel functions, methods, and properties.