topic Re: Macro Select a Field in QlikView

Macro Select a Field

angelaecheverri — Thu, 12 Dec 2013 04:01:27 GMT

Hi dears.

How is the code via Macro to select a field with an specific strings...

I need my macro who select for me select a field (Dimensions) = 'Date' , 'Contact', 'Celular'....

and then another order to un-selected....

any suggest? thanks in advance...

Re: Macro Select a Field

Thu, 12 Dec 2013 05:46:18 GMT

Use following code

ActiveDocument.GetField("Field Name").Select "Value"

ActiveDocument.Fields("Field Name").clear

Swapnil

Re: Macro Select a Field

Thu, 12 Dec 2013 05:58:42 GMT

Hi Angela,

Not sure if I understand you correctly.

But if you some values to be selected in fields and then unselect them then you can simply use Actions/Triggers instead of macros.

To select :

Go to Settings--Document Properties--Triggers , then on any event which you want write the following action:

Action Type: Selection

Action: Select in Field

Similar way to unselect:

Action Type : Selection

Action: Can be anything depending on your requirement : "Clear Other Fields" or "Clear All"

Hope this helps!

Re: Macro Select a Field

angelaecheverri — Thu, 12 Dec 2013 11:46:19 GMT

Hi Shyamal

I know this way... but i have a Macro (VB) which export for me several charts as data or image...

and i would like to be able to select something export a chart then select anothers data and export again the chart.....

thanks anyway for your answer

Re: Macro Select a Field

angelaecheverri — Thu, 12 Dec 2013 13:06:36 GMT

Swapnill

It appply just the last selection i do...

I mean I want to several charts as data or image... and i have a VBA code... which do that...

but i would like to be able to select something, and export a chart and then select anothers data and export again same chart.....

and when i use this sentence...

ActiveDocument.GetField("Field Name").Select "Value"

It actually selected but during running the code...

it just work for the last selection i do...

i cant select export a chart and select another data and export again the same chart

Any Suggest??

The code i am using is:

sub InformeNotas

Dim aryExport(2,3)

aryExport(0,0) = "TX177"

aryExport(0,1) = "Nota1"

aryExport(0,2) = "A1"

aryExport(0,3) = "data"

aryExport(1,0) = "CH100"

aryExport(1,1) = "Nota1"

aryExport(1,2) = "K1"

aryExport(1,3) = "data"

'aryExport(2,0) = "CH100"

'aryExport(2,1) = "Nota1"

'aryExport(2,2) = "K1"

'aryExport(2,3) = "data"

Dim objExcelWorkbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

end sub

'' ************************************************************************************************************************************************************************************************************************************************************************

Private Function copyObjectsToExcelSheet(qvDoc, aryExportDefinition)

Dim i

Dim objExcelApp

Dim objExcelDoc

Set objExcelApp = CreateObject("Excel.Application")

objExcelApp.Visible = true

objExcelApp.DisplayAlerts = false

Set objExcelDoc = objExcelApp.Workbooks.Add

Dim strSourceObject

Dim qvObjectId

Dim sheetName

Dim sheetRange

Dim pasteMode

Dim objSource

Dim objCurrentSheet

Dim objExcelSheet

for i = 0 to UBOUND(aryExportDefinition)

qvObjectId = aryExportDefinition(i,0)

sheetName = aryExportDefinition(i,1)

sheetRange = aryExportDefinition(i,2)

pasteMode = aryExportDefinition(i,3)

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

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

Call objSource.CopyTableToClipboard(true)

end if

Set objCurrentSheet = objExcelDoc.Sheets(sheetName)

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

objExcelDoc.Sheets(sheetName).Paste

if (pasteMode <> "image") then

With objExcelApp.Selection

.WrapText = False

.ShrinkToFit = False

End With

end if

objCurrentSheet.Range("A1").Select

end if

Call Excel_DeleteBlankSheets(objExcelDoc)

objExcelDoc.Sheets(1).Select

Set copyObjectsToExcelSheet = objExcelDoc

end function

Private Function Excel_GetSheetByName(ByRef objExcelDoc, sheetName)

For Each ws In objExcelDoc.Worksheets

If (trim(ws.Name) = Excel_GetSafeSheetName(sheetName)) then

Set Excel_GetSheetByName = ws

exit function

End If

Set Excel_GetSheetByName = nothing

End Function

Private Function Excel_GetSafeSheetName(sheetName)

retVal = trim(left(sheetName, 31))

Excel_GetSafeSheetName = retVal

End Function

Private Function Excel_AddSheet(objExcelApplication, sheetName)

objExcelApplication.Sheets.Add , objExcelApplication.Sheets(objExcelApplication.Sheets.Count)

Dim objNewSheet

Set objNewSheet = objExcelApplication.Sheets(objExcelApplication.Sheets.Count)

objNewSheet.Name = left(sheetName,31)

Set Excel_AddSheet = objNewSheet

End function

Private Sub Excel_DeleteBlankSheets(ByRef objExcelDoc)

For Each ws In objExcelDoc.Worksheets

If (not HasOtherObjects(ws)) then

If objExcelDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then

On Error Resume Next

Call ws.Delete()

End If

End Sub

Public Function HasOtherObjects(ByRef objSheet)

Dim c

If (objSheet.ChartObjects.Count > 0) Then

HasOtherObjects = true

Exit function

End If

If (objSheet.Pictures.Count > 0) Then

HasOtherObjects = true

Exit function

End If

If (objSheet.Shapes.Count > 0) Then

HasOtherObjects = true

Exit function

End If

HasOtherObjects = false

End Function

Re: Macro Select a Field

Thu, 10 Apr 2014 09:50:29 GMT

I realise that this is quite an old question now, but in case you are still looking for a solution what I did is this:

I extended the original array to include the names of any field that required a selection, and then I added a few lines to the code just before the object is exported that filter based on the value that was passed in the array.

If I was starting from scratch and had a bit more time, I would probably build a second array to contain field names and values, and pass that array along with the first to get processed for each object export, but I originally only needed to filter on one value, so went for the quick and dirty option.

So, for example, I now define my array values like this:

aryExport(0,0) = "objBanner_Header"

aryExport(0,1) = "Audience Tracker"

aryExport(0,2) = "A1"

aryExport(0,3) = "image"

aryExport(0,4) = "region"

aryExport(0,5) = "clear"

aryExport(0,6) = "basic"

And I also added these lines before the export takes place:

	If (fieldValue = "Clear") Then
		qvDoc.GetField(fieldName).clear
	Else
		qvDoc.GetField(fieldName).Select fieldValue
	End If

	If (dateRange = "highest") Then
		periodSelect = qvDoc.GetVariable("vMaxPeriod").getcontent.string
		qvDoc.GetField("FinCal.Yr_Period3").Select PeriodSelect
	ElseIf (dateRange = "basic") Then
		qvDoc.GetField("FinCal.Yr_Period3").clear
	End If

The first statement either clears or selects a value in the field. The second if statement allows me to print values either for the full date range selected, or just the last selected period.