Macro to check if data exists in a chart

[#1]

Hi all,

I am a beginner in QlikView and what's more a French guy trying to be understandable to the community, so please be lenient with me

Here is the issue :

I am attempting to write a macro (not inside QlikView but in a external VBScript file) in order to export each day data from a chart in QlikView to an Excel file. More specifically I need to export the value of only one cell in this chart and paste it in my file. So i use this command :

objCell = ActiveDocument.GetSheetObject("CH99").GetCell(1,20)
Workbook.Worksheets(1).Cells(1,1) = objCell.Text

In cases where the chart in QlikView does not contain any data, the message "Error" is displayed in the chart. And thus my VBScript file cannot extract the desired data and I got an error.

That is why, I would like to check if my chart contain data before exporting :

- if data exists, then do extract normally

- if there is no data, write a 0 in my Excel file, as below :

Workbook.Worksheets(1).Cells(1,1) = "0"

But I have no idea how to do this so any help is welcome

Hope that I have been understandable 

Thanks in advance !

[marcus_sommer]

Try something like this:

set table = ActiveDocument.GetSheetObject( "CH99" )

if table.GetRowCount > 0 then

objCell = table.GetCell(1,20) Workbook.Worksheets(1).Cells(1,1) = objCell.Text

else

Workbook.Worksheets(1).Cells(1,1) = "0"

end if

- Marcus

[#3]

Thanks for your answer !

Unfortunalety, it did not work, I got an error in this line:

objCell = table.GetCell(1,20)

So it would mean the chart has rows and colums but they are empty, right ?

To make things clear, I have this message in the middle of my chart when there is no data :

I think this is a message similar to "No data to display".

[marcus_sommer]

I'm not sure if this message is the same like 'No data ...' or that any calculation condition on the object/dimension/expression isn't true - I could imagine that it directly returned an error and I would catch this error under all circumstances. If you are on reviewing your expression you could try to add something like:

alt(YourExpression, 0)

to return 0 (disable suppressing 0 within th tab presentation) if your expression hadn't any result and you could save the extra-step within the macro.

- Marcus

[#5]

I am not sure I grasped everything you said

Tell me if I am right :

You propose to use the function

alt(YourExpression, 0)

where

YourExpression = table.GetCell(1,20)

So if the command table.GetCell(1,20) does not have any result it returns a 0, otherwise it returns the value of the cell (1,20) of my chart.

[marcus_sommer]

No, I meant to catch the case of no data within the qlikview-object and not within the vbs. Whereby if I think about to use only one single cell from the table it could be easier to use an expression within a variable or to call this expression from the macro instead of using the table. How looked the expression from the table and are there any dimensions?

- Marcus

[#7]

Thanks for your explanations, but I think it is beyond my level of competence (which is quite low...). Moreover, I do not have access to the "back office" of Qlikview. I mean I can only view the chart  but I cannot handle it. That is the reason why I decided to code not in a macro inside QlikView application but in a separate external VBScript file.

But I find this command :

On Error Resume Next   

Which allows to bypass the error I got when there is no data in my chart and to continue the execution of the VBSscript file. But in that case the cell I have to fill in my Excel workbook is left empty.

So it is a partial solution, as I need to put a "0" in the cell when I got this error.

[marcus_sommer · Accepted solution]

Then your work will be quite limitated and you will need a lot of efforts to build all these workarounds instead of using the inbuilt qlik-capabilities. I suggest to review with your colleagues the roles and access-rights within your qlik-environment.

Beside them try something like this:

set table = ActiveDocument.GetSheetObject( "CH99" )

on error resume next

objCell = table.GetCell(1,20) Workbook.Worksheets(1).Cells(1,1) = objCell.Text

if err.number > 0 then

     Workbook.Worksheets(1).Cells(1,1) = "0"

end if

on error goto 0

- Marcus

[#9]

Thanks ! It works