on 04-09-2009 4:09 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.