HELP
Every document issued on my project has to have a document number and revision and pagination. Sometimes we have documents produced in Word 97 that have an appendix containing Excel 97 tables. Each table/Excel worksheet is set to fit to one page wide by one page tall. The problem is that this translates to all different actual zoom percentages, and these are applied to the header and footer panes as well as to the rest of the worksheet, so the header and footer info ends up having inconsistent font sizes on the printed pages.
Usually our solution to this is to paste each worksheet into the Word document as an Excel object, thus gaining the ability to resize the object only without affecting header and footer. The problem with this is that sometimes, if the copy area contains too much data, the clipboard seems to truncate the object on pasting into Word, dropping columns or rows that were in the copy area in Excel.
I figured out that I could manually adjust the font sizes of each header and footer in Excel using the formula [New font size = Old font size / Zoom percentage]. This is possible because after the fit to 1 sheet wide by 1 sheet tall is set in pagesetup, if the pagesetup dialog is reopened, the actual zoom percentage is displayed even though the zoom option isn't set. I figured that since the value is displayed, I should be able to return it in a VBA subroutine and automate the tedious task of calculating and changing font sizes worksheet by worksheet. But I found that the Excel object model doesn't seem to support this. The Zoom property of the PageSetup object is a variant that can either be a percentage or False. If it is false, then the FitToPagesWide and FitToPagesTall properties are used instead. Thus, if a worksheet is set to 1 sheet wide by 1 sheet tall, the Zoom property will return False rather than an actual zoom percentage.
Not easily dissuaded, I thought that maybe I could access the value through the built-in dialog object for PageSetup, since that is where it is displayed. In VBA for Word I have done this using the argument lists provided in the VBA Help. I was able to find argument lists for the Excel built-in dialogs in Help, but they do not seem to work the same as in Word. If I tack an argument on as if it were a property, e.g., Application.Dialogs(xlDialogPageSetup).Size, instead of getting a return value I get an error saying that is not an available property or method of the object.
Every way I've come at this I've hit a wall. Does anybody have an idea that might help with this, either with pasting Excel objects without loss of data, accessing the zoom percentage without first having to set it manually for each sheet, or accessing the values of built-in dialog arguments?
Every document issued on my project has to have a document number and revision and pagination. Sometimes we have documents produced in Word 97 that have an appendix containing Excel 97 tables. Each table/Excel worksheet is set to fit to one page wide by one page tall. The problem is that this translates to all different actual zoom percentages, and these are applied to the header and footer panes as well as to the rest of the worksheet, so the header and footer info ends up having inconsistent font sizes on the printed pages.
Usually our solution to this is to paste each worksheet into the Word document as an Excel object, thus gaining the ability to resize the object only without affecting header and footer. The problem with this is that sometimes, if the copy area contains too much data, the clipboard seems to truncate the object on pasting into Word, dropping columns or rows that were in the copy area in Excel.
I figured out that I could manually adjust the font sizes of each header and footer in Excel using the formula [New font size = Old font size / Zoom percentage]. This is possible because after the fit to 1 sheet wide by 1 sheet tall is set in pagesetup, if the pagesetup dialog is reopened, the actual zoom percentage is displayed even though the zoom option isn't set. I figured that since the value is displayed, I should be able to return it in a VBA subroutine and automate the tedious task of calculating and changing font sizes worksheet by worksheet. But I found that the Excel object model doesn't seem to support this. The Zoom property of the PageSetup object is a variant that can either be a percentage or False. If it is false, then the FitToPagesWide and FitToPagesTall properties are used instead. Thus, if a worksheet is set to 1 sheet wide by 1 sheet tall, the Zoom property will return False rather than an actual zoom percentage.
Not easily dissuaded, I thought that maybe I could access the value through the built-in dialog object for PageSetup, since that is where it is displayed. In VBA for Word I have done this using the argument lists provided in the VBA Help. I was able to find argument lists for the Excel built-in dialogs in Help, but they do not seem to work the same as in Word. If I tack an argument on as if it were a property, e.g., Application.Dialogs(xlDialogPageSetup).Size, instead of getting a return value I get an error saying that is not an available property or method of the object.
Every way I've come at this I've hit a wall. Does anybody have an idea that might help with this, either with pasting Excel objects without loss of data, accessing the zoom percentage without first having to set it manually for each sheet, or accessing the values of built-in dialog arguments?
