3 Replies Latest reply: Nov 30, 2017 7:01 AM by Felip Drechsler RSS

    VBS macro. Saving fields pre-selections in .csv

    Nataliia Skliar

      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
          Felip Drechsler

          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