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

[trdandamudi]

You are welcome....

[tripatirao]

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

[trdandamudi]

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

[tripatirao]

Hi

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.

[tripatirao]

Dear Thitumala,

I have attached the qvw file with macro.

This code working pefectly for me.

Regards

Tripati

[trdandamudi]

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