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.