Office user group

A load of us went to Microsoft Reading for the Office user group event on Fri (27 Apr).

It was an excellent event, lots of interesting presentations, and the opportunity to meet fellow devs and discuss the real issues we face.

Its great to hear the latest and greatest for Microsoft, but its also good to get a dose of reality. VSTO certainly does look good for a certain set of applications, but there are plenty of us with very limited opportunity to deliver solutions to customers based on it. Personally I feel VBA devs are in limbo, have been for several years and will continue to be for the foreseeable future. I’d classify that as an opportunity for someone.

The session were:

The power of Office, which went a good way towards explaining what Office is these days (hint: its not just Word and Excel (and Access) anymore!)

Excel and BI, showed some of Excel 2007’s BI features in conjunction with Analysis services. Some neat stuff in there, maybe I should port some of those AS worksheet functions back to ’97-2003 versions.

Excel and the Ribbon, Nick Hodge nearly persuaded me the ribbon wasn’t totally rubbish. Nearly. I am starting to think that the ribbon may end up bearable in a few versions, unless its replaced of course. There was plenty of useful advice for anyone working with the ribbon.

Visio 2007, Very impressed at what can be done by connecting Visio to a database, I’ve only really used it for software development (DDL, UML etc). There look to be some great data visualisation features. One point I found extremely interesting was the fact that Visio isgetting an MCP exam. Excels was retired in 1997, Access was retired in 2001(ish?), the point being that there has been no Office focused exams for years, and now Visio is getting one. Does that mean other Office products will get them back, or are we stuck with MOUS?

Groove, now I finally know what this is, I think. Its a cross between Lotus teamrooms, and the briefcase (if anyone remembers/ever used that). Efficient data replication that allows people to work offline, with simple replication/synchronisation as soon as they go back on line. The presenter also suggested it might be useful for disaster recovery. It will probably be 5 years or more before my clients get it, but I might be able to make use of it myself, in this way.

Office integration, took a variety of raw data, through a range of processes and products to produce a bunch of reports. A great example of using the best features from each product to get the job done, quick and easy.

Underused products, a look at one note and info path. These look great in the demos, but I can see why they are ‘underused’. For me even having seen them in action, they are less than compelling. Although OneNote does character recognition on pictures which was pretty neat. If I ever get a tablet pc, I may well go for this. I’m sure some people could make great use of them, but they are probably a bit far from the very limited area I operate in.

There was also a questions and answers session where we had a livelydebate about the future of VBA/VSTO/VSTA. Of course no-one had any hard facts, but we still had a good debate.

Overall the event was excellent, good content, good atmosphere, good organisation and we were well looked after by Microsoft. If you get chance to go to another I would recommend it.

The highlight for me was meeting some of the people I have been chatting with on-line and putting faces to the names. And we had a good chance to talk through a few things after the event which was very good too.

I’ve forgotten most of the quotes I was trying to remember sadly. Except:

One presenter mentioned that he liked the ribbon because the buttons were easy to hit. Now if he were a Yorkshire lad I’d have taken that as ‘them gurt buttons are just for the cack handed’ (big buttons for the clumsy), but he wasn’t so I’m not sure. Anyway, someone else said, well fat people are easy to hit because they are big and slow moving, thats not really an advantage though is it?

A few of us are thinking to get together in a couple of months to discuss some developer stuff, share war stories etc. I’m thinking Oxford on a Friday maybe, end of June/ early July? I’ll post again in a few weeks with some suggestions for an agenda and location. If you fancy it leave a comment.

Cheers

Simon 

Advertisements

