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: 
datanibbler
Champion
Champion

Launch an Excel_file and its VBA macro from QlikView?


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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

2 Replies
marcus_sommer

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

datanibbler
Champion
Champion
Author

Hi Marcus,

many thanks!

That might indeed make the whole thing a lot easier.