Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Looping through an array of Qlikview objects not working

Hi,

I am looking to export qlikview data to an Excel spreadsheet  using an array and a For Loop in a macro.

I indicate the items in the array "objArray", I then run through the array using a For loop. Inside the For loop I set a variable to the current item in the array being used to "currObj", however it does not seem like the item in the array is being set as the script stops as soon as I first use the "currObj" variable. I can't seem to find why it is not assigning the variable "currObj" so that I can use it. Please help. Below is an extract from the export to excel macro,

Set excelFile = CreateObject("Excel.Application")
Set selectWorkbook = excelfile.workbooks.open(objFolder.self.path)
excelFile.Visible = true

Set s = ActiveDocument.Sheets("3.1 Stock Exceptions")
ActiveDocument.Sheets("3.1 Stock Exceptions").Activate
ActiveDocument.ClearCache

set excelSheet = selectWorkbook.worksheets("3.1_Exceptions")

emptyRow = 53


objArray = Array("ExceptionSummaryChart", "Exception_1", "Exception_2", "Exception_3", "Exception_4", "Exception_5")


For i = 0 to 5

set ActiveSht = Excelfile.ActiveSheet
set currObj = ActiveDocument.GetSheetObject(objArray(i))

if currObj.GetRowCount = 0 then

ActiveSht.Cells(EmptyRow,1) = currObj.GetCaption.Name.v
ActiveSht.Cells(EmptyRow +1,1) = "No Exceptions Identified"

Else

ActiveSht.Cells(EmptyRow,1)= currObj.GetCaption.Name.v

currObj.CopyTableToClipboard true
ActiveSht.Cells(EmptyRow+2,1).Select
ActiveSht.Paste
'excelsht.Paste excelsht.Range("A7")
ActiveSht.Cells.EntireColumn.AutoFit
emptyRow = excelSheet.UsedRange.rows.count + 4

ActiveDocument.GetSheetObject(currObj).Minimize
ActiveDocument.ClearCache

set currObj = Nothing
set excelSheet = nothing

end if

Next

excelSheet.SaveAs.Path objFolder.self.path

Set Excelfile = nothing
Set selectWorkbook = nothing
Set excelSheet = nothing

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Looping through an array of Qlikview objects not working

Are you sure that the object-ID's are like "Exception_1" and not like "CH01"? Also sometimes it is needed to dimension an array like: dim arr(6) or by dynamically filled/changed arrays with redim arr(AnyNumber).

Maybe you simplified the code for testing reasons and comment this and other statements and used msgbox with your loop-counter i and simple "a", "b", ... by each branch or similar to see if they work respectively by which counter it hangs.

- Marcus

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Looping through an array of Qlikview objects not working

Perhaps looking at working code can help you find the problem in your code: QlikTip #32: Exporting multiple QV objects to a single Excel document


talk is cheap, supply exceeds demand
Highlighted
MVP & Luminary
MVP & Luminary

Re: Looping through an array of Qlikview objects not working

Are you sure that the object-ID's are like "Exception_1" and not like "CH01"? Also sometimes it is needed to dimension an array like: dim arr(6) or by dynamically filled/changed arrays with redim arr(AnyNumber).

Maybe you simplified the code for testing reasons and comment this and other statements and used msgbox with your loop-counter i and simple "a", "b", ... by each branch or similar to see if they work respectively by which counter it hangs.

- Marcus

View solution in original post

Highlighted
Not applicable

Re: Looping through an array of Qlikview objects not working

I checked the object IDs, I changed the graph and Qlikview reset the Object ID without me realizing back to "CH09". Thanks!