8 Replies Latest reply: Mar 14, 2013 8:51 AM by Riccardo Pettenuzzo RSS

    VBscript to export with variable in the documentname



      I understand how to export a table to excel by using a macro (vbscript).

      I used the next script:


           sub mutaties_save


           set mut = ActiveDocument.GetSheetObject("TB01")

           mut.Export "C:\Users\Demo\test.qvd",","


           end sub


      In this script the document is saved every time with the same name: Test.qvd


      How do I use a field name in the Table as variable in the name of document that´s been saved with the script and the value that´s been seelcted.


      For example

      Table first field name is ClientID

      The client ID that´s been selected is: 55


      What script do I use to make sure the document is saven woth the name: Test_ClientID_55.qvd?




        • VBscript to export with variable in the documentname
          Deepak Vadithala

          Hi Paul,


          Please can you post whatever you have done so far? I can try to workout an example for you.


          Cheers - DV

            • Re: VBscript to export with variable in the documentname

              Hi DV,


              Thanks for your reply.

              Hereby the qv document, if that´s what you mean.

              It is a demo to try things out so it has dummy data.


              What I want to try is to export a table by using a macro.

              The table has an input field which will also be exported so new data can be used in the new upload

              Every export must have a unique number so therefore the field in the first column can be used, that´s the customerID


              The purpose was to export the table with the name: mutaties_CustomerID_.qvd


              In the edit module you see some things I tried but unfortunately with no success

              I hope you can help me out.

              Thank so far


              Kind regards,


              Paul van Gool

                • Re: VBscript to export with variable in the documentname
                  Deepak Vadithala

                  Hi Paul,


                  I am attaching the QV file with the VB Script. I am assuming this is what you need. Please let me know if you have any further questions.


                  Here is the code and explanation :





                  Sub mutatie_Save()

                      Dim MyUnique_CustomerID

                          Set v = ActiveDocument.Variables("vMyFieldName")

                          MyUnique_CustomerID = v.GetContent.String

                          Set mut = ActiveDocument.GetSheetObject("TB01")

                          mut.Export "C:\Users\deepak.vadithala\Desktop\All\Test\CustomerID_" & MyUnique_CustomerID &".qvd",";"

                  End Sub



                  Step 1 : Capture the current selection the CustomerID field within QlikView environment

                  Instead of using the GetCurrentSelections type of functions, I have used the below function :


                  =Only({$} CustomerID)


                  This returns the CustomerID on each selection. Irrespective of whether you are explicitly selecting the CustomerID or selecting a associated field. I am assuming that CustomerID is the key field with unique values. Then I am assigning the above Only() function to a QV variable. Because we wanted to evaluate this function everytime QV engine is calculated. This way we are making sure...we are capturing the current selection of CustomerID field.



                  Step 2 : Using the vMyFieldName variable in the VB Script

                  We need to call the QV variable - vMyFieldName in to VB Script to get the contents of this variable. So I have used the GetContent method which returns a string value. Please note there is difference between GetContent and GetRawContent.


                  GetContent will return the CustomerID

                  GetRawContent will return the expression i,e. =Only({$} CustomerID)



                  Step 3 : Assign the QV Variable to VB Script Variable


                  Now we are assinging the QV Variable - vMyFieldName content to the VB Script Variable to MyUnique_CustomerID. And this VB Script Variable is used concatenated with the "CustomerID_" string to get the unique value.



                  I hope this makes sense.


                  Cheers - DV