topic Re: Looping through an array of Qlikview objects not working in QlikView

Looping through an array of Qlikview objects not working

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Looping through an array of Qlikview objects not working

Gysbert_Wassenaar — Thu, 04 Jun 2015 15:53:23 GMT

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

Re: Looping through an array of Qlikview objects not working

marcus_sommer — Thu, 04 Jun 2015 20:34:22 GMT

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

Re: Looping through an array of Qlikview objects not working

Fri, 05 Jun 2015 07:25:49 GMT

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