Excel Addin and PDF rendering

Excel Addin and PDF rendering

Hi

One of our applications are an Excel 2002 Function Addin, which is used in 
spreadsheet cells to display various metadata.
The Addin works well in all client installations.

The spreadsheets are contanied in a Document Handling System (Documentum) 
and when ever the user decides, a PDF can be created from the Excel document.
The PDF is rendered on a server with Excel 2002 and Adobe Pro 7 installed.

Now, the cells where our custom Addin is used, are only showing the text 
?Name in the rendered PDF file, not the actual value.
The login which open the spreadsheet and creates the PDF on the server has 
been checked and the Addin is actally loaded and activated in the 
PDF-creating process.

Is there something I have missed here? Why is this not working?

Thanks in advance
-- 
Peter KarlstrÃ¶m
Midrange AB
Sweden

date: Tue, 2 Sep 2008 00:54:05 -0700 author: Peter KarlstrÃ¶m am

RE: Excel Addin and PDF rendering

Hello Peter,

As far as I know, Excel 2002 does not have any built-in function to export 
a workbook to pdf. According to your description, the export is done by 
Document Handling System (Documentum). In order to trouble-shoot this 
unexpected output of the UDF value, I think the first thing to do is to 
narrow down the focus:
1. Is it a product issue of Excel 2002?
2. Is it an issue of our Excel 2002 Function Addin?
3. Is it a product issue of Document Handling System (Documentum)?
4. Is it possible that we pass the wrong parameter from Excel to Documentum?

** To identify whether it's an issue of our Excel 2002 Function Addin **
We can try other functions (either Excel built-in functions like SUM, or 
other UDFs). If the result is correct for any other functions, it's likely 
to be an issue of our Excel 2002 Function Addin. I would need you to post 
some implementation details of the Excel function add-in.

** To identify whether it's a product issue of Document Handling System 
(Documentum) and whether we pass the wrong parameter from Excel to 
Documentum **
A. You may try the whole solution in other computers. If every computers 
work as expected but this one, Documentum is less likely to be the cause of 
the issue.
B. You may consider contacting Documentum support for other suggestions. 

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and 
suggestions about how we can improve the support we provide to you. Please 
feel free to let my manager know what you think of the level of service 
provided. You can send feedback directly to my manager at: 
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to 
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues 
where an initial response from the community or a Microsoft Support 
Engineer within 1 business day is acceptable. Please note that each follow 
up response may take approximately 2 business days as the support 
professional working with you may need further investigation to reach the 
most efficient resolution. The offering is not appropriate for situations 
that require urgent, real-time or phone-based interactions or complex 
project analysis and dump analysis issues. Issues of this nature are best 
handled working with a dedicated Microsoft Support Engineer by contacting 
Microsoft Customer Support Services (CSS) at 
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

date: Tue, 02 Sep 2008 09:33:57 GMT author: (Jialiang Ge [MSFT])

RE: Excel Addin and PDF rendering

Hi Jialiang Ge

Thank you for your reply.
I will try to answer your questions and be a little more specific.

1. This is not a product issue of Excel 2002. When I am logged on with the 
account that creates the PDF files, I can open the spreadsheet and the UDF is 
working as it should.
2.  Same as nbr 1 above, UDF works when I open the spreadsheet.
3. Documentum is not involved in creating the PDF, other than creating it 
using Adobe Acrobat Professional 7. 

Also, there is 2 DLL's involved. First, a COM-Addin which always loads, and 
second, the UDF. The COM-addin checks the UDF Addin in AddinInstance_ 
OnStartupComplete. In Wokbook_Open some other controls are made.

I have made some major debugging in the COM Addin of the Excel-process 
during the PDF creation and the following is confirmed:
1. The Addin is installed
2. The Excel WorkbookOpen event is triggered
3. I can retrieve the username that is running (for checking of HKCU 
registry values)
4. I check to se if the correct spreadsheet is opened
5. I do an update of the pageheader based on values in 
CustomDocumentProperties
6. I do a CalculateFull on the Excel application object
7. I do a Save of the spreadsheet

Everything seems to go fine according to the debug log, and the PageHeader 
is updated with the correct property values.
But in the cells where the UDF is used, only #Name? is displayed.

If it's OK with you, I will mail you the source-code for the COM Addin and 
the UDF, so that you can follow our solution.

