topic Run Excel macro from QV macro in QlikView

Run Excel macro from QV macro

Fri, 20 Nov 2020 18:02:30 GMT

Hi all,

I created a Straight Table in QV which I export to Excel using a macro. Next, I want to run a macro called 'Template' in Excel that I saved into my Personal Workbook in a module called 'Template'. This macro structures the exported table in Excel.

Although I've tried a lot, I always get the following error:

'Cannot run the macro 'Test.xlsm!Template.Template'. The macro may not be available in this workbook or all macros may be disabled.'

In the opened file I cannot see any macros either. So it seems that my Personal Workbook is not available or hidden but I cannot unhide anything in the View menu.

The macro in QV:

Sub exportExcel

set obj = ActiveDocument.GetSheetObject( "CH993" )

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets(1)

obj.CopyTableToClipboard true

XLSheet.Paste

XLSheet.SaveAs "Test.xlsm", 52

XLApp.Application.Run "Test.xlsm!Template.Template"

End Sub

However, when I close the Excel file, open the Excel file, close it again and open it again (I know, it sounds stupid..) I suddenly can run the macro in the saved file by hand!

Does anyone know how to execute the macro in Excel directly from the QV macro?

Thanks in advance

Re: Run Excel macro from QV macro

Anonymous — Tue, 02 Dec 2014 13:30:20 GMT

Hi try the below code

Set XLApp = CreateObject("Excel.Application")

Set XLTemplate = XLApp.Workbooks.Open("C:\Qlikview\Temp.xls")

XLTemplate.Application.Run "StopCalculation"

Regards

Harsha

Re: Run Excel macro from QV macro

Tue, 02 Dec 2014 13:41:11 GMT

Thanks for your response Harsha.

I don't know whether I interpreted your code as you meant, because unfortunately it does not work..

I added the following sub in QV and executed it after I executed the first macro:

Sub test

Set XLApp = CreateObject("Excel.Application")

Set XLTemplate = XLApp.Workbooks.Open("Test.xlsm")

XLTemplate.Application.Run "Template"

End Sub

Re: Run Excel macro from QV macro

Anonymous — Tue, 02 Dec 2014 14:34:38 GMT

Hi Mark

insted of adding the workbook in macro

create a blank workbook with only macro and then try openning it and paste the chart and then run the macro

Or if you tell me exact what you want i can help you

Regards

Harsha

Re: Run Excel macro from QV macro

Tue, 02 Dec 2014 14:47:55 GMT

HI Harsha,

I want to make it possible that the user clicks on a button in QV, which exports the Straight Table to an Excel-file. However, the user needs the data from the table in another template. So I wrote a macro in Excel that changes the table in Excel to this template.

So I want that when the user clicks on the button in QV:

1. Table is exported to Excel

2. Macro in Excel is executed automatically

I hope I'm more clear now!

Re: Run Excel macro from QV macro

Anonymous — Tue, 02 Dec 2014 15:09:44 GMT

Hi Mark

then there are 2 ways od doing it

if you have good knowledge in Excel Vba then try to convert Vba code into Vb script(Qv macros) to create the required template on the fly which is little bit tough

provide a blank template with only macro and export it to that empty template and then save it as different one and then run the macro in it which most of us use

Regards

Harsha

Re: Run Excel macro from QV macro

Tue, 02 Dec 2014 15:21:49 GMT

Sorry I just began a few weeks ago with working with macros!

The second option works and is simple. I thought that the macros I have saved in my Personal Workbook should always be available when a Excel file is opened. But apperently I'm wrong!