0

I have a macro to copy data and a picture from one workbook (A) to another workbook (B). Workbook B is in Excel 2010. The code works fine if workbook A is an earlier version of Excel such as 97-2003. But if workbook A is also in Excel 2010 format, the code seems to work but upon trying to save the file I get an error ("Errors were detected while saving 'C:...'. Microsoft Excel may be able to save the file by removing or repairing some features...").

The key part of the code (after opening workbook A) is:

For Each s In workbookA.Sheets
    If s.Pictures.Count > 0 Then
        For Each pic In s.Shapes
            If Left(pic.Name, 3) = "Pic" Then
                pic.Copy
                workbookB.Activate
                Worksheets("Sheet1").Range("A1").Select
                ActiveSheet.Paste
                GoTo gotPicture:
            End If
        Next pic
    End If
Next s
gotPicture:

workbookA.Close

The error is resolved by deleting the picture. Or by saving and closing Workbook B prior to closing Workbook A. Neither of those is a great solution. From my research it sounds like this is an error within Excel 2010 where you can't copy pictures between spreadsheets generally rather than a VBA issue. There might be a patch, but corporate policies and the number of users who will need to run the macro make that a bad option for me.

Is there a good way to get around this? Some way to copy a picture between Excel 2010 spreadsheets that doesn't cause this problem? Thanks.

ELW
  • 380
  • 2
  • 19
  • I'd work on getting rid of `Select` and `Activate`... http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Chrismas007 Dec 16 '16 at 20:47
  • That's good advice generally, but I don't believe it would fix this specific issue, would it? – ELW Dec 16 '16 at 22:34
  • you are correct which is why I didn't offer it as a full answer but rather a comment. – Chrismas007 Dec 16 '16 at 22:36

1 Answers1

0

Ran into this same issue. Got around it by keeping my source documents open until my destination workbook was saved (which, as you point out, isn't great). Hotfix patch from Microsoft: https://support.microsoft.com/en-us/help/2597034/you-cannot-save-a-workbook-that-contains-an-image-that-was-copied-from

keggy
  • 1