topic VBS macro. Saving fields pre-selections in .csv in QlikView

VBS macro. Saving fields pre-selections in .csv

Peony — Wed, 29 Nov 2017 13:46:33 GMT

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

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

felipedl — Wed, 29 Nov 2017 15:52:34 GMT

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

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:

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

Peony — Thu, 30 Nov 2017 07:09:56 GMT

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

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

felipedl — Thu, 30 Nov 2017 12:01:18 GMT

Thanks Nataliia, glad it helped .