Qlik Community

QlikView Documents

Documents for QlikView related information.

Using VBA Macro a Simple Example

techvarun
Valued Contributor II

Using VBA Macro a Simple Example

Hi All

           I am posting a small example of VBA Scripting to Export a chart into Excel sheet.

          I got the script Here.

Paste the below script in module editor Ctrl +M

  1. Sub ExcelExpwCaption 
  2.      'Set the path where the excel will be saved  
  3.      filePath = "C:\Test.xls" 
  4.  
  5.      'Create the Excel spreadsheet  
  6.      Set excelFile = CreateObject("Excel.Application") 
  7.      excelFile.Visible = true 
  8.      'Create the WorkBook 
  9.      Set curWorkBook = excelFile.WorkBooks.Add 
  10.      'Create the Sheet 
  11.      Set curSheet = curWorkBook.WorkSheets(1) 
  12.  
  13.      'Get the chart we want to export 
  14.      Set tableToExport = ActiveDocument.GetSheetObject("CH01") 
  15.      Set chartProperties = tableToExport.GetProperties 
  16.      tableToExport.CopyTableToClipboard true 
  17.  
  18.      'Get the caption 
  19.      chartCaption = tableToExport.GetCaption.Name.v 
  20.      'MsgBox chartCaption 
  21.  
  22.      'Set the first cell with the caption 
  23.      curSheet.Range("A1") = chartCaption 
  24.      'Paste the rest of the chart 
  25.      curSheet.Paste curSheet.Range("A2") 
  26.      excelFile.Visible = true 
  27.  
  28.      'Save the file and quit excel 
  29.      curWorkBook.SaveAs filePath 
  30.      curWorkBook.Close 
  31.      excelFile.Quit 
  32.  
  33.      'Cleanup 
  34.      Set curWorkBook = nothing 
  35.      Set excelFile = nothing 
  36. End Sub 

Change the properties (Image attached).

set the button properties (Image attached).

Add or Edit the script with the Object Id of the chart which you need to Print.

Click button to see the result.

It is a very very basic example for newbies I will come up with some new and complex samples once I have familiar with macros.

Attachments
Comments

Nice

0 Likes
desertmatt
New Contributor III

Dear Varun,

this is like a "push" solution - is there any way to "pull" data from qlikview objects into excel (without nprinting)?

thank you very much

Matt

0 Likes
MVP
MVP

Actually you can use COM Automation from Excel's VBA to pull data from QlikView objects.

0 Likes
desertmatt
New Contributor III

Thanks Petter,

I'm quite a newbie - could you please elaborate a little bit more on this?

How to pull a Qlikview chart (Straight table) into excel or even into an Access table?

MVP
MVP

Sure - I can do that. Can you please create a new question out of it since I think it is much more helpful for the community and goes beyond the original document topic posed here?

We can always create a link from this to the new question that you create.

0 Likes
desertmatt
New Contributor III
MVP
MVP

Beautiful ... I'll give you a working example.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎02-10-2014 07:20 AM
Updated by: