Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

matheus_silva
Not applicable

Macro export to excel in Qlikview Server

Hi folks.

I will ask a question which the answer i know, but just to make sure, let's go.

I created a vision to my prevention loss department and bacause complexity i did mutiples chart diferent and now they want export all chart to excel in once time in the same sheet....OK...I did this using Macro...Like this:

Sub Exportar_Objetos_Excel

  Set XLApp = CreateObject("Excel.Application")

  Set XLDoc = XLApp.Workbooks.Add

  Set XLSheet = XLDoc.Worksheets(1)

  XLApp.Visible = True

  XLApp.Cells.Select

     XLApp.ActiveWindow.DisplayGridlines = False

  XLApp.Range("A5").Select

  ActiveDocument.GetSheetObject("CH01").CopyBitMapToClipboard

  XLSheet.Paste

  XLSheet.Range("B25").Select

  ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard True

  XLSheet.Paste

End Sub

Its work...buuut only in Qlikiview Desktop, with Office installed...But the user use qlikview in browser, you know, There were not  excel installed in my server, so my Macro not worked...=/
Buuuuuut, in Qlikview server there is a option "SEND TO EXCEL", and this f**** work in browser, why?

Summing up, i nedd create a button in qlikview, with macro which does the same "send to excel", but send mutiples chart once time, but ot usign "CreateObject("Excel.Application")" , because this not work on server...

If not exist solution, I understand...but if someone has passed to a situation similar, and resolved...coment!!!

I'll be very thankfull.

Thanks for your time.

"sorry by my english, i'm training XD"

1 Solution

Accepted Solutions
Not applicable

Re: Macro export to excel in Qlikview Server

The 'Set XLApp = CreateObject("Excel.Application")' is actually opening up an instance of Excel. In fact, you should have seen Excel open up on your desktop when you ran it. Especially since you have the 'XLApp.Visible = True' in your code above. Since Excel is not installed on your server this is why it is not working, as the macro is trying to find and run the Excel application.

The only other option to export to Excel in a macro is to do similar to the following:

Sub ExportExcel

        set obj = ActiveDocument.GetSheetObject("CH01")

        obj.ExportBiff "C:\CH01.xls"

End sub

However, unfortunately this will not allow you to export multiple charts to the same Excel workbook.

Since your requirement is to have multiple charts in the same Excel, the only option I see for you is to have Excel installed on your server. Then take a look at the following link for a great example of exporting multiple objects to the same Excel workbook: http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

Hope this is helpful in confirming your suspicions.

- Stan

7 Replies
avinashelite
Not applicable

Re: Macro export to excel in Qlikview Server

have you enabled the Allow system access in the server

Try like this

In access point

Ctrl+Shift+M> Click on Enable System access

matheus_silva
Not applicable

Re: Macro export to excel in Qlikview Server

Did not work...=/

Ctrl+Shift+M.JPG

My Macro script

macro.JPG

Does not exist a expression in vbscript which say to pc look to local machine not to server?

Look bellow, this is in browser:

send to excel.jpg

ok.jpg

After this, a excel save in c:\user\download and finish...

I want this, but create a Macro, for send all chart once time...

Not applicable

Re: Macro export to excel in Qlikview Server

The 'Set XLApp = CreateObject("Excel.Application")' is actually opening up an instance of Excel. In fact, you should have seen Excel open up on your desktop when you ran it. Especially since you have the 'XLApp.Visible = True' in your code above. Since Excel is not installed on your server this is why it is not working, as the macro is trying to find and run the Excel application.

The only other option to export to Excel in a macro is to do similar to the following:

Sub ExportExcel

        set obj = ActiveDocument.GetSheetObject("CH01")

        obj.ExportBiff "C:\CH01.xls"

End sub

However, unfortunately this will not allow you to export multiple charts to the same Excel workbook.

Since your requirement is to have multiple charts in the same Excel, the only option I see for you is to have Excel installed on your server. Then take a look at the following link for a great example of exporting multiple objects to the same Excel workbook: http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

Hope this is helpful in confirming your suspicions.

- Stan

santiago_respane
Not applicable

Re: Macro export to excel in Qlikview Server

Hi,

besides Avinash advice that is a must do in order for you macro to work, here's my version of the macro for exporting.

The idea is to have one macro for all charts in the report.

Macro code:

sub ExportChart

      set MyTable = ActiveDocument.GetSheetObject(ActiveDocument.Variables("v.ChartToExport").GetContent().String)

      Const xlCenter = -4108

      set XLApp = CreateObject("Excel.Application")

      XLApp.Visible = True

      set XLDoc = XLApp.Workbooks.Add

    

     'Some title setting

      XLDoc.Sheets(1).name = MyTable.GetCaption.Name.v

      set XLSheet = XLDoc.Worksheets(1)

    

     'Some sheet formatting

      set XLSheet = XLDoc.Worksheets(1)

      XLSheet.Columns.Interior.color = RGB(255,255,255)

    

     'Copy table

      Mytable.CopyTableToClipboard true

      XLSheet.Paste XLSheet.Range("A1")

    

      XLSheet.Columns("A:Z").EntireColumn.AutoFit

      XLSheet.Columns("A:Z").VerticalAlignment = xlCenter

End Sub

In your case for exporting multiple charts, you can add a button, you add two actions in pairs for every object to export, as follows:

  1. Object One
    1. External -> Set Variable -> v.ChartToExport = MyTableID1
    2. External -> Run MAcro -> ExportChart
  2. Object Two
    1. External -> Set Variable -> v.ChartToExport = MyTableID2
    2. External -> Run MAcro -> ExportChart
  3. Object 3

This will generate one xls file for each object.

Please let me know if this helps.

Kind regards,

Not applicable

Re: Macro export to excel in Qlikview Server

This will, of course, only work if Excel is installed on the machine where the marco is run.

matheus_silva
Not applicable

Re: Macro export to excel in Qlikview Server

Thank you guys, but as Stan said, don't have any way to do that, if the Excel not installed in the server, unfortunately.

Only way to do that is export do .txt or .csv.

Thank you Avinash, Santiago and Stan...I tested all code, and all worked, but to qlikview desktop,like i said.

But thanks anyway...Until next time.

santiago_respane
Not applicable

Re: Macro export to excel in Qlikview Server

You had already specified that in your post, and as you said, OF COURSE it will only work if excel is installed in client machine.

Thanks anyway for your reply.