Re: copy clipboard failed - Qlik Community

Anonymous · ‎2014-08-19

copy clipboard failed in export to excel using macros.how to resolved this issue.

ashfaq_haseeb · ‎2014-08-19

Hi,

Can you let us know

What are you trying to achieve here.

Regards

ASHFAQ

Siva_Sankar · ‎2014-08-19

can you upload the qlikview file or macro?

Anonymous · ‎2014-08-19

Function ExportExcel1()

ActiveDocument.Reload()

Dim vSheet

Dim sSheet

Dim oSheet

filePath = "E:\Financial View of Chennai Branch"&day(now())&monthname(month(now))&RIGHT(YEAR(now()),2)&".xlsx"

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = TRUE

Set XLDoc = XLApp.Workbooks.Add

vSheet = ""

'vSheet = Array("Sheet1")

vSheet = "Sheet1"

Set airSheet = XLDoc.Sheets(vSheet)

'Set airSheet1 = XLDoc.Sheets(vSheet(1))

airSheet.Name = "Financial View"

chartArray = Array("CH03","CH07","CH11","CH27","CH29","CH35")

usedRows=0

usedRows1=0

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

SELECT CASE chart

CASE "CH03"

chartCaption = i.GetCaption.Name.v

'chartCaption.

airSheet.Cells(usedRows+1, 2)=chartCaption

airSheet.Cells(usedRows+1, 2).Interior.ColorIndex=51

airSheet.Cells(usedRows+1, 2).Font.SIZE=11

airSheet.Cells(usedRows+1, 2).Font.ColorIndex=27

i.CopyTableToClipboard true

airSheet.Cells(usedRows+2, 1).Select

airSheet.Paste

usedRows=airSheet.UsedRange.Rows.Count+3

CASE "CH07"

usedRows=0

'airSheet.Cells(usedRows+2, 10)=chartCaption

chartCaption = i.GetCaption.Name.v

airSheet.Cells(usedRows+1, 9)=chartCaption

airSheet.Cells(usedRows+1, 9).Interior.ColorIndex=51

airSheet.Cells(usedRows+1, 9).Font.SIZE=11

airSheet.Cells(usedRows+1, 9).Font.ColorIndex=27

i.CopyTableToClipboard true

airSheet.Cells(usedRows+2, 8).Select

airSheet.Paste

usedRows=airSheet.UsedRange.Rows.Count+3

CASE "CH11"

'airSheet.Cells(usedRows+1, 1)=chartCaption

chartCaption = i.GetCaption.Name.v

'chartCaption.Font.Size = 20

airSheet.Cells(usedRows+1, 2)=chartCaption

airSheet.Cells(usedRows+1, 2).Interior.ColorIndex=51

airSheet.Cells(usedRows+1, 2).Font.SIZE=11

airSheet.Cells(usedRows+1, 2).Font.ColorIndex=27

i.CopyBitmapToClipboard

airSheet.Cells(usedRows+2, 1).Select

airSheet.paste

usedRows=airSheet.UsedRange.Rows.Count+3

CASE "CH35"

usedRows=28

'airSheet.Cells(usedRows+1, 9)=chartCaption

chartCaption = i.GetCaption.Name.v

airSheet.Cells(usedRows+1, 2)=chartCaption

airSheet.Cells(usedRows+1, 2).Interior.ColorIndex=51

airSheet.Cells(usedRows+1, 2).Font.SIZE=11

airSheet.Cells(usedRows+1, 2).Font.ColorIndex=27

i.CopyBitmapToClipboard

airSheet.Cells(usedRows+2, 1).Select

airSheet.paste

usedRows=airSheet.UsedRange.Rows.Count+3

CASE "CH27"

usedRows=14

'airSheet.Cells(usedRows+1, 9)=chartCaption

chartCaption = i.GetCaption.Name.v

airSheet.Cells(usedRows+1, 9)=chartCaption

airSheet.Cells(usedRows+1, 9).Interior.ColorIndex=51

