cancel
Showing results for 
Search instead for 
Did you mean: 

Run-time error:7 when export to excel in MDM Data Manager

Former Member
0 Kudos

Hi,

Everytime when I exported a text field which is starting with "+" or "-" in Data Manager, I always got a vb run timer error 7. The vb debug mode told me that the excel "Template1" in "Module1" and in "Excel97InsertMacro" the below code like:

Application .ActiveSheet.Cells(row + startRow + 2, column + 1).value = myarr(row, column)

got exception. In order to get export file, the workaround solution has to manually put a ' sign before the myarr() like:

.... = "'" & myarr(row, column)

And then press F5 to continue, so the export file can be generated in this way.

Currently I have updated our MDM 5.5 SP06 with patch04 on version 5.5.64.84, but this problem is still existing. Can anybody tell me that any MDM updates can have this problem to be solved but not workaround solution ?

Thanks very much for your help

Kind regards,

Wei Dona

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi, Some special character are not support by MDM, XI and ECC .

We are also getting once in a while fail in MDM or XI side.

As of now we are trying to eliminate in the source, and I process manually to import or syndicate when ever a exception happen.

Please check for OSS notes in SAP. There should some information about it.

Former Member
0 Kudos

Thanks for your information.

Any links for OSS notes which related to this issue can help me.

Kind regards,

Wei Dona

former_member198313
Contributor
0 Kudos

Hi Wei,

I am sorry but is there any specific business requirement that you are having +/- sign in the fields.

As basic understanding these kind of signs are usually data quality issues.

However if this is such requirement to store these signs in fields, I am also able to Export to... Excel from Data Manager. Even its possible to syndicate also.

Hence I guess this is not an product error.

However what are the steps you are following to export the file/fields.

+ An

Former Member
0 Kudos

Hi,

I guess you are executing some macros after the export. If not then check if there exists any and delete them. For this, open the Excel, select Tools -> Macro -> Macros. and if you are using then for time being remove them and try exporting so that we can drill down the problem whether this is MDM issue or Office issue.

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi Ankur,

Thanks for your reply.

yes, I would say that the text fields starting with "+" or "-" are our business requirements which used to store the cross check term messages for group chart of accounts. There are thousands of these kind of fields so far. For an example, one cross check named "A059" defined the below message as :

+ 10331 ToM 407 + 10331 ToM 406 >= -5 <= 5

10331 - Bonds - Fair value - 3rd

407 - Impairments / Write Ups

406 - Impairments / Write-Downs

If we try to add a ' sign in front of the above kind of these fields in data manger, the message likes:

'+ 10331 ToM 407 + 10331 ToM 406 >= -5 <= 5

......

And the export can work perfectly. But our business doesn't want this unlike ' sign showing in data manager view. I also understand that syndicator has no problem to export for these fields but most business users would like to export in data manager as it is more flexible to generate ad hoc reports.

I guess that there is embeded excel template with below macros when export fields from data manger to excel file, this macro is automatic called during the MDM data manager export process . I also can see in vb debug mode that it called "template1" with the full vb codes like in below:

Sub Excel97InsertMacro(myarr() As Variant, startRow As Variant)

Dim row, column As Integer

For row = 0 To UBound(myarr, 1)

For column = 0 To UBound(myarr, 2)

Application.ActiveSheet.Cells(row + startRow + 2, column + 1).Value = myarr(row, column)

Next

Next

End Sub

Any more helps would be much appreciated...

Thanks and regards,

Wei Dona

former_member198313
Contributor
0 Kudos

Thanks Wei,

As Jitesh pointed out, please remove/disable this macro for time being and see the result.

I am confident that Export to...Excel will work.

I guess that why you are getting error is because of this line/step of macro.

Application.ActiveSheet.Cells(row + startRow + 2, column + 1).Value = myarr(row, column)

+ An

Former Member
0 Kudos

Hi Ankur,

Could you advise how to remove it as it is only showing up when this problem is coming ?

I checked that when I simply open a new excel there is no any macros in. I am wondering whether it is coming from MDM export to excel process or something else ?

Thanks and regards,

Wei Dona

Former Member
0 Kudos

Hi,

Atleast we have narrowed down the problem. Check the Tools -> Macro -> Macros, if you find any entry simply delete that and try to export again.

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi,

Got some useful information from Microsoft Office Help about macros and templates. Automatic execution of Macros is the part of Excel Startup settings. We can create one template which will load when excel file is opened and applies the settings like font, picture, macros etc. You need to check the excel help on this for permanent removal of this template from startup but temproraily after exporting the records, open the excel file by pressing SHIFT key and check whether you are still getting the exception.

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi Jitesh,

