Editing a macro... VB help

deec — Mon, 16 Feb 2015 20:16:29 GMT

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.

topic Editing a macro... VB help in QlikView

Editing a macro... VB help