A Plea for Custom Document Property Manipulation in Excel

In my experience, the capabilities of Microsoft Excel have far outweighed that of Microsoft Word.  It may possibly be my line of work, but I’ve thus far always found that I can accomplish more with Excel’s formulas, references, and especially macros than I could ever hope to get out of a Word document.  Word was great for typing fancy-looking text, but when I required any amount of functionality Excel was always there to save.  Until today…


The Request
Microsoft: please enhance Excel such that I can

1. Retrieve and manipulate Custom Document Properties using only Formulas (no macros required) in the same way that can be found using Word’s Insert > QuickParts > Field functionality.
2. Insert these same custom document properties into the header/footer section of a worksheet.

Microsoft Word
For example, in Word, I can choose Insert > Quick Parts > Field > DocProperty.  Then choose any document property maintained and insert a reference to it.



Limitation of Microsoft Excel
Unfortunately, in Excel a macro must be written in order to retrieve values from the CustomDocumentProperties collection.  A formula can then reference this macro.  In a corporate network with any sort of security policy, this means that users must “enable the macros” for this to function properly.

Function GetProperty(PropertyName As String, PropertySet As PropertyLocation, _
    Optional WhatWorkbook As Workbook) As Variant

(Reference Chip Pearson’s website for GetProperty code.)

Further, I’m unable to insert any auto-updated values into the footer of an Excel worksheet other than the handful of values pre-defined by Microsoft: page number, date/time, sheet name, etc.  In order for this to occur, I need yet another macro to execute Before_Print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With Worksheets("Sheet1").PageSetup
        .CenterFooter = GetProperty(“Issue.Header.Id”, 2)
    End With
End Sub

Code found by doing a Google search for excel formula in footer.

I’d like to see efforts made to improve the functionality of these items in the next release of Microsoft Excel.

Enjoy!

VS 2010 Cannot Write Updates to Schema Model Version

So… I started today creating the same sort of SQL Server Database project that I’ve done many times before.  Really simple database – just one table so far.  It’s just going to be a log for a new interface between our ERP system and the new time/activity tracking system being installed.

The first indication that something was wrong is during the Data > Schema Compare.  After selecting Project vs. Database, I noticed the [Write Updates] button is disabled.  And a friendly looking message informing me that I can’t do what I planned to do next.

“You cannot write updates to the target when you compare the specified types of schema models.”

To date, the Google results haven’t been much help for this error message.  A few hits on queries, but only one solution – from someone accidentally using the Data Tier Application Component project instead of SQL Server 2008 Database project.  DAC projects cannot write updates.

I spent a little more time pushing and prodding VS 2010 and I finally got this error message out of it as well:

“SQL01275: The version of the source and the target schema do not match.  You can compare these schemas, but you cannot write updates to the target schema.”

Okay – fine.  Searches for “SQL01275”, “version of schema do not match”, etc. were turning up nil.

I tried comparing Database vs. Database – no problem: [Write Updates] was enabled.  Proof: the functionality can still work.

I tried comparing Database file (.dbschema) vs. Database – again the error.  Taking this as a clue, I went to examine the .dbschema file.

<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="1.1"
  DspName="Microsoft.Data.Schema.Sql.Sql100DatabaseSchemaProvider"
  CollationLcid="1033" CollationCaseSensitive="False">

Notice the 2nd line has another tip.  The schema version of my VS 2010 Database Project file is version 1.1.  Got caught in another red herring.  And finally posted this thread at MSDN Forums.

Jeff

---

UPDATE

Unbeknownst to me, I was importing a SQL Server 2005 database into a SQL Server 2008 Database Project.

Solution: In the Project > Properties, change the Project version to SQL Server 2005 and [Save].

Then unload and reload the database project.  Opening the Schema Comparison again, produces the expected result.

Developing Mobile Applications for SAP

I skipped breakfast this morning at SAP TechEd 2010 to get to this hands-on lab (CD162) 40 minutes early. The hope was to get a no-show spot left by one of the approximately 50 registered attendees. One other person still beat me to the door. Right now, I'm second on the waiting list.

