How do I input column headers in an Excel report with variable width columns?

[SciManStev]

I am trying to create an Excel report with variable column widths so that the column headers have a blue background, and show up at the top of each page in the report. The end report will use text wrapping. How can I do this?

[systemcrash]

Report generation toolkit can help you in this matter, Go to Excel format API and you can use all the different API to set your cell border, alignment, colors etc etc

[SciManStev]

I am able to create the report. Other than the headers, each line is a record from a database. I can set the report so that the headers are blue, and the text wraps around after the report is finished. That is not the problem. The problem comes in trying to keep the headers at the top of each new page. The vi's that actually insert column headers will do it, but force the column widths to one value, even when using a template. Typically what I have done is to lay out the column headers and widths, and color if needed, then filll the report row by row with different database records. What I need to do is reinsert the column headers at the top of the next page. Can I poll the report and see when a new page is started? That way I could reinsert the headers as a row in the report. Or is there some other method for doing this?

Thank you!

[systemcrash]

How about instead of using column headers you update each top cell induvidually and then format this accordingly according to your requirement, but I doubt the cell width might alter when you format the columns below, hope you are getting what I mean.

Can I poll the report and see when a new page is started? That way I could reinsert the headers as a row in the report.

I believe it is up to you when do you want to stop updating the page 1 in excel and move on to page 2 or wait until all 65536 rows are filled and then move 2 next page.

[SciManStev]

How about instead of using column headers you update each top cell induvidually and then format this accordingly according to your requirement, but I doubt the cell width might alter when you format the columns below, hope you are getting what I mean.

This is what I have been doing up to this point. The only thing I can't do is to reinsert the column headers at the top of the next page because I don't know when the next page starts. The database records vary in size so that because of the wrap around` text at the end, each row can and does vary considerably in height. That means that there could be 1 to 25 rows per page. If I knew when the next page was to start, I could reinsert the headers and color easily. Without knowing how many rows are going to be for each page, or not knowing when a page break is created makes this impossible. I can easily create the entire report the way I want it except that the column headers are only displayed once, at the very top. The example Excel sheet my boss handed me that I am supposed to duplicate has the column headers at the top of each page. There could be between 1 and 200 pages, so only having the column headers displayed once would make the report hard to read.

Thank you!

[systemcrash]

How about using the EXCEL GET WORKSHEET function to track for the page number, keep monitoring the page number when there is change in number you can insert the column names in new page and allow the excel page to be filled up again. Did you try this?

[SciManStev]

That might work. I will work out the code and post back when I get it squared away. Excellent idea! Thank you very much!

[systemcrash]

Well my question would be how the control goes to next page when you are not directing it to go to next page.

[SciManStev]

The rows increase one at a time. In Excel itself, when you specify from the page setup "down then over or over then down, Excel shows a dotted line where the page boundries are. That dotted line changes as you manually increase or decrease the row height, but gives the same size for a page. My report just keeps extending downward until it finishes. I will try your method and see what happens.

[SciManStev]

Well, that didn't work. I am not changing worksheets, just adding row after row. The concept seems valid, so I will keep searching for a vi that says a page break was just added. I think I may be out of luck.