Migrate / Convert BEX Workbook to Analysis for Excel (2.1SP3)

[Former Member]

We are planning to switch from uysing BEx Analyzer to Analysis for Office. We are however struggling with migrating existing BEx workbooks to the
Analysis for Office format.

I've tried numerous things:
* Tried on different PC's
* Tried on 2.1SP & 2.1SP3
* Tried with workbook in favorites & in roles
* Tried with different workbooks, all pretty simple, even with fresh created ones just for testing this
* Tried with numerous different settings in the Analysis for Excel conversion settings

We have not yet achieved migrating 1 single workbook in all of the abovetests! In which ever way we do it, we can select the source workbook but it
never generates a new workbook (sometimes the excel just hangs for eternity end the only solution is killing the session via the windows task manager, other times it stops pretty quick with a very generic error "Object reference not set to an instance of an object"....).

I logged a support-message for this, but i just wanted to hear if others have had the same experience (and maybe have some pointers towards a solution) or even if they have had no issues with this functionality and migrated their workbooks without a problem?

[former_member198454 · Accepted solution]

Hello Tom. We are, like you, preparing to convert our users from BEx Analyzer 7 to Analysis for Office.

As a test we took approximately 10 of our most robust workbooks and were able to successfully convert them to Analysis for Office except one. I still have not been able to get it converted unfortunately. We use Office 2010. We are using 2.2 SP1 but I converted these on 2.0 SP4.

One of them initially was giving me an error but I hid the SAPBEXqueriesDefunct and SAPBEXfiltersDefunct sheets and then it migrated. No clue why that worked. The error message that one was giving me was: 'Nested exception. See inner exception below for more details: That command cannot be used on multiple selections'

The one I cannot convert is giving me the error message: 'Delete method of Style class failed'. I logged a ticket with SAP but they did not have a solution. It appears to be related to Excel styles. I've attempted to clean them up but that didn't work.

Let me know if I can provide any additional information.

[Former Member]

So, SAP-support came with the answer that we should go to 2.2 and see if t worked there. I was a little hesitant because i remember there being quite some complaints on a blogpost from Tammy that bugfixes in 2.1 were no longer there in 2.2.

Since we were having issues with the silent (un)install of 2.1 as well, we did give 2.2 SP1 a try, and it seemed to have solved the issue. I can now convert all my test workbooks.

I am still worried/annoyed about all the 3.X macro's being in the mirgrated Analyssis workbook though, but i guess that's something for a different topic. Closing this one

[Former Member]

So, by now i more or less figured out when my conversion fails and when it works:

• when i set the default workbook to "SAP Default Workbook", execute a query, save it as a new workbook, and try to migrate it, the migration is OK
• when i set the default workbook back to our company's default template,execute a query, save it as a new workbook, and try to migrate it, the migration FAILS
• when i try to convert an existing workbook (whcih was based on the company template), the migration FAILS

>> this seems to point to an issue with the company web template. Keep in mind that this created years ago as a 'save as' from the SAP Default Workbook with just some colors tweaked and an icon added...

I did some comparisons between the Macro's and VBA-code in the workbooks based on the standard and custom template and i see that there are difference (there are more macro's in the custom and also the VBA-coding for macro's in both versions is different), but all these differences are clearly 'SAP differences' (so not something we changed)

>> so, what i fear that our issue is, is that the custom template was already created years ago on an pretty old/early version of BEx. So, it contains old marco's and coding, which is no longer used in the current iteration of BEx Analyzer. The standard SAP template has been updated over the years and thus has more recent macro's & vb coding.

>> The migration functionality is probably developped & tested on pretty recent versions of the standard workbook and our custom version is maybe not compatible?

[Former Member]

One more update (for now): if in my workbooks based on the custom template manually delete the macro's that i don't find in the workbooks of the custom template, i am able to migrate them (again pointing to the same cause for the issue)

[former_member198454]

Thanks for the review, Tom. We have always used the standard template, we did not have a custom template. Many of our workbooks are 3.5 workbooks that we converted to 7.0 a few years ago. Like you mention, I fear as we get into this I am concerned some of our workbooks that were created many years ago will not convert due to the conversion tool being based upon the current 7.0 tool. Do you intend on opening a ticket with SAP? I would be curious to hear their thoughts.

