Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Gysbert_Wassenaar

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
marcus_sommer
MVP & Luminary
MVP & Luminary

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

Not applicable
Author

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