topic Re: VBscript to export with variable in the documentname in QlikView

VBscript to export with variable in the documentname

Wed, 14 Sep 2011 08:59:46 GMT

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

IAMDV — Wed, 14 Sep 2011 14:54:02 GMT

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

Wed, 14 Sep 2011 19:05:30 GMT

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

IAMDV — Thu, 15 Sep 2011 09:52:41 GMT

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

Re: VBscript to export with variable in the documentname

Thu, 15 Sep 2011 12:03:06 GMT

Hi DV

Great, thanks, it works perfect!

Paul

Re: VBscript to export with variable in the documentname

IAMDV — Thu, 15 Sep 2011 13:12:13 GMT

Hi Paul,

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

Thanks,

Re: VBscript to export with variable in the documentname

Fri, 08 Mar 2013 11:17:30 GMT

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

Re: VBscript to export with variable in the documentname

IAMDV — Fri, 08 Mar 2013 15:35:53 GMT

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

www.QlikShare.com

Cheers,

Re: VBscript to export with variable in the documentname

Thu, 14 Mar 2013 12:51:18 GMT

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 !

Re: VBscript to export with variable in the documentname

FakeJupiter — Wed, 28 Oct 2020 08:54:11 GMT

This is nice, just helped me, thanks!