topic Re: Launch an Excel_file and its VBA macro from QlikView? in QlikView

Launch an Excel_file and its VBA macro from QlikView?

datanibbler — Tue, 27 Oct 2015 10:26:32 GMT

Hi,

I am currently working to implement a multiple-tier system of Excel_files and a presentation for a daily meeting.

It all works fine so far. The order of execution is this:

- The macro in the main Excel file opens all other files (from which data is drawn via formulas)

- The main Excel file is saved

- All other files are closed

(in the main Excel file, there are a number of charts based on the data)

- The macro then opens a ppt presentation, updates all links and closes the Excel file.

The point where I realize the limits of Excel is the charts. Charts created in QlikView are simply nicer and there are some advanced possibilities which are not feasible in Excel.

<=> on the other hand, using QlikView to generate the charts would mean one extra program and the one running that Excel macro would need the ability to manually start the QlikView_app.

=> To keep that as simple as possible, what I'd like to consider is the QlikView_app opening that Excel file, running the macro inside and then just generating the charts once the macro has finished (data is updated) and finally exporting those charts in ppt.

Is that possible? Can QlikView somehow open an Excel file and trigger a VBA macro?

Thanks a lot!

Best regards,

DataNibbler

Re: Launch an Excel_file and its VBA macro from QlikView?

marcus_sommer — Tue, 27 Oct 2015 10:43:44 GMT

Hi DataNibbler,

you could easily use vbs to open your macro. This here is a way which I use productive:

QVW with execute-statement:

SET vCSCRIPT = 'c:\windows\system32\cscript.exe';

EXECUTE $(vCSCRIPT) "D:\YourExcelStartScript.vbs";

VBS with open excel-statement:

Dim fso, file

Set fso = CreateObject("Scripting.FileSystemObject")

Set file = fso.OpenTextFile("D:\TaskParameter.txt", 2)

file.WriteLine "run"

file.Close

Dim objExcel

Set objExcel = CreateObject("Excel.Application")

objExcel.Application.DisplayAlerts = false

objExcel.Application.Visible = true

objExcel.Application.Workbooks.Open "D:\YourFolder\YourExcel.xls", "3"

objExcel.Application.DisplayAlerts = true

objExcel.Application.Quit

Set file = fso.OpenTextFile("D:\TaskParameter.txt", 2)

file.WriteLine "stop"

file.Close

And within the excel is an OnOpen-Trigger which reads the parameter and started by "run" your routines. The parameter are for the purpose to be able to open your excel without the macro-execution if they are not triggered by a task.

- Marcus

Re: Launch an Excel_file and its VBA macro from QlikView?

datanibbler — Tue, 27 Oct 2015 10:48:46 GMT

Hi Marcus,

many thanks!

That might indeed make the whole thing a lot easier.