Skip to main content

Excel modify not taking effect

  • August 9, 2012
  • 7 replies
  • 0 views
D

I have the following very simple statement (where the handle is correct)

MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    7 replies

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    If you open the file in Excel and then save it as a different file name, does your statement then work?  If so, it might be how this payroll package creates the Excel document.  

    I have not done this in COBOL, but I know that in the Perl module for creating an Excel document (Spreadsheet::WriteExcel), there is a specific method that you call in order to allow for full compatibility.  Otherwise, certain parts of the header in the Excel file are skipped.  These are not necessary for Excel to open the file but can wreak havoc on other apps' ability to open and edit the files.

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    If you open the file in Excel and then save it as a different file name, does your statement then work?  If so, it might be how this payroll package creates the Excel document.  

    I have not done this in COBOL, but I know that in the Perl module for creating an Excel document (Spreadsheet::WriteExcel), there is a specific method that you call in order to allow for full compatibility.  Otherwise, certain parts of the header in the Excel file are skipped.  These are not necessary for Excel to open the file but can wreak havoc on other apps' ability to open and edit the files.

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    Thank you very much, no even if I save as a different file name the statement doesnt work. This has me totally baffled, I manually created an empty Spreadsheet, copied all columns and rows from the problem SS, and pasted them into my empty SS, saved that, then ran my app against my own SS, and it works perfectly. The only clue I have is that if, when opening the original SS, if I try to record a macro using the Developer tab, it immediately asks me for a VBA  project password. I assume therefore, VB has been used to do something, but I cant think of any Excel property which could be set to cause my problem    

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    This is an odd one.  It sounds like a combination of what I was saying and some sort of protection issue.  Excel protections can be bypassed - stackoverflow.com/.../is-there-a-way-to-crack-the-password-on-an-excel-vba-project.  Since it is coming from a payroll program, there may be code in the workbook that is preserving formatting and data.  

    Maybe, you can copy the contents of the spreadsheet into a new temp one and then change the formatting.

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    I am talking about copying the contents in code and not manually....

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    I am talking about copying the contents in code and not manually....

    D
    • Dominique SacreRocketeer
    • Author
    • Rocketeer
    • 19312 replies
    • August 10, 2012

    I have the following very simple statement (where the handle is correct)

    MODIFY hExcelWks Range("I:I")::NumberFormat("0.00")

    This is totally standard in many of my apps, and works in all of them, but I have received a .xls file from a user, where this simply has no effect. I have run through debug, and everything is happening as it should, except the MODIFY has no effect at all, the format of the column remains unchanged.

    The cells are not protected, as the change can be made manually in Excel, any ideas on what might have been done when this file was created? there seems to be some form of protection but I cant figure out what. The user has no idea, as it is created automatically by a payroll package, not the users themselves

    Thanks for the link to the password bypass, even if it doesnt help with this problem it is a useful one. I will play around a bit more, and maybe post the question to an Excel forum. If only I could figure out what property it is setting, but if I can bypass the password, hopefully I can get into the Macro and see what it is doing. Thanks again


    Terms & ConditionsAccessibility statement