EXCEL OLE - How to insert manual page break?

[SD]

Dear Group:

I'm generating a report in excel using the EXCEL OLE feature of Harbour. The report is getting generated in MS-EXCEL correctly.

Issue:

The report is having its normal page-break with proper header & footer in A4 format - no issues.

Since, my this report is a Department wise product analysis report I want to issue additionally a MANUAL/FORCED page break so that each Department is printed on a fresh page.

Regards,

Swapan

[SD]

While trying further with my code, found this may work out:

oSheet:= oExcel:ActiveSheet()

oSheet:Cells(nRow,1):PageBreak = xlPageBreakManual

At a first glance it looks it is working now. Will check it tomorrow properly and confirm the same.

Meanwhile, if anyone is doing the same with any other alternative, please do share it here....

Thanks,

Swapan

[SD]

oSheet:Cells(nRow,1):PageBreak = xlPageBreakManual

This is to confirm that it has worked for me.............

BTW, Anyone here who has done extensive work on EXCEL OLE? Didn't found much posts here on EXCEL OLE AUTOMATION....

[Juan L. Gamero]

Swapan:

I often use Excel with OLE. You probably don't see much post here because the OLE automation works like a charm in Harbour. My main reference when dealing with VBA is the Excel/VBA help, and google. Most samples out there writen with VBA Excel are easy translated to Harbour.

Regards,

--

Juan L. Gamero

[vszakats]

Hi,

 

This is to confirm that it has worked for me.............

BTW, Anyone here who has done extensive work on EXCEL OLE? Didn't found much posts here on EXCEL OLE AUTOMATION....

Like any OLE topic, it's unrelated to Harbour itself, and 

you can easily adapt OLE code written in any other language 

(often times this is VB) or use original OLE documentation 

for the products (f.e. Excel). IOW, you can use any generic 

OLE related forum to find out more information. Stackoverflow.com 

or google.com might be good places to start.

-- Viktor

[Klas Engwall]

Hi Swapan,

I think that is because we are getting our answers most quickly from the
vbaxl*.chm file that usually resides somewhere under Program
Files\Microsoft Office, For anyone who cannot find it there, here is a
download link for version 11:

http://www.microsoft.com/en-us/download/details.aspx?id=7273

But you seem to be doing OK already :-)

Regards,
Klas

[SD]

On Friday, November 23, 2012 5:57:47 PM UTC+5:30, vszakats wrote:

Like any OLE topic, it's unrelated to Harbour itself, and 

you can easily adapt OLE code written in any other language 

1st of all thank you so much for attending this thread..... 

As you've rightly said, I'm also for the same reason refraining myself from making much "NOISE" in this forum with my "EXCEL OLE Queries".

for the products (f.e. Excel). IOW, you can use any generic 

OLE related forum to find out more information. Stackoverflow.com 

or google.com might be good places to start.

Yes, been to these places for couple of days....., moreover recording "manual operations" with the help of "Record Macro" feature of MS-EXCEL and checking the generated VBA codes out of them, do helps a lot...But hope fellow members would bear with me if I throw up a few OLE queries in this forum which I fail to accomplish in my project.  

-- Viktor

I take this opportunity to ask you a question regarding usage of OLE here in Harbour (sorry asking straightaway without Googling or checking any reference):

 Does TestOLE.prg demo, exhibits the complete list of OLE activities we can perform under harbour? If there are more, then can you please name a couple of major ones. 

Regards,

Swapan

[vszakats]

Hi SD,

On Friday, November 23, 2012 6:38:04 PM UTC+1, SD wrote:

1st of all thank you so much for attending this thread..... 

As you've rightly said, I'm also for the same reason refraining myself from making much "NOISE" in this forum with my "EXCEL OLE Queries".

No problem with that in general, but it's just not the 

best place to ask it, unless it's related to some special 

Harbour properties or subtleties of OLE handling.

I take this opportunity to ask you a question regarding usage of OLE here in Harbour (sorry asking straightaway without Googling or checking any reference):

 Does TestOLE.prg demo, exhibits the complete list of OLE activities we can perform under harbour? If there are more, then can you please name a couple of major ones. 

