Change Excel Page Orientation from FoxPro 1

[jcbirch]

Hi,

I hope this isn't a repeat/silly question. I'm a newbie at this. I'm trying to generate a report using FoxPro, and I want to export & format the final report to Excel from within VFP. I have already figured out some subtle changes to make to the VB so it will run from VFP (e.g. using pixels instead of the Application.InchesToPoints method, etc.). However, I can't figure out how to incorporate certain VB elements, such as the orientation property, or "Fit to X Pages Wide by Y Pages Tall," into my FoxPro code.

Here's the current code:

PROC avg_case_profile_test

oExcel = CreateObject("Excel.Application")
With oExcel
.Visible = .T.
.Workbooks.Add() && creates 3 sheets

.Sheets(1).Select
.ActiveSheet.Name = "Avg_Case_Profile"
With .ActiveSheet.PageSetup
.CenterHeader = [&I &14] + "Target Initial Analysis" + [&I &10] + CHR(10) + "Average Case Profile"
.CenterFooter = "Exhibit 1"
.RightFooter = "&D" + Chr(10) + "Page &P of &N"
.LeftMargin = "36"
.RightMargin = "36"
.TopMargin = "72"
.BottomMargin = "54"
.HeaderMargin = "36"
.FooterMargin = "36"
.CenterHorizontally = .T.
.CenterVertically = .F.
.FitToPagesWide = 1
.FitToPagesTall = 100
.Orientation = "xlLandscape"
EndWith
EndWith

oExcel=.NULL.
Release oExcel

ENDPROC

Two issues:

1) The .Orientation property line generates the following error:
"OLE IDispatch exception code 0 from Microsoft Excel: Unable to set the Orientation property of the PageSetup class."

2) The FitToPagesWide and FitToPagesTall code doesn't supercede the "Zoom" property. If you view Page Setup from the Excel report, the "Adjust to: 100% Normal Size" button is still checked, even though the pages wide by pages tall now states 1 page by 100 pages.

Any help someone could give me would be much appreciated!

Thanks,
Jessica

 

[jcbirch]

Okay - sorry. Nevermind my first question - I finally figured it out. The code should read:
.Orientation = 2

However, I'm still stumped about question #2. Anybody know how to fix the Pages Wide/Tall?

 

[Mike Gagnon]

Question #1. As for your question #1 one, in VFP the VB constants need to be pre-define, they are not assumed. faq184-4248 might help you in this matter.

Question #2 You make a statement, but you do not explain what the problem is. Can you elaborate.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jcbirch]

I want my VFP VB code to format the final Excel report to 1 page wide. In the snippet above, I had set the FitToPagesWide property to 1, but when I viewed the report in Excel, it was still "100% normal size" (instead of "Fit to 1 Pages Wide.") Does that make sense? So, it seems to me like I wrote the code, but it didn't "take."

 

[Mike Gagnon]

Try adding to your code (which doesn't seem to deal with these two settings).

.FitToPagesWide = 1
.FitToPagesTall = 100

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

Disregard my post.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

I believe the order of the settings seems to matter in this case. Orientation should come first.

 oExcel = CreateObject("Excel.Application")
    With oExcel
        .Visible = .T.
        .Workbooks.Add() && creates 3 sheets
        .Sheets(1).Select
            .ActiveSheet.Name = "Avg_Case_Profile"
            With .ActiveSheet.PageSetup
                .CenterHeader = [&I &14] + "Target Initial Analysis" + [&I &10] + CHR(10) + "Average Case Profile"
                .CenterFooter = "Exhibit 1"
                .RightFooter = "&D" + Chr(10) + "Page &P of &N"
                .LeftMargin = "36"
                .RightMargin = "36"
                .TopMargin = "72"
                .BottomMargin = "54"
                .HeaderMargin = "36"
                .FooterMargin = "36"
                .CenterHorizontally = .T.
                .CenterVertically = .F.
                .Orientation = 2
                .Zoom = .f.
	        .FitToPagesWide = 1
		.FitToPagesTall = 100
            EndWith
    EndWith

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jcbirch]

That is great. Thank you!

 

[jimoo]

It has been my experience that a printer must be installed and online for excel to adjust margins, orientation, etc. that affect printing.

Here is some code to check for that:

IF APRINTERS(gaPrinters) = 0  
  * No printer installed
  RELEASE gaPrinters
	
  IF MESSAGEBOX("Unable to detect a printer.  Microsoft Excel expects a printer in order to properly align headers and footers.  The lack of a printer may cause a problem creating the output spreadsheet reports." + CHR(10)+CHR(10) + "Do you wish to continue anyway?",4+32) = 7
		
    RETURN
  ENDIF
ELSE
  IF SYS(13) = "OFFLINE" 
    IF MESSAGEBOX("The printer is offline.  Microsoft Excel expects a printer in order to properly align headers and footers.  The lack of a printer may cause a problem creating the output spreadsheet reports." + CHR(10)+CHR(10) + "Do you wish to continue anyway?",4+32) = 7			
      RETURN
    ENDIF
  ENDIF		
ENDIF

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com