none
Excel 2010 - 'The image part with relationship ID rId1 was not found in the file.' When copying sheets of a workbook into another workbook RRS feed

  • Question

  • Hi,

    I have a source excel workbook (XLSM format) that has sheets containing images. These sheets are programatically copied into another(target) workbook (XLSM) using VB.NET late binding. After all the copy is done i save the workbook and launch it. In the opened excel workbook I see the error message  'The image part with relationship ID rId1 was not found in the file' at all places where the images are placed.
    All the operations are done in the client machine no server side code available.

    Interestingly this issue doesn't occur in Excel 2013 and it displays the images properly issue is observed only in 2010 and 2007. Is this a know bug in Excel 2010 and 2007 if yes can any one provide me the official link to the ticket so that i can track the issue and get the Hot fix once it is available. Here is the sample code.

    Dim SourceExcelWorkbook As Excel.Workbook = Nothing
            Dim TargetExcelWorkbook As Excel.Workbook = Nothing
            Dim TargetExcelSheets As Excel.Sheets = Nothing
            Dim SourceExcelSheets As Excel.Sheets = Nothing
            Dim CopyWorkSheet As Excel.Worksheet = Nothing

            Dim XLApp As New Excel.Application
            XLApp.Visible = False
            XLApp.DisplayAlerts = False
            XLApp.ScreenUpdating = False

            Dim pobjExcelWorkbooks As Excel.Workbooks = XLApp.Workbooks

            SourceExcelWorkbook = pobjExcelWorkbooks.Open("source file path")
            TargetExcelWorkbook = pobjExcelWorkbooks.Open("target file path")

            TargetExcelSheets = TargetExcelWorkbook.Worksheets
            SourceExcelSheets = SourceExcelWorkbook.Worksheets

            Dim OriginalSheetCount As Integer = TargetExcelSheets.Count
            Dim SheetCount As Integer = OriginalSheetCount
            Dim SheetsToBeCopiedCount As Integer = SourceExcelSheets.Count

            While SheetsToBeCopiedCount > 0
                Dim lobjAfterSheet As Object = TargetExcelSheets.Item(SheetCount)
                CopyWorkSheet = SourceExcelSheets.Item(1)
                CopyWorkSheet.Move(After:=lobjAfterSheet)
                SheetCount = SheetCount + 1
                TargetExcelWorkbook.Save()
                SheetsToBeCopiedCount = SheetsToBeCopiedCount - 1
            End While

            TargetExcelWorkbook.Save()


    Thanks

    Guruteja



    • Edited by jGuruteja Thursday, September 10, 2015 5:26 AM added sample code
    Wednesday, September 9, 2015 6:53 AM

All replies

  • Anybody ??
    Thursday, September 10, 2015 5:26 AM
  • >>>Interestingly this issue doesn't occur in Excel 2013 and it displays the images properly issue is observed only in 2010 and 2007. Is this a know bug in Excel 2010 and 2007 if yes can any one provide me the official link to the ticket so that i can track the issue and get the Hot fix once it is available.

    I have used your sample codes and reproduces this issue, so I suggest you could use Copy method instead of Move method.
    You could change your code to refer to like below:

    While SheetsToBeCopiedCount > 0
          Dim lobjAfterSheet As Object = TargetExcelSheets.Item(SheetCount)
          CopyWorkSheet = SourceExcelSheets.Item(1)
          CopyWorkSheet.Copy(After:=lobjAfterSheet)
          SheetCount = SheetCount + 1
          TargetExcelWorkbook.Save()
          SheetsToBeCopiedCount = SheetsToBeCopiedCount - 1
    End While

    For more information, click here to refer about Worksheet.Copy Method (Excel)


    Friday, September 11, 2015 9:14 AM
  • Hi,

    I cant use copy method as source excel might contain any links to other workbooks which might be broken during copy and this i checked and it doesn't work. Any other suggestions ?

    Friday, September 11, 2015 4:35 PM
  • Hi jGuruteja,

    I made a test with your code, but I did not get the same issue like yours. With your code, you move all of the sheets in source file, it will generate error because you need to store at least one worksheet in a workbook. I suggest you try the code below:

        Private Sub ExcelMove_Click(sender As Object, e As EventArgs) Handles ExcelMove.Click
            Dim SourceExcelWorkbook As Excel.Workbook = Nothing
            Dim TargetExcelWorkbook As Excel.Workbook = Nothing
            Dim TargetExcelSheets As Excel.Sheets = Nothing
            Dim SourceExcelSheets As Excel.Sheets = Nothing
            Dim CopyWorkSheet As Excel.Worksheet = Nothing
    
            Dim XLApp As Excel.Application
            XLApp = CreateObject("Excel.Application", "")
            XLApp.Visible = True
            XLApp.DisplayAlerts = True
            XLApp.ScreenUpdating = False
    
            Dim pobjExcelWorkbooks As Excel.Workbooks = XLApp.Workbooks
    
            SourceExcelWorkbook = pobjExcelWorkbooks.Open("D:\Book1.xlsm")
            TargetExcelWorkbook = pobjExcelWorkbooks.Open("D:\Book2.xlsm")
    
            TargetExcelSheets = TargetExcelWorkbook.Worksheets
            SourceExcelSheets = SourceExcelWorkbook.Worksheets
    
            Dim OriginalSheetCount As Integer = TargetExcelSheets.Count
            Dim SheetCount As Integer = OriginalSheetCount
            Dim SheetsToBeCopiedCount As Integer = SourceExcelSheets.Count
    
            While SheetsToBeCopiedCount > 1
                'Dim lobjAfterSheet As Object = TargetExcelSheets.Item(1)
                Dim lobjAfterSheet As Object = TargetExcelSheets.Item(SheetCount)
                CopyWorkSheet = SourceExcelSheets.Item(1)
                CopyWorkSheet.Move(After:=lobjAfterSheet)
                SheetCount = SheetCount + 1
                TargetExcelWorkbook.Save()
                SheetsToBeCopiedCount = SheetsToBeCopiedCount - 1
            End While
            SourceExcelWorkbook.Save()
            TargetExcelWorkbook.Save()
            SourceExcelWorkbook.Close()
            TargetExcelWorkbook.Close()
            pobjExcelWorkbooks.Close()
        End Sub
    

    For your error, I suggest you manually move there sheets and then open it to check whether you still get this error.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, September 29, 2015 8:35 AM