CopyBitmapToClipboard in Macro copy the same object

[#1]

HI,

i wrote this function to create an XLS that print on more sheets different Line chart(bfeore to copy one line chart  i clear one specific filter).

Sub ExportExcelALL

  set XLApp = CreateObject("Excel.Application") ' Define Object

  XLApp.Visible = false 'Visible set as true

  set XLDoc = XLApp.Workbooks.Add 'Open new workbook

  set XLSheet11 = XLDoc.Worksheets.Add 'Select sheet where data should be pasted

  set table = ActiveDocument.GetSheetObject("CH170")

  table.CopyBitmapToClipboard'Copy data to Clipboard

     XLSheet11.Paste  XLSheet11.Range("A1") 'Paste data into cell

        XLsheet11.Cells.EntireRow.AutoFit

        XLsheet11.Cells.EntireColumn.AutoFit

        XLsheet11.Cells.Borders.LineStyle = 1

  XLsheet11.Cells.WrapText = false

  XLSheet11.Name="ACTIVITY"

ActiveDocument.Fields("ACTIVITY_NAME").clear

  set XLSheet10 = XLDoc.Worksheets.Add 'Select sheet where data should be pasted

  set table10 = ActiveDocument.GetSheetObject("CH169")

  table10.CopyBitmapToClipboard true 'Copy data to Clipboard

     XLSheet10.Paste  XLSheet10.Range("A1") 'Paste data into cell

        XLsheet10.Cells.EntireRow.AutoFit

        XLsheet10.Cells.EntireColumn.AutoFit

        XLsheet10.Cells.Borders.LineStyle = 1

  XLsheet10.Cells.WrapText = false

  XLSheet10.Name="INT EMC"

  ActiveDocument.Fields("INT_EMC_NAME").clear

  set XLSheet9 = XLDoc.Worksheets.Add 'Select sheet where data should be pasted

  set table9 = ActiveDocument.GetSheetObject("CH168")

  table9.CopyBitmapToClipboard true 'Copy data to Clipboard

     XLSheet9.Paste  XLSheet9.Range("A1") 'Paste data into cell

        XLsheet9.Cells.EntireRow.AutoFit

        XLsheet9.Cells.EntireColumn.AutoFit

        XLsheet9.Cells.Borders.LineStyle = 1

  XLsheet9.Cells.WrapText = false

  XLSheet9.Name="EMC"

  XLApp.Visible = true

  msgbox "Export Completed"

End Sub

but on all sheet the image is the same......

can someone help me?

many thanks for your attention

[syukyo_zhu]

hi, try samething like this.

'Create the Excel spreadsheet

      Set excelFile = CreateObject("Excel.Application")

      excelFile.Visible = true

      'Create the WorkBook

      Set curWorkBook = excelFile.WorkBooks.Add

Set i = ActiveDocument.GetSheetObject("CH221") 

chartCaption = replace(i.GetCaption.Name.v,"/"," ") 

     call AddExcelSheet(curWorkBook , chartCaption)

    Set curSheet = curWorkBook.WorkSheets(curWorkBook.Sheets.Count)

    curSheet.Cells(usedRows+1, 1)=chartCaption

      i.CopyTableToClipboard true

      curSheet.Cells(usedRows+3, 1).Select

      curSheet.Paste

Sub AddExcelSheet(xlDoc, strSheetName)

  xlDoc.Sheets.Add, xlDoc.Sheets(xlDoc.Sheets.Count)

  Set xlSheet  = xlDoc.Sheets(xlDoc.Sheets.Count)

  xlSheet.Name = left(strSheetName,31)

End Sub