Macro - Copy Text To Clipboard Header Problem - Qlikview

Anakin_Qlikdeveloper — Sat, 16 Nov 2024 20:21:28 GMT

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

Re: Macro - Copy Text To Clipboard Header Problem - Qlikview

Miguel_Angel_Baeyens — Tue, 30 Jul 2019 09:18:59 GMT

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.

Re: Macro - Copy Text To Clipboard Header Problem - Qlikview

Anakin_Qlikdeveloper — Tue, 30 Jul 2019 09:54:50 GMT

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

CopyTableToClipboard already includes the headers.

topic Macro - Copy Text To Clipboard Header Problem - Qlikview in QlikView

Macro - Copy Text To Clipboard Header Problem - Qlikview

Re: Macro - Copy Text To Clipboard Header Problem - Qlikview

Re: Macro - Copy Text To Clipboard Header Problem - Qlikview