VBA, Programmability, and Open File Format for Office 2010 with Brian Jones

Now that Office is available in a x64 flavor, I seem to have compatibility issues with managed add-ons, that I have developed.  There really needs to be a way to target x86 and x64 office skus, in an elegant approach. Or am I just doing something wrong?

I've also noticed some weirdness with VSTO between 2007 and 2010. Was there a strategy shift? 

-Josh

Awesome, I had not idea that was what the OpenXML SDK was all about.  So much simpler than COM automation.  Are there any plans to provide this type of API for the older Compound Document format of Office 97-2003?  We have tons of those files on our servers and it would be awesome if we could also move data in and out without requiring COM automation.

I cannot see that happening.  The best you could hope for would be a document compiler from OOXML to Office Binary.  Or perhaps you could hobble together an OOXML to ODF to Office Binary. There seems to be a lot of tools for going to and from ODF. There are a lot of strange quarks with binary files. However, there are stranger issues with OOXML, like the lack of native embedded macros. I don't think sticking a big binary blob of data into an OOXML document was a good way to handle the macro problem.

Are you currently generating doc, xls, or ppt files?

Why would you want to involve ODF when converting docs between ooxml and binary?

Just create one application that converts all your old binary .docs to the new format. You could let it run all night.

Not quite that scenario.  I've been building a DTS engine of sorts for our group which can read multiple versions of workbooks which contain scientific data.  I would like to integrate with SharePoint but it's more than just tabular data.  Some of the data is custom formatted, and even more so, there are multiple versions of some formats as we continue increase the data we collect and store.  I can't use COM Automation on the server, first off, it isn't supported, and second, if something bad happens, I don't want instances of Excel hanging and consuming memory.

From what I recall, the OOXML tools from Microsoft cannot produce or convert binary. What I was trying to get to was the idea that you could create a single application that did the conversion by hobbling together readily available library code, in the case that you didn't want to use com automation to do the job.

Would using the ODBC driver work for sucking the data out of the XLS documents? I haven't used it in a while, but it certainly wouldn't leave any instances of excel hanging around. Since there are so many ways of structuring data in Excel, I could see how extraction and verification could be complicated.

Yes, we did explore the ODBC driver when we first iterated on the prototype.  The problem we found was not extracting data, it was updating, deleting or inserting data.  The Excel ODBC driver does not support the DELETE, UPDATE, or ALTER statements, data is only appended to the Worksheet.  At least that is what I found on this KB article, Q178717.  After reading that, I decided it wasn’t a good idea to have two different solutions for accessing Workbooks.  If the current driver is different, great, I’ll have another look, otherwise I know the COM Interop works, and works well, and we have a solution in place which manages the Excel instance.

Also, the other features of Excel which we use from interop is Printing and Charting.  Once we investigated this, we decided that learning the COM API and PIAs was necessary to create a stable solution.

Again, the data is not structured in a “tabular” format.  The Worksheets are structured so they can be printed, written on, and later digitized once the data is collected.