Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
cat66 Absent Member.
Absent Member.
1994 views

Excel modify not taking effect

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

0 Likes
5 Replies
buggabill Absent Member.
Absent Member.

RE: Excel modify not taking effect

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.

0 Likes
cat66 Absent Member.
Absent Member.

RE: Excel modify not taking effect

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    

0 Likes
buggabill Absent Member.
Absent Member.

RE: Excel modify not taking effect

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.

0 Likes
buggabill Absent Member.
Absent Member.

RE: Excel modify not taking effect

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

0 Likes
cat66 Absent Member.
Absent Member.

RE: Excel modify not taking effect

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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.