[VBA] Found source of corruption, how do I remove /xl/printerSettings/printerSettings1.bin from file?

[RockandGrohl]

Hi all, have a very macro heavy and business critical worksheet that is corrupting multiple times daily since an office move. It seems to be centred around the PrinterSettings component, which makes sense seeing as all our print setup has changed due to the new office.


We work in 2010 and it wasn't being verbose, but after attempting to open and then repairing the file on my home 365 setup, it gives me the following:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Part: /xl/printerSettings/printerSettings1.bin part. (Print options)

If I rename the file from Price Panels 2021.xlsm to Price Panels 2021.zip, then go into the file and delete the PrinterSettings1-10.bin files, I can then open the sheet happily without needing to repair and this seems to solve the problem. But I fear that this is only temporary and the printer settings will return, so I'm asking is there a way to permanently remove these PrinterSettings files? Thank you.

 

[CephasOz]

A little bit of searching seems to suggest that very long strings (> 255) are the problem. Do you have any very long strings in, say, the page setup area? If so, maybe reducing them would solve your problem.

 

[RockandGrohl]

A little bit of searching seems to suggest that very long strings (> 255) are the problem. Do you have any very long strings in, say, the page setup area? If so, maybe reducing them would solve your problem.

Sorry, where is the Page Setup area?

 

[CephasOz]

Do you have any code that looks like:

        With .PageSetup
            .LeftHeader = vbNullString
            .CenterHeader = "My extremely long header that just goes on and on, never stopping, until it eventually gets so long that it exceeds 255 characters and starts causing a lot of problems that I really didn't want to have, but now corrupt my workbook and stress me out causing headaches, nervous tics, and dyspepsia"
            .RightHeader = vbNullString
            .LeftFooter = "&10&B&B" & Format(Date, "Short Date")
            .CenterFooter = vbNullString
            .RightFooter = "&10Page: &P of &N"
        End With

 

[RockandGrohl]

No, there is no Pagesetup code anywhere in the whole project unfortunately.

 

[CephasOz]

Take a look at the headers and footers that have been set for each worksheet.

 

[RockandGrohl]

Take a look at the headers and footers that have been set for each worksheet.

Can I be an absolute eejit and ask for some more in depth guidance on this :P This is a sheet I've inherited and I haven't ever set page properties or headers and footers before, thanks.

 

[CephasOz]

Of course. Run the following code on a copy of the workbook to clear all headers and footers:

Sub ClearHeadersAndFooters()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        With wks.PageSetup
            .LeftHeader = vbNullString
            .CenterHeader = vbNullString
            .RightHeader = vbNullString
            .LeftFooter = vbNullString
            .CenterFooter = vbNullString
            .RightFooter = vbNullString
        End With
    Next
End Sub

Then save and close the copy, and re-open it. Does the problem still exist?

And, for future reference, to manually change headers or footers for each worksheet:
1. Click on "Page Layout" in the ribbon.
2. In the "Page Setup" section, click the little arrow in a square that is in the bottom right of that section.
3. In the dialog box that comes up, click the "Header/Footer" tab.
4. Click the "Custom Header" button to see the headers, or the "Custom Footer" button to see the footers.

 

[RockandGrohl]

Of course. Run the following code on a copy of the workbook to clear all headers and footers:

Sub ClearHeadersAndFooters()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        With wks.PageSetup
            .LeftHeader = vbNullString
            .CenterHeader = vbNullString
            .RightHeader = vbNullString
            .LeftFooter = vbNullString
            .CenterFooter = vbNullString
            .RightFooter = vbNullString
        End With
    Next
End Sub

Then save and close the copy, and re-open it. Does the problem still exist?

And, for future reference, to manually change headers or footers for each worksheet:
1. Click on "Page Layout" in the ribbon.
2. In the "Page Setup" section, click the little arrow in a square that is in the bottom right of that section.
3. In the dialog box that comes up, click the "Header/Footer" tab.
4. Click the "Custom Header" button to see the headers, or the "Custom Footer" button to see the footers.

Thanks, I'll give this a shot next time I'm in the worksheet.

So this was never an issue before, these sheets are used practically all day, by multiple users, and it only seems to have cropped up since the office moved to a new location. Do you have any ideas what might be triggering the corruption? Cheers.

 

[RockandGrohl]

This sounds halfway hopeful.

We keep getting the following error when trying to open files-Removed Records: Object from /xl/printerSettings/printerSettings2.bin part (Print options)

We continually get errors when opening some Excel files. We all upgraded our computers to office 2010 but the guy creating our files went back to office 2007 at the recommendation of our tech people.

answers.microsoft.com