Please let me now if I have to get more details on how the actual 
PDF-rendering is started and executed.

Best Regards
-- 
Peter KarlstrÃ¶m
Midrange AB
Sweden

""Jialiang Ge [MSFT]"" wrote:

> Hello Peter,
> 
> As far as I know, Excel 2002 does not have any built-in function to export 
> a workbook to pdf. According to your description, the export is done by 
> Document Handling System (Documentum). In order to trouble-shoot this 
> unexpected output of the UDF value, I think the first thing to do is to 
> narrow down the focus:
> 1. Is it a product issue of Excel 2002?
> 2. Is it an issue of our Excel 2002 Function Addin?
> 3. Is it a product issue of Document Handling System (Documentum)?
> 4. Is it possible that we pass the wrong parameter from Excel to Documentum?
> 
> ** To identify whether it's an issue of our Excel 2002 Function Addin **
> We can try other functions (either Excel built-in functions like SUM, or 
> other UDFs). If the result is correct for any other functions, it's likely 
> to be an issue of our Excel 2002 Function Addin. I would need you to post 
> some implementation details of the Excel function add-in.
> 
> ** To identify whether it's a product issue of Document Handling System 
> (Documentum) and whether we pass the wrong parameter from Excel to 
> Documentum **
> A. You may try the whole solution in other computers. If every computers 
> work as expected but this one, Documentum is less likely to be the cause of 
> the issue.
> B. You may consider contacting Documentum support for other suggestions. 
> 
> Regards,
> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
> 
> Delighting our customers is our #1 priority. We welcome your comments and 
> suggestions about how we can improve the support we provide to you. Please 
> feel free to let my manager know what you think of the level of service 
> provided. You can send feedback directly to my manager at: 
> msdnmg@microsoft.com.
> 
> ==================================================
> Get notification to my posts through email? Please refer to 
> http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
> 
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues 
> where an initial response from the community or a Microsoft Support 
> Engineer within 1 business day is acceptable. Please note that each follow 
> up response may take approximately 2 business days as the support 
> professional working with you may need further investigation to reach the 
> most efficient resolution. The offering is not appropriate for situations 
> that require urgent, real-time or phone-based interactions or complex 
> project analysis and dump analysis issues. Issues of this nature are best 
> handled working with a dedicated Microsoft Support Engineer by contacting 
> Microsoft Customer Support Services (CSS) at 
> http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> 
>

date: Wed, 3 Sep 2008 02:01:02 -0700 author: Peter KarlstrÃ¶m am

Re: Excel Addin and PDF rendering

Hello

I worked with Peter offline. Here is a summary of the solution:

==== PROBLEM DESCRIPTION ====

We have an automation addin that exposes a UDF, and a COM add-in that 
registers/initializes the automation add-in. A client app automates Excel to 
print a workbook to PDF printer. The workbook contains some cells with the 
UDF. If we open the workbook manually in Excel, the UDFs can be recognized 
successfully. However, if we run the automation client to open the workbook, 
it shows ?NAME# in the cells.

==== ROOT CAUSE ====

?NAME# means that the UDFs are not recognized by Excel. In other words, the 
automation add-in is not loaded into the Excel process.

According to the KB article http://support.microsoft.com/kb/291392, 
automation add-ins are always load on demand:

<quote>
The /A switch that is used in the string value is new to Excel 2002 / Excel 
2003 and is used specifically to load Automation Add-ins. All Automation 
Add-ins are loaded on demand; there is no setting that can change the load 
behavior for an Automation Add-in.
</quote>

Excel loads the automation add-in dll when Excel enters edit mode, and the 
add-in's OPEN value appears in 
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options.  I see Peter 
has already made sure that this OPEN value appears in the registry according 
to his code. But why can't Excel load the automation add-in when the Excel 
instance is started from an automation client?

After some researches, we find that Excel has some additional actions when 
we manually load the addin through the Tools -> Add-in menu item. 
Fortunately, Excel exposes a method to programmatically do this:

 app.AddIns("automation addin's progID").Installed = False
 app.AddIns("automation addin's progID").Installed = True

As long as the above two lines are called before any Excel workbook is 
opened, the UDFs can be recognized successfully.

==== RESOLUTION =====

At the end of the COM Add-in's OnStartupComplete event handler, add two 
lines of code:
<Addin>.Installed =False
<Addin>.Installed = True
where <Addin> is the prog ID of the Automation Addin.

Regards,
Jialiang Ge  (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and 
suggestions about how we can improve the support we provide to you. Please 
feel free to let my manager know what you think of the level of service 
provided. You can send feedback directly to my manager at: 
msdnmg@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

"Peter Karlstr?m" <HayRoller@newsgroup.nospam> wrote in message 
news:D3CA023E-5D10-43FF-A435-E40C6B4A0662@microsoft.com...
> Hi Jialiang Ge
>
> Thank you for your reply.
> I will try to answer your questions and be a little more specific.
>
> 1. This is not a product issue of Excel 2002. When I am logged on with the
> account that creates the PDF files, I can open the spreadsheet and the UDF 
> is
> working as it should.
> 2.  Same as nbr 1 above, UDF works when I open the spreadsheet.
> 3. Documentum is not involved in creating the PDF, other than creating it
> using Adobe Acrobat Professional 7.
>
> Also, there is 2 DLL's involved. First, a COM-Addin which always loads, 
> and
> second, the UDF. The COM-addin checks the UDF Addin in AddinInstance_
> OnStartupComplete. In Wokbook_Open some other controls are made.
>
> I have made some major debugging in the COM Addin of the Excel-process
> during the PDF creation and the following is confirmed:
> 1. The Addin is installed
> 2. The Excel WorkbookOpen event is triggered
> 3. I can retrieve the username that is running (for checking of HKCU
> registry values)
> 4. I check to se if the correct spreadsheet is opened
> 5. I do an update of the pageheader based on values in
> CustomDocumentProperties
> 6. I do a CalculateFull on the Excel application object
> 7. I do a Save of the spreadsheet
>
> Everything seems to go fine according to the debug log, and the PageHeader
> is updated with the correct property values.
> But in the cells where the UDF is used, only #Name? is displayed.
>
> If it's OK with you, I will mail you the source-code for the COM Addin and
> the UDF, so that you can follow our solution.
>
> Please let me now if I have to get more details on how the actual
> PDF-rendering is started and executed.
>
> Best Regards
> -- 
> Peter Karlstr?m
> Midrange AB
> Sweden
>
>
> ""Jialiang Ge [MSFT]"" wrote:
>
>> Hello Peter,
>>
>> As far as I know, Excel 2002 does not have any built-in function to 
>> export
>> a workbook to pdf. According to your description, the export is done by
>> Document Handling System (Documentum). In order to trouble-shoot this
>> unexpected output of the UDF value, I think the first thing to do is to
>> narrow down the focus:
>> 1. Is it a product issue of Excel 2002?
>> 2. Is it an issue of our Excel 2002 Function Addin?
>> 3. Is it a product issue of Document Handling System (Documentum)?
>> 4. Is it possible that we pass the wrong parameter from Excel to 
>> Documentum?
>>
>> ** To identify whether it's an issue of our Excel 2002 Function Addin **
>> We can try other functions (either Excel built-in functions like SUM, or
>> other UDFs). If the result is correct for any other functions, it's 
>> likely
>> to be an issue of our Excel 2002 Function Addin. I would need you to post
>> some implementation details of the Excel function add-in.
>>
>> ** To identify whether it's a product issue of Document Handling System
>> (Documentum) and whether we pass the wrong parameter from Excel to
>> Documentum **
>> A. You may try the whole solution in other computers. If every computers
>> work as expected but this one, Documentum is less likely to be the cause 
>> of
>> the issue.
>> B. You may consider contacting Documentum support for other suggestions.
>>
>> Regards,
>> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
>> Microsoft Online Community Support
>>
>> Delighting our customers is our #1 priority. We welcome your comments and
>> suggestions about how we can improve the support we provide to you. 
>> Please
>> feel free to let my manager know what you think of the level of service
>> provided. You can send feedback directly to my manager at:
>> msdnmg@microsoft.com.
>>
>> ==================================================
>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
>>
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent 
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each 
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
>> ==================================================
>> This posting is provided "AS IS" with no warranties, and confers no 
>> rights.
>>
>>

date: Wed, 24 Sep 2008 13:42:27 +0800 author: Jialiang Ge [MSFT]