topic Re: Macro export to excel in QlikView

Macro export to excel

jelly_gvm — Thu, 07 Nov 2013 12:12:01 GMT

Hi All,

In my dashboard i have table box object,

Send this table box data into excel using macros with report name as Excel sheet name...

I need it urgent....

Any help really appreciate.

Thank you

Regards,

Jelly.

Re: Macro export to excel

Thu, 07 Nov 2013 12:21:15 GMT

you could modify the macro in this link to suit your requirements.

http://community.qlik.com/message/318990#318990

Re: Macro export to excel

jelly_gvm — Thu, 07 Nov 2013 12:25:23 GMT

Hi Anju,

I'm new to VB Script.......Could you please send me a Application for one object..

Regards,

Jelly

Re: Macro export to excel

fkeuroglian — Thu, 07 Nov 2013 12:25:53 GMT

Check this and try

Sub Test

ExcelAppend "h:\test.xlsx", "CH01"

End Sub

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID)

' Create an instance of Excel

SET objExcelApp = CREATEOBJECT("Excel.Application")

' Open workbook

WITH objExcelApp

.DefaultSaveFormat = xlWorkbookNormal

.DisplayAlerts = FALSE

.Workbooks.Open strExcelAppenFile

.DisplayFullScreen = FALSE

.Visible = FALSE

END WITH

' Set worksheet

SET objExcelSheet = objExcelApp.Worksheets(1)

' Set Excel used range

SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)

' Last used row in column A

intExcelLastRow = objExcelRange.Row

' Set object to append from

SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID)

' Loop all rows of the object except first header row

FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1

' Loop all columns of the object

FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1

' Get object data

SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn)

' Add that data to Excel cell

objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text

' Save and quit

objExcelSheet.SaveAs strExcelAppenFile

objExcelApp.Application.Quit

SET objExcelSheet = NOTHING

SET objExcelApp = NOTHING

END SUB

Re: Re: Macro export to excel

Thu, 07 Nov 2013 12:33:47 GMT

sub test

'Set the path where the excel will be saved

filePath = "C:\Test.xls"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set curWorkBook = excelFile.WorkBooks.Add

'Create the Sheet

'first chart object

Set curSheet = curWorkBook.WorkSheets(1)

'loop

chartArray = Array("CH06") ' give the name of your object instead of CH06

usedRows=0

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

curSheet.Cells(usedRows+1, 1)=chartCaption

i.CopyTableToClipboard true

curSheet.Cells(usedRows+3, 1).Select

curSheet.Paste

usedRows=curSheet.UsedRange.Rows.Count+3

'loop end

excelFile.Visible = true

'Save the file and quit excel

curWorkBook.SaveAs filePath

curWorkBook.Close

excelFile.Quit

'Cleanup

Set curWorkBook = nothing

Set excelFile = nothing

end sub

Re: Re: Macro export to excel

jelly_gvm — Thu, 07 Nov 2013 12:46:40 GMT

Hi Anju,

When ever we click on the button, the excel sheet should display with name as " ASIA REGION.xls"

Regards

Jelly.

Re: Macro export to excel

Thu, 07 Nov 2013 13:01:26 GMT

Hi,

in the macro above , instead of Test.xls..give the appropriate name.(line 3)

filePath = "C:\Test.xls"

Re: Macro export to excel

jelly_gvm — Fri, 08 Nov 2013 06:36:43 GMT

Thank you Anju, Its working fine.....

Re: Macro export to excel

Tue, 27 Oct 2015 07:42:20 GMT

Hi Guys,

I am using this same macro, but i wanted to do some modification like my excel file should be saved in name of Filename_vstartdate_venddate.xls, I have edited the macro, but it is not working. Could anyone help me on this.

Sub ExcelExpwCaption

'Set the path where the excel will be saved

SET v = ActiveDocument.Variables("vStartDate")

varDate = v.GetContent.STRING

SET v1 = ActiveDocument.Variables("vEndDate")

varDate1 = v1.GetContent.STRING

filePath = "H:\ExcelReport_"&varDate&"_"&varDate1&".xls"

'Create the Excel spreadsheet

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

'Create the WorkBook

Set WorkBook = excelFile.WorkBooks.Add

'Create the Sheet

Set Sheet = WorkBook.WorkSheets(1)

'Get the chart we want to export

Set tableToExport = ActiveDocument.GetSheetObject("TB01")

Set chartProperties = tableToExport.GetProperties

tableToExport.CopyTableToClipboard true

'Get the caption

chartCaption = tableToExport.GetCaption.Name.v

'MsgBox chartCaption

'Set the first cell with the caption

Sheet.Range("A1") = chartCaption

'Paste the rest of the chart

Sheet.Paste Sheet.Range("A2")

excelFile.Visible = true

' 'Save the file and quit excel

' WorkBook.SaveAs filePath

' WorkBook.Close

' excelFile.Quit

'Cleanup

Set WorkBook = nothing

Set excelFile = nothing

End Sub