copy clipboard failed

[Anonymous]

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

[ashfaq_haseeb]

Hi,

Can you let us know

What are you trying to achieve here.

Regards

ASHFAQ

[Siva_Sankar]

can you upload the qlikview file or macro?

[Anonymous]

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 

Next

airSheet.UsedRange.EntireColumn.AutoFit

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

'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

'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")

Next

airSheetdel.UsedRange.EntireColumn.AutoFit

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")

Next

airSheetdel1.UsedRange.EntireColumn.AutoFit

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")

Next

airSheetdel2.UsedRange.EntireColumn.AutoFit

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")

Next

airSheetdel3.UsedRange.EntireColumn.AutoFit

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")

Next

airSheetdel4.UsedRange.EntireColumn.AutoFit

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")

Next

airSheetdel5.UsedRange.EntireColumn.AutoFit

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]

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 ...

next

I have noticed that following statements are missing which often avoid problems:

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