Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
senpradip007
Specialist III
Specialist III

How to export to Excel include Title Caption?

Hi,

I am looking for a macro to export the data currently select in a straight table(not the column headers, just data) and send to a specified excel file on the next available row.  I do not wish to create a new excel file with this macro and I do not want to override what is already in the excel file.  I want to just add on to it.

Could someone help me out?

Thanks in advanced.

PS

1 Solution

Accepted Solutions
ecolomer
Master II
Master II

See this example:

View solution in original post

12 Replies
m_woolf
Master II
Master II

sub AppendDataToExcel  

  Const xlCellTypeLastCell = 11

  Set XLApp = CreateObject("Excel.Application")

  XLApp.Visible = True

  Set XLWorkbook = "C:\Temp\WorkbookName.xlsx")   ' Workbook should have the Header Row on Sheet1

  Set XLSheet = XLWorkbook.sheets("Sheet1")

  XLSheet.select

  Set objRange = XLSheet.UsedRange

  objRange.SpecialCells(xlCellTypeLastCell).Activate

  intLastRow = XLApp.ActiveCell.Row + 1

  XLSheet.Range("A" & intLastRow).Select

  ActiveDocument.GetSheetObject("CH178").CopyTableToClipboard false

  XLSheet.Paste ' -4163    'values

  XLWorkbook.Save

  XLWorkbook.Close

  XLApp.Quit

  set v = nothing

  set XLSheet = nothing

  set XLWorkbook = nothing

  set XLApp = nothing

end sub

senpradip007
Specialist III
Specialist III
Author

Hi,

Thanks for the prompt reply. But its not working.

Regards.

PS

avinashelite

hi

avinashelite

Hi ,

You have 3 way s to do it .

1.Go to settings >User Preference >Export > then check on the export with caption in chart option.

2. Macro

Sub ExcelExpwCaption

     'Set the path where the excel will be saved

     filePath = "E:\TestBB.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("LB02")

     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

3. NPrinting tool

ecolomer
Master II
Master II

See this example:

View solution in original post

senpradip007
Specialist III
Specialist III
Author

Thanks for your prompt reply. Tried with your app, but when I click on "Export Excel" prompting the VBA (macro) code instead of excel.

Please help.

ecolomer
Master II
Master II

Hi,

You need define directory in the macro, in the tirdh line

And you put the name of the table in the macro in line nº 16

ecolomer
Master II
Master II

And you need to select option in the macro "Access to System


p28.png

senpradip007
Specialist III
Specialist III
Author

Thanks for your prompt reply. Tried the first solution but data has been exported without the caption.

Tried with the second solution as well, but when I click on "Export Excel" prompting the VBA (macro) code instead of excel.