14 Replies Latest reply: Dec 7, 2010 6:09 AM by alxtoth RSS

    Why do users inconsistently get "Macro parse failed. Functionality was lost." error on AccessPoint doc for Excel export?

      I'm struggling with the "Macro parse failed..." error on an AccessPoint document (developer 8.5, Server 8.5). The document has a button that generates an Excel report (exports various sheet objects to a preformated Excel template file, makes selections within QlikView) and prompts user for a name and file folder/path.

      The macro works fine in development always (when there are no typos!) and in production (AccesPoint) most of the time. I can't figure out why some users get the Macro parse failed error and can't export anything (using the macro) while others have no problems. I experience the error ocassionally but after the export has completed.

      The Excel file template is stored in a shared directory and the default file path for QlikView to save the file is on the user's local drive (My Docs), so I don't think permissions are an issue. But, then again, I really have no idea.

      Thoughts anyone?!

      Thanks / Gracias!

        • Why do users inconsistently get "Macro parse failed. Functionality was lost." error on AccessPoint doc for Excel export?
          Rob Wunderlich

          Perhaps different "Always Override Security" settings between the different users?

          -Rob

            • Why do users inconsistently get "Macro parse failed. Functionality was lost." error on AccessPoint doc for Excel export?

              Rob - I thought I could only have one Macro security setting for users. (Below is the Doc Properties < Security tab.)

              Is there a way to setup different security for users? I guess I'd want to have two types of users: the majority are just standard level (to avoid this parse fail) and a few users have admin level. Honestly, though, at present we keep our Access Point documents 'protected' from users and do not allow server objects or reports. Not sure if what I'm saying has anything to do with this macro parse error or the security settings in QlikView.

                • Why do users inconsistently get "Macro parse failed. Functionality was lost." error on AccessPoint doc for Excel export?
                  Rob Wunderlich

                  Tyler - what client(s) (Win, IE, AJAX) are your clients using? And what version?

                  And can you post the macro?

                  -Rob

                    • Why do users inconsistently get "Macro parse failed. Functionality was lost." error on AccessPoint doc for Excel export?

                      IE Plugin only (v 8.5 not sure which one... QV Server build 6326 and Client Builld 8.50.6206.0409.40 ); IE Version 7.0.5730.13.

                      Macro (brace yourself!)

                       

                      ''---------------------------------------------------

                      '====================================================================
                      ' Full Report AUTOMATED
                      '====================================================================
                      SUB Full_Report_Automated

                      Dim answer
                      answer=MsgBox("This may take a couple of minutes. Please do not COPY or PASTE during this process." & chr(10) & chr(10) & "Click OK to continue.", 1 ,"Exporting Report to Excel")

                      if answer = 2 then
                      exit sub
                      else
                      'Open the destination spreadsheet
                      Path = "\\SERVER\Production\_Apps\BRAND\Reports\"
                      Filename = "Brand_View_Sales_Report.xls"
                      Set XLApp = CreateObject("Excel.Application")
                      XLApp.Visible = False
                      Set XLDoc = XLApp.Workbooks.Open(Path & Filename)

                      ' SHEET NAMES DECLARE
                      Sheet1 = "ReportGen" ' QV Sheet name
                      XLSheetABOUT = "About"
                      XLSheet1 = "Summary"
                      XLSheet2 = "Products"
                      XLSheet3 = "TYLY Var"
                      XLSheet4 = "Yesterday"

                      ' Update Report information on ABOUT tab
                      Dim BaseRow, BaseColumn
                      BaseColumn = 2
                      BaseRow = 12
                      XLDoc.Worksheets(XLSheetABOUT).Activate
                      CALL CurrentSelections ( XLApp, BaseRow, BaseColumn )

                      ' Current Date (of report generation)
                      dim today
                      today = date
                      XLApp.Cells(8, 3).Value = today

                      ' COPY AND PASTE LAST UPDATE TIMESTAMP TO THE THREE TABS
                      ActiveDocument.Sheets(Sheet1).Activate

                      ' Export Summary Sheet
                      XLDoc.Worksheets(XLSheet1).Activate
                      ActiveDocument.GetSheetObject("TX_UPDATE").CopyTextToClipboard
                      XLDoc.Worksheets(XLSheet1).Range("C1").Select
                      XLDoc.Worksheets(XLSheet1).Paste()
                      CALL Export_Summary ( XLApp, XLDoc, Sheet1, XLSheet1)

                      ' Export %Var Reports
                      XLDoc.Worksheets(XLSheet3).Activate
                      ActiveDocument.GetSheetObject("TX_UPDATE").CopyTextToClipboard
                      XLDoc.Worksheets(XLSheet3).Range("C1").Select
                      XLDoc.Worksheets(XLSheet3).Paste()
                      CALL Export_TYLY_VAR ( XLApp, XLDoc, Sheet1, XLSheet3)
                      XLDoc.Worksheets(XLSheet3).Range("A1").Select

                      ' End up on Page 1
                      XLDoc.Worksheets(XLSheetABOUT).Activate
                      XLDoc.Worksheets(XLSheetABOUT).Range("A1").Select

                      XLApp.Visible = TRUE

                      If Err.Number <> 0 Then
                      MsgBox "Not all sheets were properly loaded. Please verify the data."
                      End If

                      ' SAVE FILE
                      FileDate = Month(Date()) & "-" & Day(Date()) & "-" & year(Date())
                      File_Description = inputbox ("Please enter one or two words to describe this file (e.g. BO Knits)" & chr(10) & "Note: The file will be saved to your H:\ drive, My Documents.","Name your File")
                      Destination_Path = "H:\My Documents\"
                      FullDocName = Destination_Path & cstr(FileDate) & " " & File_Description & " - QlikView Global Analytics Summary"
                      XLApp.DisplayAlerts = False
                      XLDoc.SaveAs FullDocName

                      end if

                      END SUB


                      '====================================================================
                      ' EXPORT Summary
                      '====================================================================
                      SUB Export_Summary ( XLApp, XLDoc, QVSheet, SheetID )

                      ''''' Select MTD
                      ActiveDocument.Fields("_.timeframe_desc").Select "MTD"

                      '''''MTD Copy & Paste base data to Excel
                      Call ExportChart(XLDoc, SheetID, "SUM_LIQ_VAR", "J49")
                      Call ExportChart(XLDoc, SheetID, "SUM_LIQ_TY", "I49")
                      Call ExportChart(XLDoc, SheetID, "SUM_P_VAR", "H49")
                      Call ExportChart(XLDoc, SheetID, "SUM_P_TY", "G49")
                      Call ExportChart(XLDoc, SheetID, "SUM_RP_VAR", "F49")
                      Call ExportChart(XLDoc, SheetID, "SUM_RP_TY", "E49")
                      Call ExportChart(XLDoc, SheetID, "SUM_TOT_VAR", "D49")
                      Call ExportChart(XLDoc, SheetID, "SUM_TOT_TY", "C49")

                      ''''' Select WTD
                      ActiveDocument.Fields("_.timeframe_desc").Select "WTD"
                      '''''WTD Copy & Paste base data to Excel
                      Call ExportChart(XLDoc, SheetID, "SUM_LIQ_VAR", "J27")
                      Call ExportChart(XLDoc, SheetID, "SUM_LIQ_TY", "I27")
                      Call ExportChart(XLDoc, SheetID, "SUM_P_VAR", "H27")
                      Call ExportChart(XLDoc, SheetID, "SUM_P_TY", "G27")
                      Call ExportChart(XLDoc, SheetID, "SUM_RP_VAR", "F27")
                      Call ExportChart(XLDoc, SheetID, "SUM_RP_TY", "E27")
                      Call ExportChart(XLDoc, SheetID, "SUM_TOT_VAR", "D27")
                      Call ExportChart(XLDoc, SheetID, "SUM_TOT_TY", "C27")

                      ''''' Select LW
                      ActiveDocument.Fields("_.timeframe_desc").Select "L Week"
                      '''''LAST WEEK Copy & Paste base data to Excel
                      Call ExportChart(XLDoc, SheetID, "SUM_LIQ_VAR", "J5")
                      Call ExportChart(XLDoc, SheetID, "SUM_LIQ_TY", "I5")
                      Call ExportChart(XLDoc, SheetID, "SUM_P_VAR", "H5")
                      Call ExportChart(XLDoc, SheetID, "SUM_P_TY", "G5")
                      Call ExportChart(XLDoc, SheetID, "SUM_RP_VAR", "F5")
                      Call ExportChart(XLDoc, SheetID, "SUM_RP_TY", "E5")
                      Call ExportChart(XLDoc, SheetID, "SUM_TOT_VAR", "D5")
                      Call ExportChart(XLDoc, SheetID, "SUM_TOT_TY", "C5")

                      END SUB

                      '====================================================================
                      ' EXPORT TY LY VARIANCE
                      '====================================================================
                      SUB Export_TYLY_VAR ( XLApp, XLDoc, QVSheet, SheetID )

                      ''''' LW
                      ActiveDocument.Fields("_.timeframe_desc").Select "L Week"
                      ' NET SALES
                      ActiveDocument.Fields("x.metric_desc").Select "Net Sales"
                      Call ExportChart(XLDoc, SheetID, "SUM_VAR", "C4")
                      ' GM$
                      ActiveDocument.Fields("x.metric_desc").Select "GM$"
                      Call ExportChart(XLDoc, SheetID, "SUM_VAR", "N4")


                      ''''' WTD
                      ActiveDocument.Fields("_.timeframe_desc").Select "WTD"
                      ' NET SALES
                      ActiveDocument.Fields("x.metric_desc").Select "Net Sales"
                      Call ExportChart(XLDoc, SheetID, "SUM_VAR", "C20")
                      ' GM$
                      ActiveDocument.Fields("x.metric_desc").Select "GM$"
                      Call ExportChart(XLDoc, SheetID, "SUM_VAR", "N20")


                      ''''' MTD
                      ActiveDocument.Fields("_.timeframe_desc").Select "MTD"
                      ' NET SALES
                      ActiveDocument.Fields("x.metric_desc").Select "Net Sales"
                      Call ExportChart(XLDoc, SheetID, "SUM_VAR", "C36")
                      ' GM$
                      ActiveDocument.Fields("x.metric_desc").Select "GM$"
                      Call ExportChart(XLDoc, SheetID, "SUM_VAR", "N36")

                      END SUB


                      '====================================================================
                      ' Export Chart routine
                      '====================================================================
                      Sub ExportChart( XLS, SheetID, ObjID, RangeID)

                      XLS.Worksheets(SheetID).Activate
                      ActiveDocument.GetSheetObject(ObjID).CopyTableToClipboard True
                      XLS.Worksheets(SheetID).Range(RangeID).Select
                      XLS.Worksheets(SheetID).Paste()

                      END SUB


                      '====================================================================
                      ' Current Selections
                      '====================================================================
                      SUB CurrentSelections ( XLApp, BaseRow, BaseColumn )
                      set x = ActiveDocument.GetCurrentSelections
                      s = x.Selections
                      v = x.VarId
                      SecondColumn = BaseColumn + 1
                      XLApp.Cells(BaseRow, BaseColumn).Value = "Field"
                      XLApp.Cells(BaseRow, SecondColumn).Value = "Selection"

                      for i = lbound(s) to ubound(s)
                      ' msgbox v(i)&" = "&s(i)
                      field = v(i)
                      value = s(i)
                      XLApp.Cells(BaseRow + i, BaseColumn).Value = field
                      XLApp.Cells(BaseRow + i,SecondColumn).Value = value

                      next

                      END SUB

                • Why do users inconsistently get "Macro parse failed. Functionality was lost." error on AccessPoint doc for Excel export?
                  Mike Welham

                  Has there been any definitive answer to this as I've got the same problem when trying to export tables to Excel. My test code is as follows:

                  Dim xlSheet
                  Set xlApp = CreateObject("Excel.Application")
                  xlApp.Visible = TRUE
                  Set xlDoc = XLApp.Workbooks.Add
                  Set xlSheet = xlDoc.Worksheets(1)
                  ActiveDocument.GetApplication.WaitForIdle

                  ' Next command causes error:
                  ActiveDocument.GetSheetObject("CH36").CopyTableToClipboard true
                  xlSheet.Paste xlSheet.Range("A5")

                  Works fine locally, but fails through IE plugin and also when using Analyzer to load the qvw from the server.