topic Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview in QlikView

Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I am able to export a straight table or pivot table into excel by clicking a button by using attached macro code.

Now My requirement is I have a field called Inv_year. When I click a button,then automatically the value of Inv_year will be selected

and straight table data will go to excel and save as corresponding year.xls.

For exemple 2012.xls,2013.xls,2014.xls

sub test

'Set the path where the excel will be saved

filePath = "E:\Qllikview\BSC dashboard\BSC project-new\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("CH01") ' 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

Regards

Tripati

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

mohamed_ahid — Fri, 02 Dec 2016 13:56:35 GMT

put a sample file for your example please .

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Fri, 02 Dec 2016 14:21:50 GMT

Dear Mohamed,

Thanks for your time.

Please find the attachment.

My requirement is when I click on click here button year should be selected one by one and data for that year selected and exported into excel and stored like 2011.xls,2012.xls and so on.

Please help me .

Regards

Tripati

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Fri, 02 Dec 2016 17:00:52 GMT

Please see the attached file and hope this helps:

Note:Please make sure you update the path in the code to your local path.

Sub Export

DIM FieldName

on error resume next

SET Doc = ActiveDocument

'Assign Inv_year field to FieldName, because the report generates for each Inv_year

SET f = Doc.Variables("vInv_Year")

FieldName = f.GetContent.STRING

Doc.Fields(FieldName).Clear

SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)

For i=0 to Field.Count-1

Doc.Fields(FieldName).SELECT Field.Item(i).Text

Doc.GetApplication.WaitForIdle

Path = "C:\Temp\"&Field.Item(i).Text &".xls"

set obj = Doc.GetSheetObject("CH01")

obj.ExportBiff Path

End Sub

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Sat, 03 Dec 2016 00:43:46 GMT

Here is a second option without using variables: Hope this helps,,,

Sub Export

On error resume next

ActiveDocument.Fields("Inv_year").Clear

SET Field = ActiveDocument.Fields("Inv_year").GetPossibleValues(100000)

For i=0 to Field.Count-1

ActiveDocument.Fields("Inv_year").SELECT Field.Item(i).Text

ActiveDocument.GetApplication.WaitForIdle

Path = "C:\Temp\"&Field.Item(i).Text &".xls"

Set Obj = ActiveDocument.GetSheetObject("CH01")

Obj.ExportBiff Path

End Sub

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Sat, 03 Dec 2016 03:45:10 GMT

Dear Thirumala,

Thanks for your support.

Regards

Tripati

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Sat, 03 Dec 2016 04:18:18 GMT

You are welcome and I am glad that it worked for you.

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Mon, 05 Dec 2016 11:13:28 GMT

Dear Thirumala,

I have below requirement.

I have another field region,In the above report we have exported the report based on selection of year but I want

for each year region value will be selected and chart value send it excel.

and it should save as

2016IND.xls

2016USA.xls

2015IND.xls

2015USD.xls

I have attached qlikview file.Please check and do the needful.

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Mon, 05 Dec 2016 14:05:19 GMT

Use the below code and that will will generate the files based on Year and Region. Hope this helps....

Sub Export

on error resume next

SET Doc = ActiveDocument

'Assign Inv_year field to FieldName, because the report generates for each Inv_year

SET f = Doc.Variables("vInv_Year")

SET f1 = Doc.Variables("vRegion")

FieldName = f.GetContent.STRING

FieldName1 = f1.GetContent.STRING

Doc.Fields(FieldName).Clear

Doc.Fields(FieldName1).Clear

SET Field = Doc.Fields(FieldName).GetPossibleValues(100000)

For i=0 to Field.Count-1

Doc.Fields(FieldName).SELECT Field.Item(i).Text

SET Field1= Doc.Fields(FieldName1).GetPossibleValues(100000)

For j=0 to Field1.Count-1

Doc.Fields(FieldName1).SELECT Field1.Item(j).Text

Doc.GetApplication.WaitForIdle

Path = "E:\Qllikview\BSC dashboard\" &Field.Item(i).Text &"_" &Field1.Item(j).Text &".xls"

set obj = Doc.GetSheetObject("CH01")

obj.ExportBiff Path

End Sub

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Mon, 05 Dec 2016 14:29:23 GMT

Dear Thirumala,

Once again thanks alot.

Regards

Tripati

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Mon, 05 Dec 2016 15:08:09 GMT

You are welcome....

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Wed, 08 Feb 2017 05:56:29 GMT

Dear Thirumala,

I have one requirement , By using above code I am getting data into different excel,

Now i want all data in single excel different sheet.

Hope you understand my requirement.

Regards

Tripati

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Thu, 09 Feb 2017 16:29:28 GMT

Please follow the below link and you will get the solution. Please make sure you close the thread appropriately if you got the answer.

Macro to export multiple tables in to single excel

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Fri, 10 Feb 2017 03:57:53 GMT

My requirement is when I click a button the straight table will fetched and stored in a different sheet of single excel.

I have raised a new thread-1209426 for it .

Please check.

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

tripatirao — Fri, 10 Feb 2017 12:39:34 GMT

Dear Thitumala,

I have attached the qvw file with macro.

This code working pefectly for me.

Regards

Tripati

Re: Facing problem while implementing forloop in macro to export chart data into excel in qlikview

trdandamudi — Fri, 10 Feb 2017 13:33:51 GMT

Glad to hear that you are able to work it out...