Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
CopyBitmapToClipboard is not working. we run macro scheduled automatically.
Hi
Its very hard to help with a question like this. If you would like to have useful responses, provide more detail:
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
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.