PageSetup in Excel 2002

[Peter Karlström]

Hi

One of our COMAddin projects involves customization of the pageheader and
pagefooter in an Excel XP (2002) spreadsheet.
The customization is made in a public sub (Updateproperties) which is called
from a toolbar commandbutton. This works fine.
Now we have added an automatic update when the spreadsheet is opened (in
Workbook_Open) using the previously mentioned public sub UpdateProperties.

Now the vierd stuff begins. When we automatically update the pageheader when
opening the spreadsheet, it gets corrupted. When we right after that manually
uppdate the pageheader/footer from the toolbar during the same session in the
opened file, the header/footer is OK again. The same code is running in both
occassions.

We have discovered that this behavoir only occurs in special versions of
Excel XP (2002).
Using an updated version (10.6845.6845 SP3) it works as expected.
Using an older version (10.4302.4219 SP-2) the corruption occurs.

The customer whiches not to force an Office 2002 SP update, so my question is:
Is there a hotfix/workaround avaliable for this particular error?

Thanks in advance

 

[\Ji Zhou [MSFT]\]

Hello Peter,

The follow KB article lists all issues which were fixed by Office XP SP3,
as well as 6 available hotfix packages. I looked through them, but did not
find a similar one related to the page setup in WorkbookOpen event.
Consequently, I do not think there will be a standalone hotfix for the
issue.

http://support.microsoft.com/kb/836031/

From your description, because the Updateproperties does work in the button
click event handle, but not work well in the WorkbookOpen event handle, my
best guess is that in the Excel XP SP3, the initialization sequence has
changed. So, maybe the PageSetup has not been prepared in the WorkbookOpen
event handle.

Based on this assumption, my suggestion is to create a
System.Windows.Form.Timer in the WorkbookOpen event handler. And then we
can call the Updateproperties from the timer's Tick event. Code looks as
follows. Make sure we are using the Windows Form's Timer because other
timer's Tick event is executed in another thread which may cause new
problems here.

Private WithEvents applicationObject As
Microsoft.Office.Interop.Excel.Application
Private addInInstance As Object
Private WithEvents t As System.Windows.Forms.Timer

Public Sub OnConnection(ByVal application As Object, ByVal connectMode
As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom
As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
applicationObject = application
addInInstance = addInInst
End Sub

Public Sub Workbook_Open(ByVal wb As
Microsoft.Office.Interop.Excel.Workbook) Handles
applicationObject.WorkbookOpen
t = New System.Windows.Forms.Timer
t.Interval = 1
t.Enabled = True
End Sub

Public Sub TimerTick(ByVal sender As Object, ByVal e As
System.EventArgs) Handles t.Tick
t.Enabled = False
Updateproperties()
End Sub

Please let me know if my above suggestion works for your scenario or not?
If not, would you mind letting me know the codes in Updateproperties(), so
I can set up an Excel 2002 SP2 environment and try to reproduce it. And
then I will try my best to research on this to see if I can find a
workaround.

Have a nice day, Peter!

Best regards,
Ji Zhou ([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.