topic Re: How to export to Excel include Title Caption? in QlikView

How to export to Excel include Title Caption?

senpradip007 — Thu, 06 Aug 2015 10:57:19 GMT

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.

Re: How to export to Excel include Title Caption?

m_woolf — Thu, 06 Aug 2015 13:48:56 GMT

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

Re: How to export to Excel include Title Caption?

senpradip007 — Mon, 10 Aug 2015 07:03:49 GMT

Hi,

Thanks for the prompt reply. But its not working.

Regards.

Re: How to export to Excel include Title Caption?

avinashelite — Mon, 10 Aug 2015 07:26:29 GMT

Re: How to export to Excel include Title Caption?

avinashelite — Mon, 10 Aug 2015 07:28:46 GMT

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

Re: How to export to Excel include Title Caption?

ecolomer — Mon, 10 Aug 2015 08:04:36 GMT

See this example:

Re: How to export to Excel include Title Caption?

senpradip007 — Mon, 10 Aug 2015 08:58:48 GMT

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.

Re: How to export to Excel include Title Caption?

ecolomer — Mon, 10 Aug 2015 09:02:07 GMT

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

Re: How to export to Excel include Title Caption?

ecolomer — Mon, 10 Aug 2015 09:04:28 GMT

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

Re: How to export to Excel include Title Caption?

senpradip007 — Mon, 10 Aug 2015 09:04:43 GMT

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.

Re: How to export to Excel include Title Caption?

senpradip007 — Mon, 10 Aug 2015 09:31:56 GMT

Thanks its working.

Can you please help me to passing the different object ids as the macro parameter ?

Re: How to export to Excel include Title Caption?

avinashelite — Mon, 10 Aug 2015 09:45:34 GMT

try with this :

http://www.qlikblog.at/558/qliktip-18-workaround-passing-parameters-qlikviewmacros/

Re: How to export to Excel include Title Caption?

senpradip007 — Wed, 12 Aug 2015 10:54:16 GMT

Reached the goal. Thanks to all.