Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Add filter condition using macro

Hi All,

Is there a way to restrict data using macros?

Regards,

Sabarish

4 Replies
Not applicable

Add filter condition using macro

did you ever get an answer for this?

MVP
MVP

Re: Add filter condition using macro

activedocument.fields("Product").select "Apples"

It may be better to do it with an action if that's an option, though.

Not applicable

Re: Add filter condition using macro

Thank you I figured it out but I used it with the () around “Apples”. It is working perfectly, is there a difference?

I got the code below from a blog and added some formatting and another layer to the array for geo. Creates a spreadsheet in a few seconds for all 3 geos plus global.

for i = 0 to UBOUND(aryExportDefinition)

'// Get the properties of the exportDefinition array

qvObjectId = aryExportDefinition(i,0)

sheetName = aryExportDefinition(i,1)

sheetRange = aryExportDefinition(i,2)

pasteMode = aryExportDefinition(i,3)

SelectGeo = aryExportDefinition(i,4) 'NEW

Set objExcelSheet = Excel_GetSheetByName(objExcelDoc, sheetName)

if (objExcelSheet is nothing) then

Set objExcelSheet = Excel_AddSheet(objExcelApp, sheetName)

if (objExcelSheet is nothing) then

msgbox("No sheet could be created, this should not occur!!!")

end if

end if

objExcelSheet.Select

set objSource = qvDoc.GetSheetObject(qvObjectId)

Call objSource.GetSheet().Activate()

objSource.Maximize

qvDoc.GetApplication.WaitForIdle

if (not objSource is nothing) then

if (pasteMode = "image") then

Call objSource.CopyBitmapToClipboard()

else

if(SelectGeo = "*") then 'NEW

ActiveDocument.Fields("ACCOUNT_GEO").Clear 'NEW

else 'NEW

ActiveDocument.Fields("ACCOUNT_GEO").Select(SelectGeo) 'NEW

end if 'NEW

Call objSource.CopyTableToClipboard(true) '// default & fallback

end if

Set objCurrentSheet = objExcelDoc.Sheets(sheetName)

objExcelDoc.Sheets(sheetName).Range(sheetRange).Select

objExcelDoc.Sheets(sheetName).Paste

'assign columns width

objExcelDoc.Sheets(sheetName).Range("A1:A60000").ColumnWidth = 15

'autosize rows to fit

objExcelDoc.Sheets(sheetName).Cells.EntireRow.AutoFit

objExcelDoc.Sheets(sheetName).Range("A1:Z1").WrapText = True

objExcelDoc.Sheets(sheetName).Range("B2").Select

objExcelApp.ActiveWindow.FreezePanes = True

if (pasteMode <> "image") then

With objExcelApp.Selection

' .WrapText = False

' .ShrinkToFit = False

End With

end if

objCurrentSheet.Range("A1").Select

end if

next

Call Excel_DeleteBlankSheets(objExcelDoc)

'// Finally select the first sheet

objExcelDoc.Sheets(1).Select

'// Return value

Set copyObjectsToExcelSheet = objExcelDoc

end function

'//

MVP
MVP

Re: Add filter condition using macro

So far as I know, there's no difference between putting the value in parentheses or not.