Run Excel macro from QV macro

[#1]

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

[Anonymous]

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

[#3]

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

[Anonymous]

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

[#5]

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!

[Anonymous · Accepted solution]

Hi Mark

then there are 2 ways od doing it

1)

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

2)

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

[#7]

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!