Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Marcus,
many thanks!
That might indeed make the whole thing a lot easier.