SAP DOI Problem: Failing to set document title & Failing to execute macro

[former_member607122]

Hello, this is my first post here in this board and I have the following problem:

I have an Excel Spreadsheet, which is stored in SAP (We are using SAP Gui 7300.1.2.1078, Patchlevel 2). This Spreadsheets contains (several) macros, which i want to run after having filled the table with data, in order to show some diagrams. The document is opened perfectly and also the data input from ALV-Grid into the Excel Sheet works great. Problems appear whenever I want to execute the contained macro (Only in Office 2007!!! Office 2010/2013 are working perfectly fine).

Neither defining the EXPORT Parameter "startup_macro" nor running the "execute_macro" function is executing any macro. If I build a workaround for this problem, by firing the macro via timer, just an error appears. I figured out, that the error mainly comes from the document title, given to the document by SAP. I'm defining the document title as Path + Filename, but SAP is stripping out all "\" and ":" characters and adds an "Tabelle von" respectively "Table from" in front of the desired document_title. Excel then has problems, finding and running the macro.

My research has shown, that in every run, the method "SetDocumentTitle" fails. Could you please help me? Find some code snippets below:

SAP:

CALL METHOD control->get_document_proxy
   EXPORTING document_type = 'Excel.Sheet.8'
               document_format = 'OLE'
               no_flush      = ' '
*             REGISTER_CONTAINER = 'X'
     IMPORTING document_proxy = document
               error = error.

CONCATENATE 'FILE://' wa_file INTO url.
   document_title = wa_file.  "wa_file=C:\Temp\ExcelFile.xls

  CALL METHOD document->open_document
     EXPORTING
      document_title   = document_title
      document_url     = url
      no_flush         = 'X'
      open_inplace     = 'X'
      open_readonly    = ' '    "flag_open_readonly
*    PROTECT_DOCUMENT = ' '
      STARTUP_MACRO    = 'start123'
*    USER_INFO        = user_info
*    ONSAVE_MACRO     =
     IMPORTING
      error            = error
      retcode          = retcode.

Excel-VBA:

Sub Workbook_Activate()

    Call start123

End Sub

Sub start123()

    TimeToRun = Now + TimeValue("00:00:02")

    Application.OnTime TimeToRun, "starter"

End Sub

Thank you in advance, regards,

Stephan

P.S.: I forgot to mention, that in all Excel security measures have been deactivated in order to eliminate this source of failure....

P.S. added

[Former Member]

Hello S. Schwarz,

REF: SAP Note: 1520739 - XXL: Performance of XXL Export with MS Excel 2007 and higher

When user upgrades from lower MS Excel release to 2007 and higher, they don't change required macro settings as it was their in lower MS Excel installation.

With Office 2007 user tries to export large number of rows as the maximum row limit is higher in MS Excel 2007 and higher releases.

To improve performance please ensure to maintain below mentioned macro settings in your MS Excel 2007 and higher releases:
Go to Office button (Top-left icon) -> Excel options -> Trust center -> Trust center settings -> Macro settings
Select following options:
- Enable all macros
- Trust access to the VBA project object model
note: without this XXL Export will work but performance will not be optimal

Performance also depends on Data volume which is exported. Please use filter option on ALV screen and keep the data volume judiciously as low as possible.

Cheers,

Dan Mead

[former_member607122]

I think you got me wrong. As I wrote, I've deactivated all security settings for testing purposes. I tested the behaviour described above under 2 different systems:

• WIN 7, SAP GUI 7.30, Office 2007
• WIN 7, SAP GUI 7.30, Office 2013

Having everything set up exactly the same, the macro is only executing automatically with Office 2013 (loading exactly the same template from SAP). I also don't think that that Data Volume is an issue here, since our Table-Dimensions are something around 10 Rows x 20 Columns.

The point which lead me to the missing "Document_title" is the following:

As you can see in the Screenshot, the VBAProject Title has been set somewhere wrong (if it even has been set). If you open a normal Excel File and look at the VBAProject, you will see that title = filename.

Maybe its due to the error-handling implemented in Excel 2013, that the macro is executed properly... If executed manually, the code both works in Office 2007 and Office 2013.