airSheet.Cells(usedRows+1, 9).Font.SIZE=11

airSheet.Cells(usedRows+1, 9).Font.ColorIndex=27

i.CopyBitmapToClipboard

airSheet.Cells(usedRows+2, 8).Select

airSheet.paste

usedRows=airSheet.UsedRange.Rows.Count+3

case else

END SELECT

airSheet.Columns("B").ColumnWidth=45

airSheet.Columns("C").ColumnWidth=11

airSheet.Columns("C").WrapText=true

airSheet.Columns("D").ColumnWidth=11

airSheet.Columns("D").WrapText=true

airSheet.Columns("E").ColumnWidth=11

airSheet.Columns("E").WrapText=true

airSheet.Columns("F").ColumnWidth=11

airSheet.Columns("F").WrapText=true

airSheet.Columns("I").ColumnWidth=40

airSheet.UsedRange.EntireRow.AutoFit

'airSheet.Range("A2").Select

'oApp.ActiveWindow.FreezePanes = True

'XLApp.ActiveWindow.FreezePanes = True

'XLApp.Application.ScreenUpdating = True

airSheet.Cells(2, 2).Select

'XLApp.ActiveWindow.FreezePanes = True

'

airdelSheet = ""

airdelSheet = "Sheet2"

Set airSheetdel = XLDoc.Sheets(airdelSheet)

airSheetdel.Name = "Collection Statement"

chartArray = Array("CH29")

usedRows=1

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

airSheetdel.Range("C1")=chartCaption

airSheetdel.Range("A1:E1").HorizontalAlignment = -4108

airSheetdel.Range("A1:E1").MergeCells= True

' airSheetdel.Range("A1").WrapText= false

airSheetdel.Range("A1:E1").Interior.ColorIndex=51

airSheetdel.Range("A1:E1").Font.SIZE=11

airSheetdel.Range("A1:E1").Font.ColorIndex = 27

i.CopyTableToClipboard true

airSheetdel.select

airSheetdel.Paste airSheetdel.Range("A2")

airSheetdel.Columns("A").ColumnWidth=30

airSheetdel.Columns("B").ColumnWidth=16

airSheetdel.Columns("C").ColumnWidth=20

airSheetdel.Columns("D").ColumnWidth=20

airSheetdel.Columns("E").ColumnWidth=75

airSheetdel.UsedRange.EntireRow.AutoFit

'---------------Payment sheet------------------------------

airdelSheet1 = ""

airdelSheet1 = "Sheet3"

Set airSheetdel1 = XLDoc.Sheets(airdelSheet1)

airSheetdel1.Name = "Payment Statement"

chartArray = Array("CH26")

usedRows=1

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

airSheetdel1.Range("C1")=chartCaption

airSheetdel1.Range("A1:E1").MergeCells= True

airSheetdel1.Range("A1:E1").HorizontalAlignment = -4108

' airSheetdel.Range("A1").WrapText= false

airSheetdel1.Range("A1:E1").Interior.ColorIndex=51

airSheetdel1.Range("A1:E1").Font.SIZE=11

airSheetdel1.Range("A1:E1").Font.ColorIndex = 27

i.CopyTableToClipboard true

airSheetdel1.select

airSheetdel1.Paste airSheetdel1.Range("A2")

airSheetdel1.Columns("A").ColumnWidth=30

airSheetdel1.Columns("B").ColumnWidth=16

airSheetdel1.Columns("C").ColumnWidth=20

airSheetdel1.Columns("D").ColumnWidth=20

airSheetdel1.Columns("E").ColumnWidth=75

airSheetdel1.UsedRange.EntireRow.AutoFit

'---------------IOU & Staff Loan sheet------------------------------

airdelSheet2 = ""

airdelSheet2 = "Sheet4"

Set airSheetdel2 = XLDoc.Sheets(airdelSheet2)

airSheetdel2.Name = "IOU Outstanding Statement"

chartArray = Array("CH28")

usedRows=1

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

