Excel Addin and PDF rendering

[Peter Karlström]

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

 

[Jialiang Ge [MSFT]]

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 ([email protected], 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:
(e-mail address removed).

==================================================
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.

 

[Peter Karlström]

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

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 ([email protected], 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:
(e-mail address removed).

==================================================
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.

 

[Jialiang Ge [MSFT]]

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 ([email protected], 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:
(e-mail address removed).

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

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

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 ([email protected], 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:
(e-mail address removed).

==================================================
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.