Macro export to excel

[jelly_gvm]

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.

[#2]

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

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

[jelly_gvm]

Hi Anju,

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

Regards,

Jelly

[fkeuroglian]

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 

      NEXT 

   NEXT 

   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppenFile 

   objExcelApp.Application.Quit 

   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 

END SUB

[#5 · Accepted solution]

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

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

[jelly_gvm]

Hi Anju,

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

Regards

Jelly.

[#7]

Hi,

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

    filePath = "C:\Test.xls"

[jelly_gvm]

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

[#9]

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