airSheetdel2.Range("C1")=chartCaption

airSheetdel2.Range("A1:F1").MergeCells= True

airSheetdel2.Range("A1:F1").HorizontalAlignment = -4108

' airSheetdel.Range("A1").WrapText= false

airSheetdel2.Range("A1:F1").Interior.ColorIndex=51

airSheetdel2.Range("A1:F1").Font.SIZE=11

airSheetdel2.Range("A1:F1").Font.ColorIndex = 27

i.CopyTableToClipboard true

airSheetdel2.select

airSheetdel2.Paste airSheetdel2.Range("A2")

airSheetdel2.Columns("A").ColumnWidth=20

airSheetdel2.Columns("B").ColumnWidth=20

airSheetdel2.Columns("C").ColumnWidth=25

airSheetdel2.Columns("D").ColumnWidth=20

airSheetdel2.Columns("E").ColumnWidth=20

airSheetdel2.Columns("F").ColumnWidth=25

airSheetdel2.UsedRange.EntireRow.AutoFit

'---------------OutStanding sheet------------------------------

airdelSheet3 = ""

airdelSheet3 = "Sheet5"

Set airSheetdel3 = XLDoc.Sheets(airdelSheet3)

airSheetdel3.Name = "Debtors Statement"

chartArray = Array("CH10")

usedRows=1

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

airSheetdel3.Range("C1")=chartCaption

airSheetdel3.Range("A1:N1").HorizontalAlignment = -4108

airSheetdel3.Range("A1:N1").MergeCells= True

' airSheetdel.Range("A1").WrapText= false

airSheetdel3.Range("A1:N1").Interior.ColorIndex=51

airSheetdel3.Range("A1:N1").Font.SIZE=11

airSheetdel3.Range("A1:N1").Font.ColorIndex = 27

i.CopyTableToClipboard true

airSheetdel3.select

airSheetdel3.Paste airSheetdel3.Range("A2")

airSheetdel3.Columns("A").ColumnWidth=10

airSheetdel3.Columns("B").ColumnWidth=45

airSheetdel3.Columns("C").ColumnWidth=13

airSheetdel3.Columns("C").WrapText= true

airSheetdel3.Columns("D").ColumnWidth=10

airSheetdel3.Columns("E").ColumnWidth=10

airSheetdel3.Columns("F").ColumnWidth=10

airSheetdel3.Columns("F").WrapText= true

airSheetdel3.Columns("G").ColumnWidth=10

airSheetdel3.Columns("H").ColumnWidth=10

airSheetdel3.Columns("H").WrapText= true

airSheetdel3.Columns("I").ColumnWidth=10

airSheetdel3.Columns("J").ColumnWidth=10

airSheetdel3.Columns("J").WrapText= true

airSheetdel3.Columns("K").ColumnWidth=10

airSheetdel3.Columns("L").ColumnWidth=10

airSheetdel3.Columns("L").WrapText= true

airSheetdel3.Columns("M").ColumnWidth=10

airSheetdel3.Columns("M").WrapText= true

airSheetdel3.Columns("N").ColumnWidth=5

airSheetdel3.Columns("N").WrapText= true

airSheetdel3.UsedRange.EntireRow.AutoFit

'---------------Creditot sheet------------------------------

airdelSheet4 = ""

airdelSheet4 = "Sheet6"

Set airSheetdel4 = XLDoc.Sheets(airdelSheet4)

airSheetdel4.Name = "Creditor Statement"

chartArray = Array("CH23")

usedRows=1

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

airSheetdel4.Range("C1")=chartCaption

airSheetdel4.Range("A1:I1").HorizontalAlignment = -4108

airSheetdel4.Range("A1:I1").MergeCells= True

' airSheetdel.Range("A1").WrapText= false

airSheetdel4.Range("A1:I1").Interior.ColorIndex=51

airSheetdel4.Range("A1:I1").Font.SIZE=11

airSheetdel4.Range("A1:I1").Font.ColorIndex = 27

