CopyBitmapToClipboard is not working in macro

[Anonymous]

Hi

CopyBitmapToClipboard is not working. we run macro scheduled automatically.

[jonathandienst]

Hi

Its very hard to help with a question like this. If you would like to have useful responses, provide more detail:

• Best option, a sample of your document demonstrating the problem
• The code of the module that is failing
• What error message are you getting

With respect, remember that people responding on this site are doing this voluntarily out of their own time. If you ask a terse question, expect somewhat terse responses.

Regards

Jonathan

[Anonymous]

Function ExportExcel1()

'ActiveDocument.Reload()

Dim vSheet

Dim sSheet

Dim oSheet

filePath = "E:\Financial View of Chennai Branch"&(day(now())-1)&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

' MailReport

End Function

please see the macro.