Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SteveZheng
Contributor
Contributor

Qlikview Macro call Excel macro doesn't work in Wins10

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

11 Replies
m_woolf
Master II
Master II

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

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

SteveZheng
Contributor
Contributor
Author

Hi, 

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

 

Thanks,
Steve

m_woolf
Master II
Master II

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
Contributor
Contributor
Author

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
Master II
Master II

This is vbscript code from a QlikView application.

SteveZheng
Contributor
Contributor
Author

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

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

SteveZheng
Contributor
Contributor
Author

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
Master II
Master II

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.