No, it's just some random/interesting appetizer 

to demonstrate some of its capabilities.

OLE is an MS standard to access 3rd party objects 

in simple and transparent way, so you can do anything 

with it that those 3rd party OLE objects/servers 

provide for you. Harbour provides the _interface_ for 

that.

-- Viktor

[SD]

On Friday, November 23, 2012 5:56:07 PM UTC+5:30, Juan L. Gamero wrote:

I often use Excel with OLE. You probably don't see much post here because the OLE automation works like a charm in Harbour.

Really Harbour works like charm when it comes to integration with Excel. While exploring OLE Integration feature of Harbour, it has given me immense joy. As now it has become almost a necessity rather then feature (at-least in India) to have all reports with "Export to Excel/PDF" kind of feature in business applications, SAP also provides export to Excel feature which helps people esp. of managerial grade to use this data for further manipulation/analysis with a tool (excel/spreadsheet) which they are comfortable.......

Good to know that a few here are using Excel OLE... so not to worry much when stuck with an issue:)

Regards,

Swapan

[SD]

I think that is because we are getting our answers most quickly from the
vbaxl*.chm file that usually resides somewhere under Program
Files\Microsoft Office, For anyone who cannot find it there, here is a
download link for version 11:

http://www.microsoft.com/en-us/download/details.aspx?id=7273

Yes...yes now I remember that while searching for EXCEL OLE, I did came through your this suggestion which you suggested to a member in the forum...

I forgot about this, while over-googling a bit for last few days:)

But you seem to be doing OK already :-) 

Yes, beginners-level achieved:-) 

Thanks for the support!

Regards,

Swapan

 

[SD]

On Saturday, November 24, 2012 12:07:54 AM UTC+5:30, SD wrote:

I think that is because we are getting our answers most quickly from the
vbaxl*.chm file that usually resides somewhere under Program
Files\Microsoft Office, For anyone who cannot find it there, here is a
download link for version 11:

/*

1. I couldn't accomplish this for my following issue, so making a bit of noise here with an Excel OLE related question.

2. Should I need to post this as a "new post"?

*/

Dear Group:

This request is in continuation of my journey to porting a report to Excel using OLE under Harbour 3.0...................

The Excel report is ok, with proper horizontal manual page breaks. Now am looking for:

HOW TO INSERT VERTICAL PAGE BREAK?

So that the print-out comes up correctly on the same page, and not a portion of the right side of the page moves to the next page.....

I was using oSheet:PageSetup:Zoom = 62, and if I recollect correctly, it was giving correct "page break view" on my system, but when same routine on other computer with different office ver. - the horizontal manual page break was intact & correct, but the vertical page break got destroyed....then I removed the code for the zoom property but nothing seems to be ok for me with respect to vertical page break.....

My printed data range is say:A1:O167 (this is variable as per the actual data size). The "FIXED" is I want to have a vertical page break after the right-most end of my data, which is in this case is COLUMN "O". If range is required, that can be assigned but I tried lot of options nothing resulted the desired vertical page break.

I was trying like this:

oSheet:VPageBreaks:AddBefore:= oSheet:Range(mrange)

oSheet:VPageBreaks(1):Location:= oSheet:Range( "P1" )

oSheet:Range("P1:P167"):VPageBreaks:DragOff:Direction:= xlToRight

If someone can supply the working code (Harbourized!) for this issue, would be highly obliged.

Regards,

Swapan

 

[Klas Engwall]

Hi Swapan,

