Excel 2007 Macro Compatibility Issues

[M_S_Jones]

Good morning all,

I hope somebody can help me shed some light on the following: I have a macro that formats a populated Excel spreadsheet into a new documents and archives it on one of our servers. The formatting macro has been used on a small scale and is soon to be rolled out to a larger user base. I encountered my first problems with it yesterday when I installed it on to the machine of an Excel 2007 user. The two problems I encountered are the following:

1. A problem with object embedding; OLEObjects.Add adds an icon rather than the image that the other versions of Excel (2002 and 2003) add. This icon links to the image stored in a temporary files directory.

2. The print margins appear to be smaller. I formatted a document in 2007 and then formatted the same document in 2002. When I opened the formatted result files in 2002 the column widths are identical, as are the margins in Files >> Page Setup, yet the print margin is smaller in the 2007-formatted file.

I'm trying to resolve this issue as soon as possible, and so I am happy to add an if statement checking the version number and then formatting the the page differently in 2007 (as I know the current code works okay with the other versions that we have on site.) In terms of the OLE issue, I'm sure I could add the image by other means and could just go with that. As for the print margin issue, I'm not sure how to resolve it.

Any advice would be appreciated.

Thanks,

Matthew

 

[M_S_Jones]

I've managed to resolve the first of my issues, I don't believe this was due to the version of Excel, I believe this was a compatibility issue with the software that was being used as the default image viewer. I changed the default and it started working.

The second issue I think I now understand better, as changing some of the printer settings appear to have made the margins larger (although not as large as I'd like them to be, but I can keep experimenting with that.)

I now have one last issue that I would like to resolve which is: all of the code in my macro that resizes rows appears to be ignored. Columns are resized without any problems, but rows all remain equal in height. I do not get any error messages. The code I am running to resize rows is in the format of, for example:

Rows("35:36").Select
Selection.RowHeight = 18

Does anybody know of any reason why such code should not work in 2007? It works fine on all of my other users, who use Excel versions 2000, 2002 SP3 and 2003. All of the rest of the macro, including code after the resizing of rows works fine in 2007.

Thanks for any help you can offer,

Matthew