Macro - Copy Text To Clipboard Header Problem - Qlikview

[Anakin_Qlikdeveloper]

Hi,

I have an Excel file that contains formulas. I export some objects (Pivot Table) from qlikview to excel by using macro and I have to use CopyTextToClipboard for merged cells and cells with formula to use SUMIFS in excel file. Everything works fine but when the macro is complete, the export table does not contain the headers.

PS: Using CopyTableToClipboard True/False  is useless for this problem

My Macro;

sub Export2

Path = "Myfilepath\"
FileName = "Myfilename"
strSaveFile = Path & FileName
'==============================================================
set oXL=CreateObject("Excel.Application")
Set oXLDoc = oXL.Workbooks.Open("Myfilepath.xlsx")
oXL.visible=True

aSheetObj=Array("CH1336","CH1335","CH1337","CH1334")

'==============================================================

Set oSH = oXL.ActiveSheet

num_rows = oSH.UsedRange.Rows.Count

oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(0))
obj.CopyTextToClipboard
oSH.Paste
sCaption=obj.GetCaptionl.Name.v
Set obj=Nothing

oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True

oSH.Cells.Select
oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select
oSH.Name=left(sCaption,30)

oSH.Range("K1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(1))
obj.CopyTextToClipboard
oSH.Paste
sCaption=obj.GetCaption.Name.v
Set obj=Nothing

oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True

oSH.Cells.Select
oXL.Selection.Columns.AutoFit

oSH.Range("K1").Select
oSH.Name=left(sCaption,30)

oXL.Sheets.Add
oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )

Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(2))
obj.CopyTextToClipboard
oSH.Paste
sCaption=obj.GetCaption.Name.v
Set obj=Nothing

oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True

oSH.Cells.Select
oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select
oSH.Name=left(sCaption,30)

Set oSH=Nothing

oXL.Sheets.Add
oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )

Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(3))
obj.CopyTextToClipboard
oSH.Paste
sCaption=obj.GetCaption.Name.v
Set obj=Nothing

oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True

oSH.Cells.Select
oXL.Selection.Columns.AutoFit

oSH.Range("A1").Select
oSH.Name=left(sCaption,30)

Set oSH=Nothing

'==============================================================

oSH.Range("A1").Select
oXL.Sheets("Sheet2").Delete
oXL.Sheets("Sheet3").Delete
oSH.Name = "Data"

oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

set oSH = Nothing
set oXL=Nothing
end sub

[Miguel_Angel_Baeyens]

This was working, back in time:

https://community.qlik.com/t5/New-to-QlikView/Export-to-Excel/td-p/321150

The only thing I can remember from that is that you needed to activate sheets in QlikView and workbooks in Excel prior to the copy/paste.

[Anakin_Qlikdeveloper]

Thanks Miguel Angel but in this example copytexttoclipboard is not used. 

CopyTableToClipboard already includes the headers.