Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Macros for dynamically export table objects to Excel

Hello everyone,

I'm not totally new to Qlik, but I'm new to VBA scripts. I try to export a table object to Excel, which works fine based on some examples I found, but what i try do to is dynamically pass the table object id. The table object id that i want to export changes based on some selections. For example if i choose Var1 = 1 and Var2=1 then show tableID1 etc. Wehn i press the export button that run the macro i want to export the table object that is shown.

Here is a part of my code:

sub ExportWithSelections

      Set XLApp = CreateObject("Excel.Application")

      XLApp.Visible = True 

      Set XLWorkbook = XLApp.Workbooks.Add

      Set XLSheet = XLWorkbook.sheets("Sheet1")

      XLSheet.Range("J1")="Selected Filters:"

      XLSheet.Range("A1").Select

     

      ActiveDocument.GetSheetObject("CH177").CopyTableToClipboard true

      XLSheet.Paste ' -4163    'values

      XLSheet.Range("B:F").NumberFormatLocal = "#.##0"

      XLSheet.Range("J2").Select


rem ** selection of the table on=bject id**

      ActiveDocument.GetSheetObject("TX1553").CopyTextToClipboard

      XLSheet.Paste ' -4163    'values

      XLSheet.Paste ' -4163    'values

      XLApp.Selection.WrapText = false


      set XLSheet = nothing

      set XLWorkbook = nothing

      set XLApp = nothing


end sub


I try to include something like this but it seems like the syntax is not correct. getVariables is a function that i use to determine the variables values so i can decide which table object id i should pass to the method.

----------------------------------------------


FUNCTION getVariable(varName)

set v = ActiveDocument.Variables(varName)

getVariable = v.GetContent.String

END FUNCTION


------------------------------------------

Dim obj as string.

if getVariable(vNA=1 and vCurrentDimensionId=1)

then obj="CH198"

elseif

  getVariable(vNA=1 and vCurrentDimensionId=2)

then obj="CH203"

  elseif

  getVariable(vNA=1 and vCurrentDimensionId=4)

then obj="CH204"

elseif

  getVariable(vNA=1 and vCurrentDimensionId=3)

then obj="CH205"

  elseif

  getVariable(vNA=1 and vCurrentDimensionId=8)

then obj="CH202"

   elseif

  getVariable(vNA=1 and vCurrentDimensionId=8)

then obj="CH202"

    elseif

  getVariable(vNA=1 and vCurrentDimensionId=9)

then obj="CH206"

end if

Any suggestion would be very much appreciated.

Best regards.

0 Replies