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

    VBscript to export with variable in the documentname

      Hi

       

      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?

       

      regards,

      Paul

        • 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 :

                  __________________________________________________________________

                   

                  Code:

                   

                  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