Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
IAMDV
Not applicable

Re: VBscript to export with variable in the documentname

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

8 Replies
IAMDV
Not applicable

VBscript to export with variable in the documentname

Hi Paul,

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

Cheers - DV

Not applicable

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

IAMDV
Not applicable

Re: VBscript to export with variable in the documentname

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

Not applicable

Re: VBscript to export with variable in the documentname

Hi DV

Great, thanks, it works perfect!

Paul

IAMDV
Not applicable

Re: VBscript to export with variable in the documentname

Hi Paul,

I am happy to know that. Please can you mark this thread answered?

Thanks,

DV

Not applicable

Re: VBscript to export with variable in the documentname

YOU CANT UNDESTAND HOW GREAT YOU ARE!..... WONDERFUL POST! IT'S OXIGEN TO ALL MY WEEK!!

IAMDV
Not applicable

Re: VBscript to export with variable in the documentname

Happy to know this was helpful. Many thanks for the encouraging words. Please check my blog for more tutorials...

www.QlikShare.com

Cheers,

DV

Not applicable

Re: VBscript to export with variable in the documentname

hi, i have a new question for You!

I would like to obtein the following result from a concat function:

FIELDCONCATENATED ----> 'field(i)','field(i+1)','field(i+2)',...,'field(n)'

ex:

'1/1/8','2/6/90','4/14/45'

i'm not able to concatenate the:   ' 

I need this solution, because i'm tring to update in a dinamic way the result of a MATCH function, without changing the script (but only a variable in an input box and adding a line in an excel document)

FOR THE MOMENT MY MATCH CONDITION IS THE FOLLOWING:

IF(MATCH(fieldP,

                         'P6/0/0',

                                                  'P5/0/0',

                         ETC....,

                                                  '25/2.5/0I'),

'fieldP is present','change'))))

AND I WOULD LIKE THIS:

IF(MATCH(fieldP,FIELDCONCATENATED),

'fieldP is present','change'))))

Therefore if it will be necessary to add a new fieldP in the excel, I don't need to open the script!

I wanted to explain this becase if you have another way of thinking should be great!

Thank You !