topic Page numbers with Excel export in QlikView

Page numbers with Excel export

Mon, 28 Sep 2009 20:27:51 GMT

I got a little problem when I export a qv-table to excel. I have to do this with a little macro in vbscript. The excel sheet is created well and I can format it as I like. But I need some data in header and footer of the excelsheet. I can not belive it but it seems to be hard to add a simple "page XY of XYZ" in the footer. Does anybody have an idea how to do this? This does not even count the printpages correct:

xlDoc.Sheets(1).PageSetup.Pages.Count

This is the vbscript I wrote:

sub exportXLS() set xlApp = CreateObject("Excel.Application") xlApp.Visible = false set xlDoc = xlApp.Workbooks.Add xlDoc.Sheets(1).activate set obj = ActiveDocument.getsheetobject("CH325") set titel = ActiveDocument.getsheetobject("TX2447") ActiveDocument.GetApplication.WaitForIdle' Set Data obj.CopyTableToClipboard true xlDoc.Sheets(1).cells(4,1).select xlDoc.Sheets(1).paste titel.CopyTextToClipboard xlDoc.Sheets(1).cells(2,1).select xlDoc.Sheets(1).paste 'delete column 9 xlDoc.Sheets(1).Columns(9).clear lastCell = 2 for i = 5 to 6500 if isempty(xlDoc.Sheets(1).cells(i,1)) then lastCell = i exit for end if next ' Set Format with xlDoc.Sheets(1) .cells.mergecells = false .Cells.EntireColumn.AutoFit 'Columnwidth .Columns(1).Columnwidth = 22 'Label .Columns(2).Columnwidth = 50 'Content .Columns(3).Columnwidth = 8.7 'Submittal Date .Columns(4).Columnwidth = 4.3 'Issue .Columns(5).Columnwidth = 7.1 'Package Revision .Columns(6).Columnwidth = 8.7 'Package Date .Columns(7).Columnwidth = 11 'Trade .Columns(8).Columnwidth = 12 'Drawing Type .PageSetup.Orientation = 2 'Landscape .PageSetup.LeftMargin = xlApp.CentimetersToPoints(2.3) .PageSetup.BottomMargin = xlApp.CentimetersToPoints(1.5) .PageSetup.TopMargin = xlApp.CentimetersToPoints(2) .PageSetup.RightMargin = xlApp.CentimetersToPoints(1) .PageSetup.CenterHeaderPicture.FileName .PageSetup.PrintArea = .Range(xlDoc.Sheets(1).cells(1,1),xlDoc.Sheets(1).cells(lastCell,8)).Address .PageSetup.LeftFooter = "Generated on " & Date & " " & Time .PageSetup.RightFooter = xlDoc.Sheets(1).PageSetup.Pages.Count end with With xlDoc.Sheets(1).Cells.Font .Name = "Arial" .Size = 9 End With 'Title rows xlDoc.Sheets(1).Cells(1,1).Font.Size = 18 xlDoc.Sheets(1).Cells(1,1).Font.Color = RGB(112, 112, 112) xlDoc.Sheets(1).Cells(1,1).Font.Bold = True xlDoc.Sheets(1).Cells(2,1).Font.Size = 16 xlDoc.Sheets(1).Cells(2,1).Font.Color = RGB(112, 112, 112) 'Header row with xlDoc.Sheets(1).Range(xlDoc.Sheets(1).Cells(4,1),xlDoc.Sheets(1).Cells(4,8)) .Font.Bold = true .Interior.Color = RGB(172,172,172) .WrapText = true end with xlApp.Visible = trueend sub

Thanks for any help.

Page numbers with Excel export

Mon, 28 Sep 2009 22:14:12 GMT

You can try this code maybe helpful

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objDoc = objExcel.Documents.Add()

Set objRange = objDoc.Sections(1).Footers(1).Range

Set objTemplate = objDoc.AttachedTemplate
objTemplate.AutoTextEntries("Page X of Y").Insert objRange

Thanks

Talha

Page numbers with Excel export

Tue, 29 Sep 2009 14:16:52 GMT

Have you tried it?

.PageSetup.RightFooter ="Pag. &P di &N"

Works!

Daniela

Page numbers with Excel export

Tue, 29 Sep 2009 14:25:06 GMT

This is it:


 .LeftHeader = ""
 .CenterHeader = PrinCenterHeader.GetContent.String 'Get variable
 .RightHeader = ""
 .LeftFooter = "Confidential"
 .CenterFooter = ""
 .RightFooter = "Page &P+1 of &N" ''Pagefooter -page xx of xxx

/Martin

Page numbers with Excel export

Tue, 29 Sep 2009 16:42:25 GMT

@Daniela Yes it does! And if someone can tell me how to insert a picture into the header I'm happy at all and can go home and pack up work 😉 This won't work. Ends up with unknown runtime error.

.PageSetup.CenterHeaderPicture.FileName = "Picture.jpg"

(The pic is in the same path as the qlikview file.)

Page numbers with Excel export

Tue, 29 Sep 2009 16:56:15 GMT


 .PageSetup.LeftHeaderPicture.Filename = "D:\QV_apps\Finance\Data\Logo\Topbg.jpg"
 .PageSetup.LeftHeader = "&G"

/Martin

Page numbers with Excel export

Tue, 29 Sep 2009 17:37:05 GMT

Works! Thanks a lot.