24 Responses to “Office user group”

  1. Harlan Grove Says:

    OK, I’ll bite: what’s InfoPath supposed to be good for? What key features does it provide that Excel and Access lack? To be honest, it strikes me as a tool for Word/PPT users too timid to try using Excel or Access.

  2. Simon Says:

    Harlan – I’m not the person to answer that I’m afraid, I got the feeling it was for people who wanted to do something simple, but had loads of free time to fill. All the examples I have seen would be solved better by sending an email to someone, or picking the phone up.

    I’m sure someone somewhere has found a use for it – please post if its you.
    cheers
    Simon

  3. sgbhide Says:

    I used it once……..spent half an hour on it….and found that it does nothing that as Harlan says cant be done with ADO in Excel/Access.

    Havent opened it since….

    Like wise what good is Office Publisher……

  4. Stephane Rodriguez Says:

    I’m a little torn between three aspects wrt to VBA.

    – In a Sarbanes-Oxley world, I’m not sure if there is still a place for VBA in the long run.

    – VBA macros are radicalized in Office 2007 by requiring the use of files ending with ‘M’, and with a higher default security when run on Windows Vista (to my knowledge, I could not verify this though)

    – On the other hand, Microsoft approach to the new ZIP-based file formats pretty much guarantees that the bulk of Office developers will keep using VBA for a long time to come. The reason why is because, internally, the file formats are a simple XMLification of the binary file formats. This has a couple intended/unintended consequences. One of the consequences is that the guts are directly exposed, meaning there is no “humanification” layer between how Excel internally stores its content and what you get access to. For instance, if you’d like to manipulate a date, you’ll have to fix the famous 19xx bug yourself, and by the way you’ll get a number and it’s up to you to translate it in a human readable format. If you intend to create a vector shape (chart, pic, …), then you’ll have to deal with the underlying low-level MSO concepts (4 layers of abstraction, a number of imcompatible and undocumented coordinate units). And of course, if you directly access this stuff, the Excel run-time is not running for you, meaning that anything you change gets out of sync with just about anything else in the spreadsheet, increasing the corruption rate by 500% (I am conservative).

  5. Simon Says:

    Stephane – I’ve read some of your stuff about the new file formats, and I know in biff reading is ok, writing is scary.
    I heard there was some stuff in the .net framework to enable working directly with the xlm files, have you tried it? any views? Its System.IO.Packaging I think.
    I’ve not messed much but I understand there are all manner of non obvious interdependencies such that you wouldn’t want to wade in and have a bash manually. I take it from your 500% comment that its not really much more accessible than biff?
    Cheers
    Simon

  6. Harlan Grove Says:

    In a SARBOX world what place is there for any scripting language? Or any piece of unverified code? However, perfect SARBOX compliance is so impractical that any company attempting it is almost certain to eaten alive by competitors willing to chance uncertain lawsuits. Sensible managers will aim for ‘safe enough’ and ‘good enough’, and those are subjective.

    Second, MSFT would have to be radically stupider than than they’ve ever been before to allow the Excel process to operate at higher OS security level if it had an .XLSM file open rather than an .XLSX file. Have any security experts put this in writing, or is this idle speculation?

    As for the third point, XML format is SUPPOSED to be nothing more than a relatively plain text version of the binary format. The ‘humanification’ layer has ALREADY BEEN DONE – there are Perl modules that read (and write!) .XLS files, and others that read .XML files. Put ’em together. There’s also the open source code bases for OpenOffice, Gnumeric, KSpread, Oleo, etc., that provide WORKING code (albeit not in any BASIC dialect) to handle nearly everything that can be found in .XLS files.

    Undue concern about the hardcoded, binary-ish nature of .XLS[XM] file formats reflects a certain ignorance of CURRENTLY AVAILABLE tools that aren’t VBA/VSTO/VSTA. Besides, it’s only data access that XML file formats are supposed to provide, i.e., the ability for other programs to be able to extract data from any XML file, NOT the ability to reproduce the full functionality of the application that produced the XML file. Experienced programmers should know better than to expect to be able to manipulate, say, drawn objects in worksheets in .XLS[XM] files WITHOUT having libraries that provide that functionality or fully understanding that they’re embarking on reinventing rather complicated wheels.

  7. Stephane Rodriguez Says:

    Simon,

    Even reading BIFF is problematic. If you read BIFF then you are deliberately saying “I don’t care VBA events that are run when I open the spreadsheet ; I don’t care the addins that get loaded and might impact the spreadsheet I open ; …)

    BIFF is stored in a storage/stream OLE document, whose interfaces are provided by WIN32. Since WIN32 does not provide direct support for ZIP files, and .NET System.GZipStream is very limited (it does not support directories), I guess Microsoft had to provide something to fill the gap they are creating with XPS (PDF competitor) and the new Office 2007 file formats. But that’s a generic exposure of ZIP, it does not make it easy to create/read/modify Excel .xlsx/m/a files.
    A bit of strategy : System.IO.Packaging is part of .NET 3.0 which ships with XP SP2, 2003 SP2 and Vista. Not sure how many third parties out there can afford this in deployment scenarios. Technically speaking, upon analyzing the internal structure of those ZIP files, it’s interesting to note that content types are declared twice (which provides room for document corruption), and relations to ZIP parts between themselves are also declared twice : once in separate .rels ZIP entries, and in streams themselves. Even more room for document corruption. By the way, if you head over at openxmldeveloper.org/forums, it’s easy to get the feel that the new breed of Office developers embracing this stuff are pretty much stuck in the starting blocks.
    Bottom line : strategy. Microsoft “opened up” because of tighten governement regulations (and the NIH syndrome with OpenOffice who came up with ZIP+XML before them).

  8. Stephane Rodriguez Says:

    By “System.IO.Packaging is part of .NET 3.0 which ships with XP SP2, 2003 SP2 and Vista.” I meant it can be deployed on XP SP2 and 2003 SP2, and it ships with Vista. Any .NET deployment is problematic, and Vista is nowhere to be seen for at least a year.

  9. Stephane Rodriguez Says:

    Harlan,

    “As for the third point, XML format is SUPPOSED to be nothing more than a relatively plain text version of the binary format. The ‘humanification’ layer has ALREADY BEEN DONE – there are Perl modules that read (and write!) .XLS files”

    Can you name one that does it correctly?

    If we take SpreadsheetGear (disclaimer : it’s one of my competitors), it claims to be “the most compatible Excel spreadsheet” third-party. Yet, it does not support VBA macros and events. Meaning that, as I alluded to in my comment above, the minute you open a spreadsheet using this tool, you are not doing the same thing than Excel. In a number of cases, this pretty much means you are not going very far (especially if you are a VBA freak). I focus on VBA because that’s always very hot in this blog, but I could give more examples if needed.

    “Undue concern about the hardcoded, binary-ish nature of .XLS[XM] file formats reflects a certain ignorance of CURRENTLY AVAILABLE tools that aren’t VBA/VSTO/VSTA.”

    Sorry, I can’t parse this sentence. Care to explain?

    “Besides, it’s only data access that XML file formats are supposed to provide”

    Nope. That’s not Microsoft is positioning this stuff in public. The lack of developer story for Office 2007 file formats (aside System.IO.Packaging which is a ZIP library) indeed speaks volume that Microsoft never intended to provide access to Office developers in the same way than they make it easy and worth to create VBA macros. Yet, just head over to all public Office related MS blogs, you’ll see a lot of cognitive dissonance.

    “the ability for other programs to be able to extract data from any XML file,”

    Not quite true either. The data, most often, is not left for direct consumption. And that’s where there is this big gap with what Office developers get if they use VBA and a running instance of Word/Excel/Powerpoint. For instance, strings are stored as shared strings by default. Shared strings are rich strings by default. Any cell might have a number format (proprietary algorithm) applied to it. And VBA events might be attached to an action like selecting or reading the content of that cell.

    “WITHOUT having libraries that provide that functionality”

    That was my original point. The disagreement I have with you is that you are assuming you can find a library (may be ton of them, actually) that replaces the VBA world for that matter. Can you name one? I personally can’t. For the same reason, you will never be able to find an Office 2007 alternative that fully supports this stuff. N-E-V-E-R.

  10. Simon Says:

    Stephane – personally I don’t think MS will ever do anything that helps with VBA. It was always undocumented in biff, its just a blob in the new formats, I think they are just waiting for the whole VBA thing to whither and die. My concern is that it will take the whole of MS Office with it.

  11. Stephane Rodriguez Says:

    “personally I don’t think MS will ever do anything that helps with VBA.”

    Their non-action is exactly what keeps VBA alive and for a loooooooooong time to come.

    Have you noticed by the way that while VBA is showing its age, nothing can be offered as a seamless alternative?

    “My concern is that it will take the whole of MS Office with it.”

    Yes. And they can’t afford this. So we are back to square one.

  12. Dennis Wallentin Says:

    Simon,

    >>I heard there was some stuff in the .net framework to enable working
    >>directly with the xlm files, have you tried it? any views? Its
    >>System.IO.Packaging I think.

    Check out the ExcelPackage:
    http://www.codeplex.com:80/ExcelPackage

    I got it on my “To do list” for a coming blogpost.

    Kind regards,
    Dennis

  13. Harlan Grove Says:

    >“As for the third point, XML format is SUPPOSED to be nothing more than
    >a relatively plain text version of the binary format. The ‘humanification’
    >layer has ALREADY BEEN DONE – there are Perl modules that read (and
    >write!) .XLS files”
    >
    >Can you name one that does it correctly? . . .

    Search through the hits from

    http://search.cpan.org/search?query=Excel&mode=all

    I’ve only ever used Herbert myself, and that was years ago. Was it perfect? No. Was it good enough not to have to use Excel? Most of the time, yes.

    As for the rather simple example of dates, all POSIX-compliant programming languages provide support for Unix systime calls, and the 1900 and 1904 date system date/time values 25569.0 and 24107.0 (net of time zone adjustment), respectively, correspond to Unix systime 0 (1 Jan 1970 00:00 UTC). If you have any Excel date/time value DT (in 1900 date system),

    INT((DT-25569)*86400)

    is the Unix systime. All POSIX-compliant programming languages also provide internationalized date/time text formatting. If this is an example of the humanification with which you’re concerned, I have to wonder about just how much (little) you know outside the VB domain.

    >If we take SpreadsheetGear (disclaimer : it’s one of my competitors), it
    >claims to be “the most compatible Excel spreadsheet” third-party. Yet, it
    >does not support VBA macros and events. . . .

    If that’s what you consider essential, fine. VBA code isn’t what I consider spreadsheet DATA. I don’t even consider spreadsheet formulas to be spreadsheet data. My goal extracting contents from Excel .XLS{X|M|} files is getting as much as but not any more than I’d be able to get using external references into closed workbooks from within Excel. If you want something more, that’s your goal (and your problem).

    >Meaning that, as I alluded to in my comment above, the minute you open
    >a spreadsheet using this tool, you are not doing the same thing than
    >Excel. . . .

    Yes, indeed, EXACTLY WHAT **I** WANT. If you want something different, that’s your affair (and, to repeat, your problem).

    Dunno about you, but if I want the programming as well as the data, I’ll just open the file in Excel. If I want the programming in terms of the cell formulas but can live without the VBA stuff, I can open in Gnumeric or OpenOffice Calc. [Full disclosure: when there are no external references; none of the Excel alternatives I’ve used have ever handled external references into close Excel workbooks at all well.]

    I’m not claiming there are alternative programs that will allow users to run macro-intensive Excel workbooks the same as Excel would run them. That was certainly not the point of my first response to you. You were bitching & whining about the XML file formats not being all that different than the binary file formats AND the difficulty of implementing Excel semantics for workbook contents. It was unclear you meant macros, events and udfs. If that’s what you meant, you have a point, but it seems to me you’re missing the point of what the XML file formats were meant to accomplish.

    >“Undue concern about the hardcoded, binary-ish nature of .XLS[XM] file
    >formats reflects a certain ignorance of CURRENTLY AVAILABLE tools that
    >aren’t VBA/VSTO/VSTA.”
    >
    >Sorry, I can’t parse this sentence. Care to explain?

    Nope. I’ll leave it as an imponderable.

    >“Besides, it’s only data access that XML file formats are supposed to
    >provide”
    >
    >Nope. That’s not Microsoft is positioning this stuff in public. . . .

    Then Microsoft needs to provide the libraries that provide the needed functionality. XML according to the W3 Consortium is a means of representing data and metadata as plain text. If the metadata is supposed to represent programming code, that’s ENTIRELY up to the organization that promulgated the particular schema to support.

    >The lack of developer story for Office 2007 file formats (aside
    >System.IO.Packaging which is a ZIP library) indeed speaks volume that
    >Microsoft never intended to provide access to Office developers in the
    >same way than they make it easy and worth to create VBA macros. . . .

    Indeed. They’re providing simpler access to the data as I’d define data. Looks like I’m having a much easier time with the new formats because my expectations for what Microsoft is likely to provide (and should have provided) are more limited than yours. It’d seem I’m also much more predisposed to discount or ignore Microsoft marketing hype than you. Life becomes easier when you cease to take Microsoft utterances at face value.

    >Yet, just head over to all public Office related MS blogs, you’ll see a lot of
    >cognitive dissonance.
    >
    >“the ability for other programs to be able to extract data from any XML
    >file,”
    >
    >Not quite true either. The data, most often, is not left for direct >consumption. . . .

    Examples?

    It *IS* possible to pull the values of all cells in a workbook, and put them in essentially the same human readable layout in other file formats, even tab-separated plain text.

    You seem to mean the metadata of formula relationships and VBA. You just mean something different for the term ‘data’ than most other people familiar with XML files do.

    > . . . strings are stored as shared strings by default. Shared strings are
    >rich strings by default. . . .

    So?

    By ‘rich strings’ do you mean they include formatting? The .Characters property of cells does, but the .Value and .Value2 properties don’t. You seem to want the .Characters property. I’m satisfied with the .Value2 property, and I strongly suspect simpler, more efficient access to the .Value2 property is all that Microsoft intended to provide and all that anyone with any experience working with Microsoft products expected to receive. If you want the .Characters property as rich text, you could iterate through it one character at a time, generating the appropriate HTML or RTF formatting for each character. Ugly, brute force and maybe slow, but hardly impossible.

    >Any cell might have a number format (proprietary algorithm) applied to
    >it. . . .

    Proprietary? Maybe the newer bits for spacing in Accounting or Currency format groups, but the basic formats are obviously derived from BASIC PRINT USING number formats that were developed at Dartmouth College in the late 1960s and early 1970s, so while IANAL I believe Microsoft would have a very difficult time trying to exercise IP rights to the semantics for Excel number formats. They’re not all that difficult to parse, and there are more than a few open source COBOL/PL/1 PIC format interpreters for other languages on the Internet. This is basic (note the case) stuff.

    >And VBA events might be attached to an action like selecting or reading
    >the content of that cell.

    Fine. Cell contents aren’t the ‘data’ you’re after. That’s your problem.

    >“WITHOUT having libraries that provide that functionality”
    >
    >That was my original point. The disagreement I have with you is that you
    >are assuming you can find a library (may be ton of them, actually) that
    >replaces the VBA world for that matter. . . .

    Wrong. I never said anything about the VBA world (other than implying that its horizons are extremely limited and parochial). If the goal is extracting data minimally defined (cells’ .Value2 property with events and macros disabled), there ARE tons of currently extant libraries and scripting language modules that DO handle all the aspects of semantic interpretation of anything that could be found in cells’ .Value2 properties.

    >Can you name one? I personally can’t. For the same reason, you will
    >never be able to find an Office 2007 alternative that fully supports this
    >stuff. N-E-V-E-R.

    You’re trying to attribute your own meaning for terms and your own expectations for the functionality XML provides to me. I agree that it’s unlikely Microsoft intends for any other company, organization or individual to be able to provide as much functionality upon opening Excel workbook files as Excel itself provides.

    Putting it differently, Microsoft isn’t about to provide either a free Excel runtime (similar to Access’s) or the tools to create one. Unlike you, I would never have expected them to do so.

  14. Stephane Rodriguez Says:

    You like taking wild tangents, don’t you?

    The question was related to a VBA alternative. Microsoft Office 2007 file formats are not the answer, nor should we compare apples and oranges.

  15. Simon Says:

    Good to see such diversity in our needs and expecations.
    I had rather hoped the new file formats would enable decent diffing including formulas, sounds like thats a no no.
    On the VBA front, the only (possibly) viable alternative to Excel seems to be OOo Calc at this stage.

  16. Ross Says:

    I really really want to know how VSTA is going to fit with Excel and VBA? Maybe they well allow VBA to run in the/a new VSTA IDE? (Maybe not!!!)

  17. Harlan Grove Says:

    OK, I’ll be precisely on-topic.

    VBA is unnecessary for reading or interpreting the .Value2 properties of cells in Excel’s new XML file formats. Even complete Excel cell formatting has already been implemented in Perl modules. Shame for you that you have to reinvent these wheels since you want to maintain a closed source business model, but that’s your choice.

    XML file formats have nothing to do with VBA. VBA would be no easier to use to fetch data from CLOSED .XLS[XM] files than languages with built-in or widely available add-on XML parsers. The benefits of VBA for Excel apply only to OPEN Excel workbooks which have already had their contents translated into in-memory binary formats. No other language provides those benefits, but those benefits are available EQUALLY to old format .XL? and new format .XL?[XMB] files.

    Disk file formats and VBA’s usefulness are independent concepts.

  18. Simon Says:

    Ross
    I don’t think anyone knows, and if they do they are not saying.
    Where do you think the code should go? in the .xls? as per VBA or in a separate binary (assembly) a la VSTO/.net/com add-in?
    If O14 is going to be released in 2009, then public betas may start early in 2008.
    OOo Calc has multiple IDEs depending on your choice of language, I’m sure VSTA will be a separate IDE too. If they can’t have VB6 in VS .net after 6 years I don’t see how we’ll get VBA in a VSTA IDE. Apart from anything it would cause a riot for all those classic VB fans forlornly hoping VS will have a VB.com one day.

  19. Simon Says:

    Harlan/Stephane – I think I understand what you are both saying, and I agree with both of you. I just think we may all be looking at this in different ways depending on our needs.

    Harlan some of us would like access to more than the .value2, such as .formula, .HasArray etc, possibly without firing up Excel. There was a hope that the new format would give us full fidelity access to the spreadsheet, including values, formulas, formatting etc. I wanted this for compliance reasons, I’m sure there are plenty of others. From what you say .value2 is pretty much solved, but I think Stephane is saying that all that other stuff is still not easily accessible. I assume you mean formulas are still tokenised etc Stephane?

    If this were all accessible then we could use other tools to read the file directly instead of automating Excel, which is often but not exclusively VBA.

    I think this is what Stephane means when he says we’ll still be using VBA?

    I take your point that we may have been unrealistically optimistic. From a compliance POV if you open the file in Excel all bets are off as you may have triggered a data changing event. Thats ‘data’ is the broadest sense, forensic style.

    Stephane is that what you meant or are you looking for something to execute the VBA too? Excel services doesn’t even run VBA, and I don’t think it ever will.

    please let me know if I got it wrong, and Harlan I promise I will try and be even more sceptical of MS markting hype. (I wouldn’t have thought that possible, but it looks like I got caught out here).

    I was sort of expecting what you get when you save a .xls as xlm in 2003, which gives easy access to the stuff I want to get at.

    Cheers
    Simon

  20. Methods In Excel » Says:

    […] spare time on his hand than me (or maybe he’s just not as lazy and bad at writing!) Simon has written up an excellent review of the UK OUG in […]

  21. Harlan Grove Says:

    Excel 2003’s XML spreadsheet schema saves cell formulas as text in R1C1 referencing syntax. FWIW, OpenOffice ODF .ODS format saves formulas as text. If Excel 2007’s OOXML .XLS[XM] format saves tokenized formulas, that’d be a huge step BACKWARDS. And, FWIW, cell formatting in Excel 2003 XML and OOo .ODS formats is plain text, so same assessment if it’s not plain text in OOXML .XLS[XM] formats.

  22. Stephane Rodriguez Says:

    Simon said “Excel services doesn’t even run VBA, and I don’t think it ever will.”

    That’s true in Office 2007. That’s a question for Microsoft to answer going forward.

    There are four reasons IMHO for Microsoft not supporting VBA on the server.

    – it’s too powerful, and customers would start moving their stuff on the server and stop buying desktop licenses
    – VBA is a single-thread apartment, while Excel services is multi-threaded, re-entrant and all that. Kind of problematic…
    – the Office team can’t bother fix the problems with running VBA on the server (like message boxes being raised). Note that it’s Summit Software who is responsible for VBA/VBE.
    – Microsoft think custom code should be run (in .NET) outside the spreadsheet.

  23. Ross Says:

    Simon, I looked into VSTA a bit more last night, I’ll post on in soon.
    Cheers
    Ross

  24. MikeC Says:

    I have to say, Simon’s review of the day is much more professional than mine is. No change to the normal way of things there, then…!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: