Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This code to trigger an Excel macro seems to work fine in W10:
strMacro = "'" & XLWorkbook.Name & "'!" & "MacroName"
xlApp.Run(strMacro)
Hi,
thanks for your reply! Is this written in Qlikview? May I get the context of the two lines?
Thanks,
Steve
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
Hi,
the macro I need is supposed to be written in Qlikview not in Excel. Macro in Qlikview call a macro in Excel.
Steve
This is vbscript code from a QlikView application.
Set XLApp = CreateObject("Excel.Application")
Set XLWorkbook = XLApp.Workbooks.Open("FilePathHere" & "\" & "CreateChart Test.xltm",3)
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
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.