call VBscript function/procedure from loading script

[#1]

Hi all,

I wrote vbscript macro that export data to excel.

When running the macro from the "edit module" screen (by clicking on the "Test" button") it works.

I'm trying to call my function from the loading script.

I tried to write the macro as a procedure (with SUB, END SUB) and call from script with "CALL" - but it throws "script line error" message.

I tried to write the macro as a function (with Function, END Function) and call from script with SET var = MyFunc() or with LOAD MyFunc(), but no data was exported.

How should I call my macro? Does there is any security issue?

I read all discussions about this issue and tried to do everything but it still doesn't work...

Can someone please advise? thanks!!!

[israrkhan]

have you tried with button and trigger,

create a button and add action on button, "run a macro" an see if it works...

[marcus_sommer]

During the load it didn't exist the ActiveDocument and all methods which needs this basic-point will fail. Instead you could use a PostReload-Trigger.

- Marcus

[MarcoWedel]

Your function method should work.

[MarcoWedel]

One example:

Fuzzy Matching/Joining

[MarcoWedel]

1. QUALIFY *; 
2.  
3. Table1: 
4. LOAD *, RecNo() as RecNo INLINE [ 
5.     MARK1, MARK2 
6.     CH: WV2ZZZ7HZ9H119390, ENGINE NO: AXB179634 
7.     CH: WV2ZZZ7HZ9H119548, ENGINE NO: AXB179429 
8.     CHASSIS NO:SHSRE77508U101822, ENG NO:K2424-1019151 
9.     CH: KL1PM5E51AK528096, ENG: F18D4009613KA 
10.     CH: WV2ZZZ7HZ9H100620, ENG NO: AXB178969 
11.     CH: AHTLT58E406009706, ENG: 1NR0178419 
12.     CH: AHTLT58E206008912, ENG: 1NR0162953 
13.     CH: AHTLT58E606008833, ENG: 1NR0162819 
14. ]; 
15.  
16. Table2: 
17. LOAD *, RecNo() as RecNo INLINE [ 
18.     MARK1, MARK2 
19.     CHASS# WV2ZZZ7HZ9H119390, E# AXB179634 
20.     C# WV2ZZZ7HZ9H119548, ENG NO: AXB179429 
21.     CH: AHTLT58E206008912, ENG: 1NR0162953 
22.     CH: KL1PM5E51AK528096, ENG: F18D4009613KA 
23.     CHS# WV2ZZZ7HZ9H100620, ENG#: AXB178969 
24.     CH: AHTLT58E406009706, ENG: 1NR0178419 
25.     VIN NO:SHSRE77508U101822, ENG:K2424-1019151 
26.     CH: AHTLT58E606008833, ENG: 1NR0162819 
27. ]; 
28.  
29. UNQUALIFY *; 
30.  
31. NoConcatenate 
32.  
33. tabLinks: 
34. LOAD * Resident Table1; 
35. Join (tabLinks) 
36. LOAD * Resident Table2; 
37. Left Join (tabLinks) 
38. LOAD Table1.RecNo, 
39.      Table2.RecNo, 
40.      levenshtein(Table1.MARK1,Table2.MARK1) as LevDistMARK1, 
41.      levenshtein(Table1.MARK2,Table2.MARK2) as LevDistMARK2 
42. Resident tabLinks; 
43.  
44. DROP Fields Table1.MARK1, Table2.MARK1, Table1.MARK2, Table2.MARK2 From tabLinks; 

using this module / vbs-function:

1. '******************************************************************************************************** 
2. '* 
3. '*  VBScript function implementing Levenshtein-Distance-algorithm 
4. '*  source: http://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Levenshtein_distance#VBScript 
5. '* 
6. '******************************************************************************************************** 
7.  
8.  
9. Function levenshtein( a, b ) 
10.   Dim i,j,cost,d,min1,min2,min3 
11.  
12. ' Avoid calculations where there there are empty words 
13.   If Len( a ) = 0 Then levenshtein = Len( b 😞 Exit Function 
14.   If Len( b ) = 0 Then levenshtein = Len( a 😞 Exit Function 
15.  
16. ' Array initialization 
17.   ReDim d( Len( a ), Len( b ) ) 
18.  
19.   For i = 0 To Len( a 😞 d( i, 0 ) = i: Next 
20.   For j = 0 To Len( b 😞 d( 0, j ) = j: Next 
21.  
22. ' Actual calculation 
23.   For i = 1 To Len( a ) 
24.   For j = 1 To Len( b ) 
25.                         If Mid(a, i, 1) = Mid(b, j, 1) Then cost = 0 Else cost = 1 End If 
26.  
27.   ' Since min() function is not a part of VBScript, we'll "emulate" it below 
28.   min1 = ( d( i - 1, j ) + 1 ) 
29.   min2 = ( d( i, j - 1 ) + 1 ) 
30.   min3 = ( d( i - 1, j - 1 ) + cost ) 
31.  
32.   If min1 <= min2 And min1 <= min3 Then 
33.   d( i, j ) = min1 
34.   ElseIf min2 <= min1 And min2 <= min3 Then 
35.   d( i, j ) = min2 
36.   Else 
37.   d( i, j ) = min3 
38.   End If 
39.   Next 
40.   Next 
41.  
42.   levenshtein = d( Len( a ), Len( b ) ) 
43. End Function 

hope this helps

regards

Marco

[jagan]

Hi,

Check this there is an example in this link

Calling VB function (macro) in Load script to find common string length in two strings

Regards,

Jagan.

[#8]

Thank you all for your help!!!! I used Marcus_Sommer suggestion (PostReload-Trigger) and it's working!

[MarcoWedel]

please mark your thread as answered.

thanks

regards

Marco