Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Paul,
Please can you post whatever you have done so far? I can try to workout an example for you.
Cheers - DV
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
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
Hi DV
Great, thanks, it works perfect!
Paul
Hi Paul,
I am happy to know that. Please can you mark this thread answered?
Thanks,
DV
YOU CANT UNDESTAND HOW GREAT YOU ARE!..... WONDERFUL POST! IT'S OXIGEN TO ALL MY WEEK!!
Happy to know this was helpful. Many thanks for the encouraging words. Please check my blog for more tutorials...
Cheers,
DV
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 !
This is nice, just helped me, thanks!