Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Thanks Miguel Angel but in this example copytexttoclipboard is not used.
CopyTableToClipboard already includes the headers.