Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kitjupitera
Contributor

VBS macro. Saving fields pre-selections in .csv

Hi.

In the attached file I have a macro script for saving preselected values into the .csv. And also there is OnOpen trigger for "Top" field selections.

When I run macro pushing "FieldsSave" button in .csv sholud appear string "TOP | 10" as result. But it wont happend. And I can't get where is the mistace in code.

Please, help to find out where is the mistake.

Sub extractAOSF

fileName = replace(ActiveDocument.GetProperties.FileName,".qvw","")

filePath = (fileName)&".fields.csv"

    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FileExists(filePath) Then

        Set storageFile = fso.OpenTextFile (filePath, 2, True)

    Else

      Set storageFile = fso.CreateTextFile(filePath)

    End If

    storageFile.WriteLine "FieldName|FieldValue"

    Set ffds=ActiveDocument.Fields("$Field").GetPossibleValues

    AOSCount=0

    for i=0 to ffds.Count-1

    v = ffds.item(i).Text

set field=ActiveDocument.GetField(v)

AO=field.GetProperties.OneAndOnlyOne

    if AO = True then

    set selection=field.GetSelectedValues

    value=selection.Item(0).Text

    storageFile.WriteLine( value.Name & "|" & value.RawValue)

    AOSCount=AOSCount+1

    end if

next

   storageFile.Close()

    msgbox("AOS Fields saved:"&AOSCount)

End Sub

Tags (1)
1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: VBS macro. Saving fields pre-selections in .csv

Hi Nataliia,

Try the following code:

Sub PreSelection

     ActiveDocument.Fields("TOP").Select "10"

     ActiveDocument.Fields("Qrt").Select "2012 Q1"

End Sub

Sub extractAOSF

     fileName = replace(ActiveDocument.GetProperties.FileName,".qvw","")

     filePath = (fileName)&".fields.csv" 

     PreSelection()

    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FileExists(filePath) Then

        Set storageFile = fso.OpenTextFile (filePath, 2, True)

    Else

      Set storageFile = fso.CreateTextFile(filePath)

    End If 

 

    storageFile.WriteLine "FieldName|FieldValue"

    Set ffds=ActiveDocument.Fields("$Field").GetPossibleValues

  

    AOSCount=0

    for i=0 to ffds.Count-1

    v = ffds.item(i).Text

    'storageFile.WriteLine v

    set teste = ActiveDocument.Fields(v).GetSelectedValues

    ' If no selections are made more selections being made

    'for j=0 to teste.Count -1

    ' set allFields = teste(j) & ","

    'next

    storageFile.WriteLine v & "|" & teste(0).Text

    AOSCount = AOSCount + 1

next

   storageFile.Close()

    msgbox("AOS Fields saved:"&AOSCount)

End Sub

This will get the selected values of the filesds into the cvs file, getting the following image:

Sample.png

3 Replies
felipedl
Valued Contributor III

Re: VBS macro. Saving fields pre-selections in .csv

Hi Nataliia,

Try the following code:

Sub PreSelection

     ActiveDocument.Fields("TOP").Select "10"

     ActiveDocument.Fields("Qrt").Select "2012 Q1"

End Sub

Sub extractAOSF

     fileName = replace(ActiveDocument.GetProperties.FileName,".qvw","")

     filePath = (fileName)&".fields.csv" 

     PreSelection()

    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FileExists(filePath) Then

        Set storageFile = fso.OpenTextFile (filePath, 2, True)

    Else

      Set storageFile = fso.CreateTextFile(filePath)

    End If 

 

    storageFile.WriteLine "FieldName|FieldValue"

    Set ffds=ActiveDocument.Fields("$Field").GetPossibleValues

  

    AOSCount=0

    for i=0 to ffds.Count-1

    v = ffds.item(i).Text

    'storageFile.WriteLine v

    set teste = ActiveDocument.Fields(v).GetSelectedValues

    ' If no selections are made more selections being made

    'for j=0 to teste.Count -1

    ' set allFields = teste(j) & ","

    'next

    storageFile.WriteLine v & "|" & teste(0).Text

    AOSCount = AOSCount + 1

next

   storageFile.Close()

    msgbox("AOS Fields saved:"&AOSCount)

End Sub

This will get the selected values of the filesds into the cvs file, getting the following image:

Sample.png

kitjupitera
Contributor

Re: VBS macro. Saving fields pre-selections in .csv

Brilliant! It works! Felip thank you! Your solution is elegant and simple.

felipedl
Valued Contributor III

Re: VBS macro. Saving fields pre-selections in .csv

Thanks Nataliia, glad it helped .

Community Browser