Qlikview Macro call Excel macro doesn't work in Wins10

[SteveZheng]

Hello,

I used to use a qlikview macro to call an excel macro which will perform a certain task in excel in win 7. It worked fine. However since I switched to Win 10, it didn't work any more. The Qlik macro will stop at "oSH.Application.Run "GetSheetsAR" and error message said : "Cannot run the macro 'GetSheetsAR'. The macro may not be available in this workbook or all macros may be disabled."

I went to the excel trusted center and enable the macro settings, but it still didn't work. I also tried add trusted locations in excel, still not work. 

I wonder if anyone has the same issue and is there any solution here? 

(the excel macro AR.xlsm works fine if I manually run it). 

Thanks a lot,

Steve

This is Qlikview macro below:

Sub AR
Set oXL=CreateObject("Excel.Application")
f_name="C:\Users\Public\Documents\NPrinting\Output_AR\MonthlyReconcile\AR.xlsm"
Set oWB=oXL.Workbooks.Open(f_name)
Set oSH=oWB.Worksheets.Item(1)
oSH.Application.Run "GetSheetsAR"
'oWB.Save
'oWB.Close
Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
End Sub

[m_woolf]

This code to trigger an Excel macro seems to work fine in W10:

strMacro = "'" & XLWorkbook.Name & "'!" & "MacroName"
xlApp.Run(strMacro)

[SteveZheng]

Hi, 

thanks for your reply! Is this written in Qlikview? May I get the context of the two lines? 

Thanks,
Steve

[m_woolf]

I don't know what you mean by "the context of the two lines"

xlApp is the Excel application object
XLWorkbook is the workbook object

[SteveZheng]

Hi,

the macro I need is supposed to be written in Qlikview not in Excel. Macro in Qlikview call a macro in Excel. 

Steve

[m_woolf]

This is vbscript code from a QlikView application.

[SteveZheng]

I see. May I know how did you define the xlApp and XlWorkbook?

[m_woolf]

Set XLApp = CreateObject("Excel.Application")
Set XLWorkbook = XLApp.Workbooks.Open("FilePathHere" & "\" & "CreateChart Test.xltm",3)

[SteveZheng]

Hi, 

I changed my script as suggested as below. But it still show me the same error message: "Can't run the macro"****", the macro may not be available in this workbook or all macros may be disabled."

I have go to excel and enable all macro related options. 

Steve

Sub AR
Set xlApp=CreateObject("Excel.Application")
Set XLWorkbook = XLApp.Workbooks.Open("C:\Users\Public\Documents\NPrinting\Output_AR\MonthlyReconcile" & "\" & "AR.xlsm",3)
strMacro = "'" & XLWorkbook.Name & "'!" & "GetSheetsAR"
xlApp.Run(strMacro)

Set oSH=nothing
Set oWB=nothing
Set oXL=nothing
End Sub

[m_woolf]

I don't think your problem is with the code.

I created a test qvw on a W10 machine with your macro script. Then I created a test excel workbook named "AR" with a macro named "GetSheetsAR". When I triggered the macro script in QlikView, it opened the Excel workbook and ran the macro.