When digging into why my one workbook will not convert I went to Microsoft's site searching on the error message. It is telling me to clean up unused styles. I used utlities I found on Microsoft's site to attempt to clean up styles but it didn't seem to do anything. I then hit the issue in this thread which this user was able to successfully bypass by performing a repair then the usage of the utility but mine still fails: http://answers.microsoft.com/en-us/office/forum/office_2007-excel/unable-to-delete-custom-cell-style.... If I could determine which cell style it didn't like I would be happy to delete it. 

So, perhaps Microsoft's site might have some insight on the error message on possible cleanup you could perform prior to conversion? I asked SAP if they could provide any insight on best practices, what error messages mean, etc. converting these workbooks but was not provided any information. I can live with taking items out but it would be nice to know what items to start with by process of elimination.

Good luck! If I learn any more as we roll out the tool and conversion I will let you know.

Lynn

[Former Member]

I have a support ticket opened at SAP for this, no answer yet...

Your issue seems to be quite different from yours. Tried the repait functionality, no luck

The error i get is very generic (in dutch, but the 1st line is the generic "error was found, so i disconnected all datasources" message, and the 2nd does tell much more, the quite common nested exception error: "object reference not set to an instance of the object")

I did some diging into the log files that are written under C:\Users\<windows_user>\AppData\Local\Temp\Sap\Cof and found the log file that seems to be the generated when the conversion goes into error:

I looked around with "Could not copy style font properties", but didn't find anything useful. At least it already points somewhat to a potential error...

I'll keep this post updated and thanks again for coming back with your experiences

[Former Member]

Linn, Tammy,

many thanks for the feedback of your experiences!

I now managed to migrate some test-workbooks which i created in Analayzer myself (so not a real-life situation, but at least a step in the good direction). Not sure why it now worked..

Next issue/question: our BEx Analyzer custom-template is a very small variation of the SAP Standard (company logo, some color tuning).

Is it normal that when i open the succesfully converted workbook in A4E,

• have to clean up the buttons (chart, filter, information) that no longer work
• have to unhide & delete the columns where the navigation pane is normally 'hidden'
• still have all the marco's from BEx in there.

Certainly the macro's i find annoying. They no longer work and have no function, so it seems logical to remove them, but there are a lot of them and it's a tedious job to delete them one-by-one. did you face the same issue (of the macors being there after conversion)? If so, how did you handle this (clean-up or leave them there?)

[TammyPowlas]

Hi Tom - we don't have a lot of macros, and weren't expecting them to convert, so we moved one-by-one manually

Depending on your volume, perhaps Victor's solution may help with this (but I am not sure)

[Former Member]

But the macro's in my screenshot are SAP standard in BEx Analyzer, not custom-created macro's.

So you didn't have these in your migrated workbooks (the screenshot is from when i open a migrated workbook in Analysis...)?

[TammyPowlas]

No, I did not have or use these in the workbooks. 

[TammyPowlas]

Tom - long ago we did this on a much earlier version of Analysis Office. I don't remember facing these issues.

Would you be willing to download Analysis 1.4 SP15 and try converting?  Is that a possibility?  I would be curious if you face the same issues.

[Former Member]

Thanks for the feedback. Installing another version just for testing this won't be an option sadly enough. Software installation has to go via the 'install guy' at our company and he is overburdened as it is..

[TammyPowlas]

Tom: my go-to reference for conversion of workbooks is this presentation by Tobias Kaufman of SAP - see page 34 http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/602fb1d6-869b-2f10-b69c-89169b6aa...

Another suggestion is to look at Victor's migration service post suggestion

[Former Member]

Thanks for the links

I had already found the mass conversion tool from Victor's post and have requested info (and pricing) from our SAP account manager. (sidenote: I'm quite surprised that SAP dares to position this as a SAP consulting solution, this should be part of the standard package imo, or at least available as free download, but that's a whole other discussion...)

[TammyPowlas]

Tom,

Using this version of 2.2 (patch was released Friday)

I was able to successfully convert old workbooks on an old 7.0x sandbox.  Let's see if others in the community can assist/provide advice