---

I made it!  Good lab too; it was worth the wait.  The important take-aways of this course were (1) learning how to use a set of SDOE_* transactions that are used to design the data structures and rules that are exported and consumed by the mobile application development environment; and (2) understanding how to consume the the published XML data structures in an application developed in Visual Studio C#.


Creating Distribution Rules
Transaction SDOE_WB is available in SAP Netweaver Mobile 7.1.  It is the Workbench used to create your distribution rules for mobile devices.  Here you indicate the data structures and configure rules used to determine what instances of those data structures are passed on to the mobile devices.


Developing on the SAP/Sybase Co-Innovation Stack 
After designing the data model to be consumed by the mobile application, you will need to export the model as an ESDMA bundle.  This is used by the Sybase Unwired Platform (SUP) to generate code in C# or Java (possibly others).  The generated code handles passing data and messages between the mobile device local database and the SAP backend database.  This allows you, the developer, to focus on the mobile application's business logic and design.

Looking forward to my first project.

UPDATE: I've since learned that there is many different ways (released and unreleased) to do mobile development.  Most of which are complicated at best.  The post above is just one small piece of one way to get a mobile application up and running.

SAP TechEd 2010 Keynote

Vishal Sikka presented the keynote session at SAP TechEd 2010 in Las Vegas. The important take-aways for me were:

SAP Netweaver 7.3 Release
Information about the upcoming release of SAP Netweaver 7.3.

Coalescing mobile application strategy
A presentation was given on a 2-minute development project published to an iPad using the Gateway Consumption Toolset.  Looking forward to attending additional sessions this week regarding mobile development.

SAP In-Memory applications with HANA

I had a conversation with an SAP Labs technician to learn more about this.  This is a device (think Google Search Appliance) that contains the hardware and software in one appliance.  It integrates well into existing system landscapes in that it can sit next to an SAP R/3 and/or SAP BW system and replicate a predefined set of data from either or both systems to be presented to end users in an in-memory, high-performance method.

InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file

I've been working on a problem for the past half a day with the MS Office 2007 PIA.  The issue is with MS Office automation on Windows Server 2008 (x64).

This statement fails:

                wb = (Excel._Workbook)(books.Open(_sourceFilename, 0, true, missing, missing,
                                                 missing, true, missing, missing, missing, missing,
                                                 missing, missing, missing, missing));

with error:

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file ''. There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

   at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)

The solution is an odd one: create the directory "C:\Windows\SysWOW64\config\systemprofile\Desktop".

That's it.  Works like a charm.

Windows SharePoint Search Access Denied

This is a re-post in order to make the solution to this error more transparent.  The problem was that our SharePoint Search service was not returning any results. I found the following event occuring in the Event Viewer.

================
Event details:
Event Source: Windows SharePoint Services 3 Search
Event Category: Gatherer 
Event ID: 2436
Description:
The start address cannot be crawled.
Context: Application 'Search index file on the search server', Catalog 'Search'
Details:
Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has "Full Read" permissions on the SharePoint Web Application being crawled.   (0x80041205)
================

This stumped me for a long while until I finally took the time to dig in and do the proper research.  I found another error showing in the SharePoint log file:

================
CSTS3Accessor::GetDocFormat: Return error to caller, hr=8007007A - File:d:\office\source\search\search\gather\protocols\sts3\sts3acc.cxx Line:654
 ================

A simple Google search turned up this post by Yorick with the solution.

Thanks Yorick.

IE8 Navigate in "Protected Mode"

If you spawn an IE session in your development read on…

I have an application that is posting data to a webpage from a client app using the IE.Navigate API.  The problem was that in IE8, the server never received the request.  Instead (when Visible was set to True), the window just opened and then disappeared.  I finally discovered the reason.

In the “Internet” zone, the “Enable Protected Mode” setting was checked.  An admirable security feature, it can be awfully annoying to the uninformed (as security features typically go).  Now, note that my URL is on our Local Intranet.  Regardless, by unchecking this option, the application immediately began functioning properly again.

Good luck with your IE8 testing.