0

We have an Access program that creates an Excel object, essentially exporting data. We want to run this program in a location that does not have the complete MS Excel application installed, instead just the 2007 Viewer. In addition we do not want tore-write the entire implementation (ADO replacement), currently the app uses the Create.Object("Excel.Application") method to work with Excel.

In other words can you get/obtain a reference in MS Access to Microsoft Excel Object Library without having the full edition to Excel installed?

We referenced some other questions like this one: How to refer to Excel objects in Access VBA?

We also installed the Office Primary Interop Assemblies (PIA) from MSFT as well. We are running MS Access 2003 and hoping to use the Excel 2007 Viewer.

Any ideas?

Brent

Community
  • 1
  • 1
Brent Pabst
  • 1,156
  • 1
  • 15
  • 37
  • What do Office Primary Interop Assemblies (PIA) from MSFT have to do with this? Isn't that some Dot Net thing? Does it have anything to do with VBA? – HansUp Aug 02 '11 at 21:10
  • I strongly doubt that the Excel viewer will provide the entirety of Excel's functionality to you for automation. Seems to me your only real solution is to remove the dependence on full Excel automation. Depending on what you're trying to do, that may or may not be a problem. – David-W-Fenton Aug 03 '11 at 20:58
  • @HansUp I think it is, we tried that just to see if it helped, it didn't. I simply posted the full chain of what we had tried to ensure anyone trying to answer had the full picture. – Brent Pabst Aug 04 '11 at 02:02
  • @David-W-Fenton Yea modding the software is a huge task, its a commercial ERP package and changing all of the references probably will cost us more than buying Excel ;) Thanks for the feedback though, we're trying to cut corners on the licensing. – Brent Pabst Aug 04 '11 at 02:03

1 Answers1

0

I think DoCmd.OutputTo should work without Excel libraries:

DoCmd.OutputTo acOutputTable, "Your object/query name", "Excel97-Excel2003Workbook(*.xls)", "Full path to the destination"

It seems like that should work with only two references:

enter image description here

It may limit your flexibility, though (e.g. custom formatting etc.)

Igor Turman
  • 2,165
  • 1
  • 22
  • 25
  • Igor, I think you're right, Access seems to be able to do it fine when it uses the built-in functions. But as I said in my post we are not trying to modify the code that exists (its a commercial product) we simply want Access to use the Excel libraries, either through some work around or if it comes to it manually copying/registering them. Regardless I'll pass this on and see if it might work. – Brent Pabst Aug 03 '11 at 03:51
  • Igor, We tried your suggestion today but even the OutputTo command would not execute, it fails with similar errors that the required components do not exist. Any idea how we can get the references loaded/referenced without actually installing Excel? – Brent Pabst Aug 04 '11 at 02:00
  • @Brent, I am not sure why _DoCmd.OutputTo_ did not work for you. I tested it myself and it worked perfectly fine. I tested it with Access 2007, though. I created a package (runtime) with my Access 2007 database and installed it on the machine that has neither Access nor Excel installed. It worked perfectly fine. I was able to export data as Excel spreadsheet. But again, this might be not the most elegant solution for you – Igor Turman Aug 04 '11 at 17:27
  • Well we are stuck on Access 2000/2003. We don't have the same version of the Access Library it appears you have, maybe it was a change in 2007? If you have any ideas I'm all ears! – Brent Pabst Aug 12 '11 at 01:57
  • Brent, I did try to execute _DoCmd.OutputTo_ in the environment that has neither Access nor Excel installed (no Office at all, just Access run-time). It worked! It just created Excel spreadsheet that I was able to open from another machine. I am not sure what kind of problem you are dealing with...sorry. I think you should take closer look at PIA. But for the simplest way (_DoCmd.OutputTo_) - it must work for your requirements. The only problem, as I said, would be very limited output formatting – Igor Turman Aug 12 '11 at 06:47
  • Did you try it in a 2003 environment only? You mentioned originally you ran this in 2007. I'm thinking the difference between 07 and 03 is causing some problems here. – Brent Pabst Aug 12 '11 at 13:15
  • Can you please tell me what error you get when execute Docmd.OutputTo command in your environment? It's hard to believe you get (if you do) the same error you would get with CreateObject() without the Excel library – Igor Turman Aug 12 '11 at 15:42
  • @BrentPabst let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2411/discussion-between-igor-turman-and-brent-pabst) – Igor Turman Aug 12 '11 at 15:42