> HOW TO INSERT VERTICAL PAGE BREAK?
> So that the print-out comes up correctly on the same page, and not a
> portion of the right side of the page moves to the next page.....
>
> I was using oSheet:PageSetup:Zoom = 62, and if I recollect correctly, it
> was giving correct "page break view" on my system, but when same routine
> on other computer with different office ver. - the horizontal manual
> page break was intact & correct, but the vertical page break got
> destroyed....then I removed the code for the zoom property but nothing
> seems to be ok for me with respect to vertical page break.....
>
> My printed data range is say:A1:O167 (this is variable as per the actual
> data size). The "FIXED" is I want to have a vertical page break after
> the right-most end of my data, which is in this case is COLUMN "O". If
> range is required, that can be assigned but I tried lot of options
> nothing resulted the desired vertical page break.
>
> I was trying like this:
>
> oSheet:VPageBreaks:AddBefore:= oSheet:Range(mrange)
> oSheet:VPageBreaks(1):Location:= oSheet:Range( "P1" )
> oSheet:Range("P1:P167"):VPageBreaks:DragOff:Direction:= xlToRight
>
> If someone can supply the working code (Harbourized!) for this issue,
> would be highly obliged.

I cannot comment on setting pagebreaks other than to consult the
vbaxl*.chm help file you are already consulting :-) but I can confirm
that oSheet:PageSetup:Zoom := nPercentage has always served me well.
Which two different Office versions you are referring to? All features
may not exist in the oldest versions, but zooming is OK from at least
Office 2000.

Regards,
Klas

[SD]

Hi Swapan,
On Thursday, November 29, 2012 7:46:53 PM UTC+5:30, Klas Engwall wrote:

Thanks Klas for responding to my post. I just don't know why but it gives me sheer joy when I see that you've responded to to my post/comment. May be due to your sincere participation in the comp.lang.clipper forum with respect to my certain important posts.

 

I cannot comment on setting pagebreaks other than to consult the
vbaxl*.chm help file you are already consulting :-)

Oops! I was expecting so much from you!! :-) On serious note, its very much ok. You never needed badly ever...otherwise by now you would have discovered it.

but I can confirm that oSheet:PageSetup:Zoom := nPercentage has always served me well. 

[note: This is ok....the %age passed from the prg (say 62%) do remains ok...on other systems also....I doubt its not that easy when others could have different monitor sizes and resolutions...will come to this issue (if any), after my vertical page break issue gets resolved...]

I want the generated excel file to carry proper "pagebreaks", so that the user may not need to go for "Page Break Preview" and re-set the vertical page to the extreme  right to have the proper print-outs as per the report requirement. [Actually this report origins from a super/special -condensed mode report in DMP in 80 col. stationery now looking for A4 option also.]

Would appreciate if any1 like Juan L. Gamero jumps in for the Vertical Page Break solution........otherwise I may have to post this as a NEW POST to attract other members also...

Regards,

Swapan

[ujayadev_at_gmail.com]

Dear Swapan

HOW TO INSERT VERTICAL PAGE BREAK?

So that the print-out comes up correctly on the same page, and not a portion of the right side of the page moves to the next page.....

Use: oSheet:VPageBreaks:Add(oSheet:Range("D1"))

HTH,

Jayadev

[SD]

Dear Jayadev:

Thanks a lot! It does work!! Thanks for supplying the "harbourized" code...........oSheet:VPageBreaks:Add(oSheet:Range("D1"))

BUT PLEASE NOTE, certain issues are there in my case. 

--I do have manual horizontal page breaks, things are not formatting the way intended, with proper synchronization of Horizontal & Vertical page breaks..

--I have columns from A to O (fixed no. of columns) with dynamic rows as per the data.

--I want to give the vertical page break after the right most column of my data column (between O & P)

--When Vpage-range not given then auto vpage break appears between I & J cols.with dashed vertical lines 

--When for testing Vpage-range supplied with "D" then manual vpage break appears between C & D cols.with bold vertical lines 

  (its too late in the night for me to do further investigation....)

I want to convey that in this way manual and auto both vertical lines exists...so purpose gets defeated...

What I've worked out roughly is that, the combination of:

PageSetup:Zoom, PageSetup:FitToPagesWide and PageSetup:FitToPagesTall properties  could be the best keys to resolve the issue in my case.

Yes, Jayadev it really helped me to further understand my issue.....and get it resolved (will try for the final testing 2morrow)

Regards,

Swapan