Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
copy clipboard failed in export to excel using macros.how to resolved this issue.
Hi,
Can you let us know
What are you trying to achieve here.
Regards
ASHFAQ
can you upload the qlikview file or macro?
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
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