Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
deec
Contributor

Editing a macro... VB help

We're currently using Stefan's method for exporting some tables out to excel

QlikTip #32: Exporting multiple QV objects to a single Excel document

In short, I'm trying to pass some Excel ActiveWindow VBA properties, but can't seem to get it right.

If you're not familiar, his code asks for you to fill an array with information and then call a function called copyObjectsToExcelSheet(). The function looks like this:

Set objExcelApp = CreateObject("Excel.Application")

Set objExcelDoc = objExcelApp.Workbooks.Add

'a bunch of stuff with the array

Set copyObjectsToExcelSheet = objExcelDoc

end function


After the function ends, you're sent back to the original sub, which ends this way:

Dim objExcelWorkbook 'as Excel.Workbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here

'// like saving the excel, some formatting stuff, ... 

As you can see, the author encourages you to make any changes at this point, by adding to the code.

After the code runs, I can make changes to the Sheet objects by adding some things. Like this:

For i = 1 to 4

  With objExcelWorkbook.Sheets(i).Range("A1:A2").Font

         .Name = "Calibri"

         .Size = 20

  .Bold = True

  objExcelWorkbook.Sheets(i).Range("8:8").WrapText = True

next


But I can't edit some ActiveWindow properties in excel. Like these:

    ActiveWindow.FreezePanes = True

    ActiveWindow.DisplayGridlines = False

How can I pass ActiveWindow properties to the sheets that the function worked on? Can I do this without editing the function any further?

Any help would be greatly appreciated.