Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
deec
Creator
Creator

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.

0 Replies