If I start a new excel file, normally there is a default name for this workbook as Book1. And I checked that the Tools -> Macro -> Macros, nothing can be deleted as there is no macros in.

What I can see that it looks different when the export process running from MDM Data Manager. The way to export in data manager like as:

Click File -> Export To -> Excel...-> choose "Available fields:"

I am taking this text field as I mentioned before. And add to "Fields to export:",

-> click "OK" to save this export file as a new name on any place on local

Once this process is starting, the "Exporting to Excel" window in MDM Data Manager can show up and it tells as following info:

Status: Writing

File: cross check.xls (a new name I give it)

I also can see that at the bottom of the window it is showing "250 of 1124 records exported".

But this process can not be finished because the vb run time box is showing up. There are only buttons

of "End", "Debug" and "Help" available. In this mode, if I tried to delete these codes, but the export process has aborted anyway. No export file can be generated.

So I guess that this macro is from MDM ?

Thanks and regards,

Wei Dona

former_member198313
Contributor
0 Kudos

My apologies Wei,

It seems this is automatic macro from MDM and might not be conflicting. I can see the same under Macro --> Visual Basic Editor.

However Jitesh and me were able to export the text fields to excel with your examples.

It seems there is some issue with Excel only.

However try using the setting Macro --> Security. Choose option "Medium".

Also, is this problem always come on record 250 ?

+ An

Former Member
0 Kudos

Hi,

Why dont you try for 2-3 records?

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi Ankur,

I have checked that the setting Macro --> Security, the option was "Medium".

yes, the problem is coming on the record 251 as I have checked in Data Manager that this field is starting as "+".

Thanks for your help

Kind regards,

Wei Dona

Former Member
0 Kudos

Hi,

Make the security to low and try with 2-3 records containing fields "+".

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi Jitesh,

Yes, I have tested with another table contains only 3 records, and change a text field to start with + sign. And make an export again, it works without exception !

Any possible solution you may have ?

Many thanks for your help

Kind regards,

Wei Dona

Former Member
0 Kudos

Hi Jitesh,

Verry sorry, I have to correct my message before which I have sent to you.

I have checked the exported excel file with workaround solution, I can see that in the exported file the first record containing + sign is actually in line 97. And in MDM Data Manager I can find this record but it is not easy to know how the export process in MDM taking this record to excel ( the sequence) ?

For testing only 3 records, it works without exception even I can see that the generated exported file has contained the same vb macros.

So I guess that perhaps the message related to the number 250 records in MDM Data Manager during the export process doesn't mean the real records. Hope I am right here ? Or any configuration in MDM has to be adjusted before executing export ?

Thanks & regards,

Wei Dona

Former Member
0 Kudos

Hi,

I just wanted to put focus on particular records. The exception may be due to some records thats why i suggested to try with 2-3 records. If this works fine for you that means that means MDM and Office are working as expected. You may need to drill your search to find out the records creating exception.

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi Jitesh,

Thanks very much for your reply.

The problem seems not actually related with starting "+" or "-" sign in the large text field even I thought about it in the past.

It looked that the length of the text has determined whether this runtime error is showing up or not in vb code for this myarr(). I did a test that if I limited the length of my text to less than 1024, the export could get through. But if I put one more string added to the same text, I got exception for this vb runtime error.

So I think that it should be a general problem but not specific text, right ?

Thanks again

Kind regards,

Wei Dona

former_member198313
Contributor
0 Kudos

Hi Wei,

You found the problem.

Excel 2003 has limit of 1024 char in a cell length. http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Excel 2007 does not have such limit.. http://office.microsoft.com/en-us/excel/HP100738491033.aspx?pid=CH101030621033

+ An

*This is NOT SAP official message

Former Member
0 Kudos

Hi Wei & An,

There is no such limit on Cells in Excel. I tried entering more than 1024 characters and it has accepted. I think its the macro which is enforcing this limit. Wei if possible and feasible for you, reinstall the MS Office. This will definitely solve your problem.

Regards,

Jitesh Talreja

former_member198313
Contributor
0 Kudos

Hi Wei,

As Jitesh rightly pointed that you need to reinstall Excel.

However Excel 2003 has this limitation as pointed out in my previous reply.

You require excel 2007 to resolve your problem.

Hope this helps,

+ An

*This is NOT SAP official message

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

I am able to export the records even though my text field starts with "+" or "-". I am using Microsoft Office 2003 and SAP MDM 5.5.61.48. I dont think the issue is related to MDM Server version, this is because of Office version or settings.

Regards,

Jitesh Talreja

Former Member
0 Kudos

Hi,

I have checked that my two pcs, one with Microsoft Office version 2003 (11.8105.8107) SP2 and another one with version 2002 (10.6850.6845) SP3. In both cases, I have the same "run-time" error.

Kind regards,

Wei Dona