Write Data File Path back to Dataset with Script

 

 

Question

Is there a way to return a value (string) from a script call?

I have a script that returns the file location of the active data set (actually, of the earliest opened data set - A.B.), but I want to assign it to a spss variable (and have it recalculate every time I run the syntax).

If I run the script (below) as script I can get the result (in a msgbox), but I'm not sure how to attach it to a spss variable

Something like:

string location(a255).

Comp location = script '[path]'.

Comment

There is probably no way to assign script result to a variable directly in the same instruction of calling the script. But scripts can interact (to some extend) with data in the dataset, as well as generating and running syntax. This gives a number of ways to return data file path back to dataset. Though none of them is ideal :(

Solution 1

The example below gets the file path and generates syntax that:

  1. Creates string variable Location
  2. Writing file path to it
  3. Executes the syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
'#Language "WWB-COM"

Option Explicit

Function GET_LOCATION() As String
'Benjamin Shinar. 2017.09.04 - script get file location
'Anton Balabanov. 2017.12.10 - write path back to spss dataset
	Dim objDataDoc As ISpssDataDoc
	Dim TEMP_LOCATION As String

	'Get the Designeted data
    Set objDataDoc=objSpssApp.Documents.GetDataDoc(0)
	TEMP_LOCATION=objDataDoc.GetDocumentPath
	'MsgBox(TEMP_LOCATION)
	objSpssApp.ExecuteCommands "STRING Location (A255)." & vbCrLf & "COMPUTE Location = '" & TEMP_LOCATION & "'." & vbCrLf & "EXECUTE.", False
	GET_LOCATION=TEMP_LOCATION
End Function

Sub Main()
	GET_LOCATION()
End Sub

Here is the demo syntax from wich the script above is called. It assumes the script was saved to the path specified in SCRIPT command. You have to bear in mind that syntax generated by the script is performed asynchronously (independently) with the calling syntax. That is, if your calling syntax continue to produce some other actions after SCRIPT command, then syntax generated by the script may potentially conflict with them. To put it simpler, the Location variable will be created only when the remaining of the calling syntax is finished. That's the major limitation of this solution.

23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
* Encoding: UTF-8.
* This is demo of how VB script can write data file path back to dataset.
DATASET CLOSE ALL.

DATA LIST LIST /a (F1.0).
BEGIN DATA
1
2
3
END DATA.

SAVE OUTFILE='C:/temp/testdata.sav'.

* Calling the script below will result in writing the path above to the
* new variable Location in the dataset.
SCRIPT 'C:/Users/balab/Documents/SaveDataFileLocationToVariable.wwd'.

Solution 2

It has the same limitations as the first one with respect to asynchronous running with the syntax. But it's a bit more flexible in terms of manipulation of the path information. This way script creates macro variable !fileloc with file path. So you may either create a string variable with it, or use it multiple times in the syntax as you need.

Script:

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
'#Language "WWB-COM"

Option Explicit

Function GET_LOCATION() As String
'Benjamin Shinar. 2017.09.04 - script get file location
'Anton Balabanov. 2017.12.10 - write path back to spss dataset
	Dim objDataDoc As ISpssDataDoc
	Dim objSPSSInfo As ISpssInfo

	Dim TEMP_LOCATION As String

	'Get the Designeted data
	Set objSPSSInfo = objSpssApp.SpssInfo

    Set objDataDoc=objSpssApp.Documents.GetDataDoc(0)
	TEMP_LOCATION=objDataDoc.GetDocumentPath
	'MsgBox(TEMP_LOCATION)
	objSpssApp.ExecuteCommands "DEFINE !fileloc()" & vbCrLf & "'" &TEMP_LOCATION & "'" & vbCrLf & "!ENDDEFINE.", False

	GET_LOCATION=TEMP_LOCATION
End Function

Calling syntax:

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
* Encoding: UTF-8.
* This is demo of how VB script can write data file path back to dataset.
DATASET CLOSE ALL.
OUTPUT CLOSE ALL.

DATA LIST LIST /a (F1.0).
BEGIN DATA
1
2
3
END DATA.

SAVE OUTFILE='C:/temp/testdata5.sav'.

SCRIPT 'C:/Users/balab/Documents/SaveDataFileLocationToVariable.wwd'.

Later, in another syntax run within SPSS session, you may use the created macro as you like. Say, to create string variable with the path in it:

77
78
79
STRING Location (A255).
COMPUTE Location = !fileloc.
EXECUTE.

Solution 3

If you need to run script and the remaining syntax in one run, i.e to allow later commands to manipulate the path, you may try to impute file path from the script to Data Editor 'on the fly', i.e. via Clipboard. Using clipboard is generally to very reliable way to automate things, but it worth to try.


 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
'#Language "WWB-COM"

Option Explicit

Function GET_LOCATION() As String
'Benjamin Shinar. 2017.09.04 - script get file location
'Anton Balabanov. 2017.12.10 - write path back to spss dataset
	Dim objDataDoc As ISpssDataDoc
	Dim objSPSSInfo As ISpssInfo

	Dim TEMP_LOCATION As String

	'Get the Designeted data
	Set objSPSSInfo = objSpssApp.SpssInfo

    Set objDataDoc=objSpssApp.Documents.GetDataDoc(0)
	TEMP_LOCATION=objDataDoc.GetDocumentPath
	'MsgBox(TEMP_LOCATION)

	Clipboard(TEMP_LOCATION)
	objDataDoc.SelectCells("Location", "Location", 1,1)
	objDataDoc.Paste

	GET_LOCATION=TEMP_LOCATION
End Function

Sub Main()
	GET_LOCATION()
End Sub

Of course, you'll need to create Location variable in advance. And you may have to wait a while to see the result.


109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
* Encoding: UTF-8.
* This is demo of how VB script can write data file path back to dataset.
DATASET CLOSE ALL.
OUTPUT CLOSE ALL.

DATA LIST LIST /a (F1.0).
BEGIN DATA
1
2
3
END DATA.

SAVE OUTFILE='C:/temp/testdata5.sav'.



STRING Location (A255).
SCRIPT 'C:/Users/balab/Documents/SaveDataFileLocationToVariable.wwd'.
EXECUTE.

The file path should be in the first case of the Location variable, like this:

Data Editor fragment with variable Location containing file path

Solution 4

More reliable yet complex solution is to prepare COMPUTE instruction by the script and put it to a file to a known place, then read and execute this file from syntax.

129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
'#Language "WWB-COM"

Option Explicit

Function GET_LOCATION() As String
'Benjamin Shinar. 2017.09.04 - script get file location
'Anton Balabanov. 2017.12.10 - write path back to spss dataset
	Dim objDataDoc As ISpssDataDoc
	Dim objSPSSInfo As ISpssInfo

	Dim TEMP_LOCATION As String

	'Get the Designeted data
	Set objSPSSInfo = objSpssApp.SpssInfo

    Set objDataDoc=objSpssApp.Documents.GetDataDoc(0)
	TEMP_LOCATION=objDataDoc.GetDocumentPath
	'MsgBox(TEMP_LOCATION)

	Open "c:\temp\filepath.sps" For Output As #1
	Print #1, "COMPUTE Location = '" & TEMP_LOCATION & "'."
	Close #1
	Wait(1)

	GET_LOCATION=TEMP_LOCATION
End Function

Sub Main()
	GET_LOCATION()
End Sub

Syntax. You may notice double call of the script. This is on purpose. Despite syncronous execution of the syntax and script, the INSERT command seems to be run before file descriptors from the script are closed. As a result, INSERT either not seeing file at all (if it was just created) or seeing the old content of the file. Double running the script ensure the file exist and contains the latest path detected.

160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
* Encoding: UTF-8.
* This is demo of how VB script can write data file path back to dataset.
DATASET CLOSE ALL.
OUTPUT CLOSE ALL.

DATA LIST LIST /a (F1.0).
BEGIN DATA
1
2
3
END DATA.

SAVE OUTFILE='C:/temp/testdata12.sav'.



STRING Location (A255).

SCRIPT 'C:/Users/balab/Documents/SaveDataFileLocationToVariable.wwd'.
SCRIPT 'C:/Users/balab/Documents/SaveDataFileLocationToVariable.wwd'.

INSERT FILE = 'C:/temp/filepath.sps' SYNTAX = INTERACTIVE.

EXECUTE.
Related pages

...

Navigate from here