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

[tripatirao]

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

Next

'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

[mohamed_ahid]

hi

put a sample file for your example  please .

[tripatirao]

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

[trdandamudi]

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

      Next

End Sub

[trdandamudi]

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

      Next

End Sub

[tripatirao]

Dear Thirumala,

Thanks for your support.

Regards

Tripati

[trdandamudi]

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

[tripatirao]

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.

[trdandamudi · Accepted solution]

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

          Next

      Next

End Sub

[tripatirao]

Dear Thirumala,

Once again thanks alot.

Regards

Tripati