i.CopyTableToClipboard true

airSheetdel4.select

airSheetdel4.Paste airSheetdel4.Range("A2")

airSheetdel4.Columns("A").ColumnWidth=10

airSheetdel4.Columns("B").ColumnWidth=45

airSheetdel4.Columns("C").ColumnWidth=10

airSheetdel4.Columns("D").ColumnWidth=10

airSheetdel4.Columns("E").ColumnWidth=10

airSheetdel4.Columns("F").ColumnWidth=10

airSheetdel4.Columns("G").ColumnWidth=10

airSheetdel4.Columns("G").WrapText= true

airSheetdel4.Columns("H").ColumnWidth=10

airSheetdel4.Columns("H").WrapText= true

airSheetdel4.Columns("I").ColumnWidth=10

airSheetdel4.Columns("I").WrapText= true

airSheetdel4.UsedRange.EntireRow.AutoFit

'---------------CAF Status sheet------------------------------

airdelSheet5 = ""

airdelSheet5 = "Sheet7"

Set airSheetdel5 = XLDoc.Sheets(airdelSheet5)

airSheetdel5.Name = "CAF Status Statement"

chartArray = Array("CH38")

usedRows=1

For Each chart In chartArray

Set i = ActiveDocument.GetSheetObject(chart)

chartCaption = i.GetCaption.Name.v

airSheetdel5.Range("B1")=chartCaption

airSheetdel5.Range("A1:D1").HorizontalAlignment = -4108

airSheetdel5.Range("A1:D1").MergeCells= True

' airSheetdel.Range("A1").WrapText= false

airSheetdel5.Range("A1:D1").Interior.ColorIndex=51

airSheetdel5.Range("A1:D1").Font.SIZE=11

airSheetdel5.Range("A1:D1").Font.ColorIndex = 27

i.CopyTableToClipboard true

airSheetdel5.select

airSheetdel5.Paste airSheetdel5.Range("A2")

airSheetdel5.Columns("A").ColumnWidth=12

airSheetdel5.Columns("A").WrapText= true

airSheetdel5.Columns("B").ColumnWidth=45

airSheetdel5.Columns("B").WrapText= true

airSheetdel5.Columns("C").ColumnWidth=10

airSheetdel5.Columns("D").ColumnWidth=10

airSheetdel4.UsedRange.EntireRow.AutoFit

XLDoc.Sheets(1).Select

XLDoc.SaveAs filePath

XLDoc.Close

XLApp.Quit

Set XLDoc = Nothing

Set XLApp = Nothing

End Function

marcus_sommer · ‎2014-08-19

It's quite difficult to say what could be wrong by such a large routine. I suggest that you used more loops through arrays to simplify your code especially for the whole format-statements. I use for a similar request several xls-tables with, paths, files, sheets, objects, objecttyps, selections, target-cells, formattings and so on which I show in gui as tableboxes and which I then read per loops - here an example:

Set TableOne = qvDoc.GetSheetObject("TB01")

ColumnOne = TableOne.GetColumnCount

RowOne = TableOne.GetRowCount

Set CellMatrixOne = TableOne.GetCells2(0,0,ColumnOne,RowOne)

for iReport = 1 to RowOne - 1

Set objExcelDoc = objExcelApp.Workbooks.Add

FullPath = CellMatrixOne(iReport)(0).Text & CellMatrixOne(iReport)(1).Text & "_" & ReportDate & ".xls"

objExcelDoc.Application.ActiveWorkbook.SaveAs FullPath, , ""

'do more ...

qvDoc.GetApplication.WaitForIdle 'wait until all qv calculations are finsih

XLDoc.sheets(YourSheet).Activate 'activate the sheet before using

Sometimes the clipboard hanged somehow - for this you could it clear before you filled it with own content:

Set WshShell = CreateObject("WScript.Shell")

WshShell.Run "cmd.exe /c echo. >NUL | clip", 0, True

I hope this is helpful for you.

- Marcus