Setting the print area?

FLX TMS FlexCel Studio for .NET
1

Hi there,


I've just recently started using Flexcel and I'm highly impressed about the speed and flexibility.
I'm currently trying to finish a project for a customer for which I'm copying together data from various excel tables into a single document with many sheets.

The customer now wants me to set the print rage so only data from cell A1 up till Y(rowcount) (the number of rows varies) gets printed. I can do this manually by settings focus on these cells and clicking on page layout > Print area > Assign print area within excel. But as the final document contains roughly 500 sheets and gets generated once a month, that's not really an option.

I've searched these forums and all available documentation but couldn't find a way to solve my issue. Only thing I found was a VBA Script which pretty much would do the job:

Worksheets("Tags").PageSetup.PrintArea = Worksheets("Tags").Range( _
        Cells(2, 1), Cells(Worksheets("Tags").Range("A65536").End(xlUp).Row, 12)).Address

I couldn't find a way to embed and automaticly run that one either though, so I'm looking for some guidiance how I can get my project finished.

Thanks for your help,

  Patrick
2
Hi,
PrintArea can be a little confusing beacuse it is "just" a hidden named range, so FlexCel exposes it as such. You set it by creating the named rage, instrad of having a command specifically for printarea.

The simplest way to find out the exact commands is with APIMate. (this is in the start menu->tms FlexCel->Tools)

The steps are:
1)Create an empty file in Excel, and set the Print Area as you normally do: page layout > Print area > Assign print area
2)Save the file in Excel.
3)Open the file in APIMate (there is no need to close Excel)

APIMate should give you the code for it, in C# or VB.NET. Here I get:
 
   //Named Ranges
    TXlsNamedRange Range;
    string RangeName;
    RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Area);
    Range = new TXlsNamedRange(RangeName, 1, 32, "=Sheet1!$A$1:$B$3");
    //You could also use: Range = new TXlsNamedRange(RangeName, 1, 1, 1, 1, 3, 2, 32);
    xls.SetNamedRange(Range);

In your case, as you probably have the coordinates in numbers, I would use the commented line above:
Range = new TXlsNamedRange(RangeName, SheetIndex, SheetIndex, FirstRow, FirstCol, LastRow, LastCol, 32);
  
instead of
Range = new TXlsNamedRange(RangeName, 1, 32, "=Sheet1!$A$1:$B$3");

Note that if you do want to use a formula, you can construct the formula references like $A$1 with the TCellAddress class.

Of course you can use APIMate for a lot of other things: when you know how to do something in Excel and not in FlexCel, you can try it.
3

Thanks a lot for the FAST reply!


I've already discovered the code you mentioned using APIMate, but it didn't seem to work in thie first place. Now with your reply I found the issue: I'm setting the print area after a sheet has been generated for each sheet. It seems however, that it's overwriting the setting so only the last sheet is configured with the proper print settings.

Looking at this example however, Excel seems to know this setting per page:
http://excel.tips.net/T003157_Setting_Print_Ranges_for_Multiple_Worksheets.html

Any way to get this working using Flexcel?

Thanks and regards,

  Patrick
4

Hi,

Yes, page print area is per sheet. When you do
Range = new TXlsNamedRange(RangeName, SheetIndex, SheetIndex, FirstRow, FirstCol, LastRow, LastCol, 32);

Make sure to change SheetIndex to 1 ofr the first sheet, 2 for the second, etc.
Or if you prefer, and the ActiveSheet is correct, you could do:
Range = new TXlsNamedRange(RangeName, xls.ActiveSheet, xls.AcriveSheet, FirstRow, FirstCol, LastRow, LastCol, 32);
5

To expand a little in the answer: There are 2 different "Sheets" in a named range.


The first is the sheet where the name is actually stored. Normally this is sheet 0, or a "global" sheet. All names are by default stored there. An exception happens if you copy a sheet: As we can't have 2 global names with the same name, the copied name in the copied sheet will be local.

You can see the sheet where a name is stored in Formulas->Name Manager, in the "Scope" column:



The other sheet, is the sheet where the name "refers to" In the image above, the name will be stored in the Workbook (sheet 0), but it will refer to sheet1, because the formula is Sheet1!A1.

Note that not all the names will have a RefersTo sheet. You might define a name as "=1+2" and this won't refer to any sheet. Still, it will be stored somewhere, either in a sheet or locally.

For the case of the print area, the area must be stored locally in the sheet where it applies (different from most names which are stored in the workbook). And it must refer to the same sheet. 

This is why we set the sheet twice:
Range = new TXlsNamedRange(RangeName, SheetIndex, SheetIndex, FirstRow, FirstCol, LastRow, LastCol, 32);

the first sheet index is where the sheet will be stored, the other the sheet where the formula will refer to. Both must be the same.

If you use the other way:
Range = new TXlsNamedRange(RangeName, LocalSheetIndex, 32, "=Sheet1!$A$1:$B$3");

Then localSheetIndex is the sheet where the name will be stored, and the sheet where the name referst to is given by the formula: Sheet1!...