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

[#1]

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!

[rwunderlich]

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

-Rob

[#3]

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.

[rwunderlich]

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

And can you post the macro?

-Rob

[#5]

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

[#6]

I am having basically the same problem with a similar macro. The macro works fine on my machine and when I run it through IE on the server I have no problems. I had other people try it in their Qlikview application and it works fine and then when they go through IE they get the macro parse failed. I am thinking it has something to do with the users browsers settings or the QlikOCX Control plugin but haven't found anything yet.

I am using Qlikview 8.20.5423.2

[biester]

Have you already tried Shift+Ctrl+M => "Give System Access to Module Script" (in IEPlugin and Analyzer) ?

Rgds,
Joachim

[Oleg_Troyansky]

Security settings is obviously the first area to look into... Another possibility (long shot) that have been known to cause "random" errors is timing - for example, when you Activate a sheet, you need to wait for all the objects on the sheet to be calculated. For this purpose, try inserting

ActiveDocument.GetApplication.WaitForIdle

in key points, especially after Active commands.

Not sure that this is your problem, but it might be...

[#9]

I think you are right Oleg, I had a macro running when the document was opened and I think that might be causing the "Macro parse failed". I think it might be trying to run before the user selects their security settings. I removed it and we are not getting the error anymore.

Another error that I am getting is when the macro tries to save an excel file. I think the "Macro parse failed" error is happening because that user does not have access to write to the folder. That would make sense eh?

[r3iuk]

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.