Excel error printersettings1.bin

[Martijn79]

All,

I have a problem which many people seem to have. This basically destroys my spreadsheet and I can't find a solution to avoid the problem from occuring. I have tried to find a solution on Internet already, but unfortunately was not able to find one. Can someone help me with to solve this by providing the reason and what I should do or not do to prevent the error from occuring. The spreadsheet (Excel 2016) contains VBA/macros and therefore is saved as *.xlsm.

The error itself is "Removed Part: /xl/printerSettings/printerSettings1.bin part. (Print options)" which renders the sheet useless.

Your reply is greatly appreciated!

Regards,
Martin

[LJMetzger]

Hi Martin,

My speculation is that there is something in your macros that is causing a problem.

I had a similar problem recently that was caused by a Macro that used the following code excerpt:

Please Login or Register  to view this content.

The length of the 'Formula1' string (sDataValidatationString) MUST be 255 characters or less. When the string was longer than 255 characters, the file worked normally and I could save the file fine. After I opened the flie again, I would get the dreaded file was corrupt message similar (but not the same) as yours. It took me two weeks of detective work to determine what my problem was. The fix was to have the list on the Spreadsheet and have Formula1 use a Range (e.g. 'sFormula1 := "='Sheet X'!$E$3:$E$5" ).

My suggestions:
a. Make a MASTER .xlsm copy of your file that contains no Macro code modules.
b. Make a copy of the MASTER .xlsm file
c. Introduce Macro Modules and run their code. Save the file and exit Excel.
d. Open the file, and if there is no corruption, this is your New Master file.
e. Repeat until the problem is found.

I hope this helps.

Lewis

[xlnitwit]

Hi,

Does your macro do anything with the PageSetup property of any of your sheets?

[Martijn79]

Hi Martin,

My speculation is that there is something in your macros that is causing a problem.

I had a similar problem recently that was caused by a Macro that used the following code excerpt:

Please Login or Register  to view this content.

The length of the 'Formula1' string (sDataValidatationString) MUST be 255 characters or less. When the string was longer than 255 characters, the file worked normally and I could save the file fine. After I opened the flie again, I would get the dreaded file was corrupt message similar (but not the same) as yours. It took me two weeks of detective work to determine what my problem was. The fix was to have the list on the Spreadsheet and have Formula1 use a Range (e.g. 'sFormula1 := "='Sheet X'!$E$3:$E$5" ).

My suggestions:
a. Make a MASTER .xlsm copy of your file that contains no Macro code modules.
b. Make a copy of the MASTER .xlsm file
c. Introduce Macro Modules and run their code. Save the file and exit Excel.
d. Open the file, and if there is no corruption, this is your New Master file.
e. Repeat until the problem is found.

I hope this helps.

Lewis

Thanks Lewis! I was thinking, could it be because of the below code?

Please Login or Register  to view this content.

I am not highly skilled at VBA, but it seems that this is similar to your problem with respect to validation string length, or am I missing something here? If so, what would be the solution for my code?

[xlnitwit]

I would suggest that you put those values into a range somewhere, and use that range address as the DV list formula.

[Martijn79]

I would suggest that you put those values into a range somewhere, and use that range address as the DV list formula.

The problem with putting them in a sheet is that I have multiple cCond#, but not all are used for every case. Hence it could be that for a certain scenario, only cCond1, cCond2, cCond4 and cCond5 are valid and for another scenario cCond1, cCond4 and cCond6 are valid to populate the validation listbox. How do I get those from a sheet efficiently?

[xlnitwit]

You only populate the range with the valid values- if you use a named range, you can resize that in your code after populating it. Your only real alternative is to remove all the data validation before saving the file and then reinstate it upon opening.

[Zer0Cool]

When you say it renders the sheet useless, what do you mean? What gets removed and what remains, does it open, do you get additional errors?

Can you post a sample of it?

The error is saying that the .bin file inside your xlsm representing the printer config for likely sheet 1 (not a sheet named sheet1 but the first sheet made in your file) is corrupt. Usually the removal of it should have no effect on content in your file, only things like print area and maybe page layout for 1 of the sheets.

If Excel removing it causes issues you may need to remove it manually or access what caused the problem if its not part of the printer/bin file.

As none of your code seems to act upon the printer or page layout I doubt your code is the cause of the problem (unless you have code you have not shown that does). Its hard to say the cause without knowing all the symptoms in a specific sense (useless, not working, broken means nothing. We need details as to what it is/is not doing).

[Martijn79]

When you say it renders the sheet useless, what do you mean? What gets removed and what remains, does it open, do you get additional errors?

Can you post a sample of it?

The error is saying that the .bin file inside your xlsm representing the printer config for likely sheet 1 (not a sheet named sheet1 but the first sheet made in your file) is corrupt. Usually the removal of it should have no effect on content in your file, only things like print area and maybe page layout for 1 of the sheets.

If Excel removing it causes issues you may need to remove it manually or access what caused the problem if its not part of the printer/bin file.

As none of your code seems to act upon the printer or page layout I doubt your code is the cause of the problem (unless you have code you have not shown that does). Its hard to say the cause without knowing all the symptoms in a specific sense (useless, not working, broken means nothing. We need details as to what it is/is not doing).

Thanks, I can't share the sheet as it is confidential. However, I tried (based on information from Internet) to remove the bin file from the archive using 7Zip and that resulted in the file not opening at all. For the file without the bin file removed, it removes all the layout from the respective sheet, meaning that it does not show any chart, buttons, set cell widths to default etc.

[Zer0Cool]

And the error doesnt mention removing other parts of the file as well? The error should have an option to view a log or something of that fashion, its usually not much more explicit than the error but may be worth looking at.

The printsettings.bin should only remove printer related data from the file (page setup type stuff, print area, etc.). Charts, buttons and cell width data are stored in 3 other underlying files inside the xlsm. They should only be affected if those underlying files are removed/changed.

Try it without 7zip, using just windows explorer. Make a copy of the file, rename it to .zip instead of .xlsm. Open that in file explorer, delete the printersettings.bin file. Navigate out of the zip and rename it back to .xlsm. Open it in Excel. You may get an error (take note of what it says), but it should open and only be missing the print settings for a sheet (presuming there isnt other corruption in the file). If it still asks to repair when opening it should detail what it removed, in which case you either deleted the wrong bin or other parts of the file like I mentioned above are also corrupt.

It is possible the file is corrupt and not something that can be fixed. If thats the case you can try and move as much of it as possible to a new file and rebuild what was stripped out. You can also try saving it to another file type like xlsb or xls and back to xlsm as sometimes that can correct minor issues (however its not a reliable way to solve complex problems).

[LJMetzger]

Hi Martin,

You can try code like the following code. The list in the spreadsheet can be hidden by hiding the column the list is in or by formatting the cells in the list as ';;;' (3 -semicolons) which hides the contents of a visible cell in plain sight:

Please Login or Register  to view this content.

This particular routine puts 'Data Validation' in 'one cell only', because it restores the original value that was in the cell. If you don't want to keep the original cell value, the routine can be easily modified to create 'Data Validation' for a range.

NOTE: When using VBA to put data in a cell that has 'Data Validation', the value is NOT CHECKED to see if it is in the 'Data Validation List'.

Lewis

[CTGCWIQC]

I am having a similar issue and believe it may be for the same reason. I'm a VBA novice and copied this from somewhere and re-purposed it. It creates data validation drop down from a range of cells, removes duplicates. It runs on event when drop down cell is selected. Any thoughts?
I can share workbook if needed.

Please Login or Register  to view this content.