topic Re: Open an Excel workbook using Qlikview in QlikView

Open an Excel workbook using Qlikview

Anonymous — Wed, 05 Oct 2016 10:48:29 GMT

Hi People,

I have found a few solutions on forum but nothing is working for me:

SET vQVFile = 'C:\Users\ljar01\Desktop\Klienci.xlsx';

sub ddd (Root)

set XLApp = CreateObject("Excel.Application");

set XLDOC = XLApp.Workbooks.Open($(vQVFile));

XLApp.Visible = True;

end sub

If there is sub ddd () without (root) there is an error:

Script line error:

sub ddd ()

I was also wondering if the reason lies in " ; " but it is not working whether these signs are in code or not.

My goal is to store database as csv and open it automatically via Qlikview to run a workbook macro.

How can i open Excel document via Qlikview?

Please help,

Best regards,

Jacek Antek

Re: Open an Excel workbook using Qlikview

zhadrakas — Wed, 05 Oct 2016 10:54:29 GMT

Hello,

one solution would be to create a Trigger "open URL" that opens

\\networkpath\Folder\Subfolder\InputData.xls

but thats probably not the best solution for your case.

Why dont you get your macro working in QlikView?

regards

tim

Re: Open an Excel workbook using Qlikview

Anonymous — Wed, 05 Oct 2016 11:08:59 GMT

Hi Tim Driller,

thank you for your tip but this CSV will be my database used in other Projects.

Unfortunately my company works mainly on Excel files so it has to be in that way...

Jacek

Re: Open an Excel workbook using Qlikview

marcus_sommer — Wed, 05 Oct 2016 11:09:20 GMT

Where do you write this code - within the macro-editor? If you used there the following:

sub ddd

dim vQVFile

vQVFile = "C:\Users\ljar01\Desktop\Klienci.xlsx"

set XLApp = CreateObject("Excel.Application")

set XLDOC = XLApp.Workbooks.Open(vQVFile)

XLApp.Visible = True

end sub

it should work.

- Marcus

Re: Open an Excel workbook using Qlikview

Anonymous — Wed, 05 Oct 2016 11:15:47 GMT

Marcus,

it is working ! I was writing this in script code.

Hmm sa now should i call somehow this macro to run it via load script ?

Jacek

Re: Open an Excel workbook using Qlikview

marcus_sommer — Wed, 05 Oct 2016 11:24:31 GMT

During the load-run you couldn't execute a sub-routine only macro-functions. I'm not sure if this kind of macro would work within a function triggered from load because statements which need an ActiveDocument won't work - by statements which need ActiveX I don't know.

As alternatively you could use an EXECUTE statement like: Re: Execute statement problem.

- Marcus

Re: Open an Excel workbook using Qlikview

Anonymous — Wed, 05 Oct 2016 12:08:44 GMT

Wow,

this is difficult.

I am trying to do something like this:

Execute start "" "C:\Users\ljar01\Desktop\Klienci.xlsx"

but this is not working...

Jacek

Re: Open an Excel workbook using Qlikview

Anonymous — Wed, 05 Oct 2016 12:19:42 GMT

hmm thisi is working:

EXECUTE C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE C:\Users\ljar01\Desktop\Klienci.xlsx;

but i am wondering if is possible to close alse this document and

if is possible to shorten "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" this one.

For example EXECUTE cmd.exe start excel.exe...

Jacek Antek

Re: Open an Excel workbook using Qlikview

marcus_sommer — Wed, 05 Oct 2016 12:26:21 GMT

You could put the path to the exe and to the file into variables and call them within the EXECUTE statement. But I think I would rather use the above mentioned way to call a vbs-batch which opened and closed the xls and does maybe some other things than calling the xls directly - like in this example: Re: Run Excel from QMC.

- Marcus

Re: Open an Excel workbook using Qlikview

Anonymous — Wed, 05 Oct 2016 12:32:00 GMT

I will try this !

Thank You very much Marcus!

Warm Regards,

Jacek

Re: Open an Excel workbook using Qlikview

Anonymous — Mon, 24 Oct 2016 08:51:37 GMT

Hi Guys, hi Marcus.

Execute is working like a charm nut only when i am turn on QlikView manually.

From publisher my macro doesn't work unfortunately.

I wanted to run my vbs file from publisher:

The last one is my application which should open VBS macro and run and close Excel. This is not working...

any ideas ho w to solve this?

Jacek Antek

Re: Open an Excel workbook using Qlikview

Anonymous — Mon, 24 Oct 2016 08:52:01 GMT

Hi Guys, hi Marcus.

Execute is working like a charm nut only when i am turn on QlikView manually.

From publisher my macro doesn't work unfortunately.

I wanted to run my vbs file from publisher:

The last one is my application which should open VBS macro and run and close Excel. This is not working...

any ideas ho w to solve this?

Jacek Antek

Re: Open an Excel workbook using Qlikview

marcus_sommer — Mon, 24 Oct 2016 09:22:45 GMT

There are various reasons why such a execute-batch could fail - often related to wrong/missing user and security configurations. I think the following postings will be helpful for you:

Re: Script consisting only of some EXECUTE commands - impossible?

Re: 'Execute' statement in QVW file under QMC

Re: Run Excel macro via QMC

- Marcus

Re: Open an Excel workbook using Qlikview

marcus_sommer — Mon, 24 Oct 2016 18:09:18 GMT

Run Excel macro via QMC

Re: Open an Excel workbook using Qlikview

brooksc57 — Wed, 04 Aug 2021 17:23:49 GMT

This works for me. I do this because I keep a Master Calendar in Excel that changes each day so I open and close it to simply attract all the changes.

Step 1: Build a macro in qlikview

Sub xlO()
Set oXL=CreateObject("Excel.Application")
f_name="\\ < your path >\MasterCalendar.xlsx"
'f_name="C:\ExcelFile.xlsx"
Set oWB=oXL.Workbooks.Open(f_name)
oWB.Save
oWB.Close
oXL.Quit
End sub

Step2: in Document Properties add an OnOpen Trigger to call the macro.

I have it working in QMC as well.

I'll assume if you have that file open already you might have an issue.

thx

Brooks