Problem exporting Excel Sheet data to SPSS Syntax format (.sps) using Excel VBA
I am currently working on a Excel macro where an output sheet needs to be exported to *.sps (SPSS Syntax) format.
Here is the exact code that I use which works 80% of the time:
Sub Print_CM_to_sps()
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(ThisWorkbook.Path & "All_CM_Edits.sps") Then
Set LogData = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 2, True)
LogData.Close
Set LogDatas = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
LogDatas.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
Else
Set NewFile = FSO.CreateTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
NewFile.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
End If
End Sub
As mentioned, this approach should be ok but I am confused why 20% of the time my created *.sps file is not readable by SPSS. When such anomaly occurs, the *.sps file can be properly viewed with any other editing tools (e.g. notepad/npp/sublime/UE/etc.) but when opened in IBM SPSS Statistics it is only read as one line of Chinese characters.
To give an example, these are the lines that are contained in my *.sps file which are very simple command lines actually:
Rename Variables (AU3a01 = AU3a_1). Rename Variables (AU3a02 =
AU3a_2). Rename Variables (AU3a03 = AU3a_3). Rename Variables (AU3a04
= AU3a_4). Rename Variables (AU3a05 = AU3a_5). Rename Variables (AU3a06 = AU3a_6). Variable Labels AU3a_1 "Brand A". Variable Labels
AU3a_2 "Brand B". Variable Labels AU3a_3 "Brand C". Variable Labels
AU3a_4 "Brand D". Variable Labels AU3a_5 "Brand E". Variable Labels
AU3a_6 "Brand F". Value Labels /AU3a_1 to AU3a_6 1 "1 - Terrible" 2
"2" 3 "3" 4 "4" 5 "5" 6 "6" 7 "7" 8 "8" 9 "9" 10 "10 - Perfect".
Execute.
Here's a screenshot of the error that I mentioned:
SPSS Error Display
Hope to hear from anyone that could help me.
Many thanks!
excel vba excel-vba spss
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
add a comment |
I am currently working on a Excel macro where an output sheet needs to be exported to *.sps (SPSS Syntax) format.
Here is the exact code that I use which works 80% of the time:
Sub Print_CM_to_sps()
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(ThisWorkbook.Path & "All_CM_Edits.sps") Then
Set LogData = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 2, True)
LogData.Close
Set LogDatas = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
LogDatas.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
Else
Set NewFile = FSO.CreateTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
NewFile.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
End If
End Sub
As mentioned, this approach should be ok but I am confused why 20% of the time my created *.sps file is not readable by SPSS. When such anomaly occurs, the *.sps file can be properly viewed with any other editing tools (e.g. notepad/npp/sublime/UE/etc.) but when opened in IBM SPSS Statistics it is only read as one line of Chinese characters.
To give an example, these are the lines that are contained in my *.sps file which are very simple command lines actually:
Rename Variables (AU3a01 = AU3a_1). Rename Variables (AU3a02 =
AU3a_2). Rename Variables (AU3a03 = AU3a_3). Rename Variables (AU3a04
= AU3a_4). Rename Variables (AU3a05 = AU3a_5). Rename Variables (AU3a06 = AU3a_6). Variable Labels AU3a_1 "Brand A". Variable Labels
AU3a_2 "Brand B". Variable Labels AU3a_3 "Brand C". Variable Labels
AU3a_4 "Brand D". Variable Labels AU3a_5 "Brand E". Variable Labels
AU3a_6 "Brand F". Value Labels /AU3a_1 to AU3a_6 1 "1 - Terrible" 2
"2" 3 "3" 4 "4" 5 "5" 6 "6" 7 "7" 8 "8" 9 "9" 10 "10 - Perfect".
Execute.
Here's a screenshot of the error that I mentioned:
SPSS Error Display
Hope to hear from anyone that could help me.
Many thanks!
excel vba excel-vba spss
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
Since later in the process you are going to use SPSS syntax, you could consider automating this syntax production in SPSS instead of VBA (can be done easily)
– eli-k
Nov 19 '18 at 6:43
add a comment |
I am currently working on a Excel macro where an output sheet needs to be exported to *.sps (SPSS Syntax) format.
Here is the exact code that I use which works 80% of the time:
Sub Print_CM_to_sps()
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(ThisWorkbook.Path & "All_CM_Edits.sps") Then
Set LogData = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 2, True)
LogData.Close
Set LogDatas = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
LogDatas.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
Else
Set NewFile = FSO.CreateTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
NewFile.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
End If
End Sub
As mentioned, this approach should be ok but I am confused why 20% of the time my created *.sps file is not readable by SPSS. When such anomaly occurs, the *.sps file can be properly viewed with any other editing tools (e.g. notepad/npp/sublime/UE/etc.) but when opened in IBM SPSS Statistics it is only read as one line of Chinese characters.
To give an example, these are the lines that are contained in my *.sps file which are very simple command lines actually:
Rename Variables (AU3a01 = AU3a_1). Rename Variables (AU3a02 =
AU3a_2). Rename Variables (AU3a03 = AU3a_3). Rename Variables (AU3a04
= AU3a_4). Rename Variables (AU3a05 = AU3a_5). Rename Variables (AU3a06 = AU3a_6). Variable Labels AU3a_1 "Brand A". Variable Labels
AU3a_2 "Brand B". Variable Labels AU3a_3 "Brand C". Variable Labels
AU3a_4 "Brand D". Variable Labels AU3a_5 "Brand E". Variable Labels
AU3a_6 "Brand F". Value Labels /AU3a_1 to AU3a_6 1 "1 - Terrible" 2
"2" 3 "3" 4 "4" 5 "5" 6 "6" 7 "7" 8 "8" 9 "9" 10 "10 - Perfect".
Execute.
Here's a screenshot of the error that I mentioned:
SPSS Error Display
Hope to hear from anyone that could help me.
Many thanks!
excel vba excel-vba spss
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
I am currently working on a Excel macro where an output sheet needs to be exported to *.sps (SPSS Syntax) format.
Here is the exact code that I use which works 80% of the time:
Sub Print_CM_to_sps()
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(ThisWorkbook.Path & "All_CM_Edits.sps") Then
Set LogData = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 2, True)
LogData.Close
Set LogDatas = FSO.OpenTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
LogDatas.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
Else
Set NewFile = FSO.CreateTextFile(ThisWorkbook.Path & "All_CM_Edits.sps", 8, True)
For i = 2 To lstRow
NewFile.WriteLine ThisWorkbook.Sheets("Data").Range("A" & i).value
Next i
End If
End Sub
As mentioned, this approach should be ok but I am confused why 20% of the time my created *.sps file is not readable by SPSS. When such anomaly occurs, the *.sps file can be properly viewed with any other editing tools (e.g. notepad/npp/sublime/UE/etc.) but when opened in IBM SPSS Statistics it is only read as one line of Chinese characters.
To give an example, these are the lines that are contained in my *.sps file which are very simple command lines actually:
Rename Variables (AU3a01 = AU3a_1). Rename Variables (AU3a02 =
AU3a_2). Rename Variables (AU3a03 = AU3a_3). Rename Variables (AU3a04
= AU3a_4). Rename Variables (AU3a05 = AU3a_5). Rename Variables (AU3a06 = AU3a_6). Variable Labels AU3a_1 "Brand A". Variable Labels
AU3a_2 "Brand B". Variable Labels AU3a_3 "Brand C". Variable Labels
AU3a_4 "Brand D". Variable Labels AU3a_5 "Brand E". Variable Labels
AU3a_6 "Brand F". Value Labels /AU3a_1 to AU3a_6 1 "1 - Terrible" 2
"2" 3 "3" 4 "4" 5 "5" 6 "6" 7 "7" 8 "8" 9 "9" 10 "10 - Perfect".
Execute.
Here's a screenshot of the error that I mentioned:
SPSS Error Display
Hope to hear from anyone that could help me.
Many thanks!
excel vba excel-vba spss
excel vba excel-vba spss
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
edited Nov 19 '18 at 11:57
Imran Malek
1,6112714
1,6112714
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
asked Nov 19 '18 at 6:17
Jeff PJeff P
212
212
Since later in the process you are going to use SPSS syntax, you could consider automating this syntax production in SPSS instead of VBA (can be done easily)
– eli-k
Nov 19 '18 at 6:43
add a comment |
Since later in the process you are going to use SPSS syntax, you could consider automating this syntax production in SPSS instead of VBA (can be done easily)
– eli-k
Nov 19 '18 at 6:43
Since later in the process you are going to use SPSS syntax, you could consider automating this syntax production in SPSS instead of VBA (can be done easily)
– eli-k
Nov 19 '18 at 6:43
Since later in the process you are going to use SPSS syntax, you could consider automating this syntax production in SPSS instead of VBA (can be done easily)
– eli-k
Nov 19 '18 at 6:43
add a comment |
1 Answer
1
active
oldest
votes
k, thanks for promptly answering.
Did a deeper dig and came with a solution that involves enabling library references as the attached image. It is done by going to Tools>>References in the module editing window.
Oddly enough, I have been searching for solutions for 3 weeks now and luckily came across this solution just today. :)
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53369247%2fproblem-exporting-excel-sheet-data-to-spss-syntax-format-sps-using-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
k, thanks for promptly answering.
Did a deeper dig and came with a solution that involves enabling library references as the attached image. It is done by going to Tools>>References in the module editing window.
Oddly enough, I have been searching for solutions for 3 weeks now and luckily came across this solution just today. :)
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
add a comment |
k, thanks for promptly answering.
Did a deeper dig and came with a solution that involves enabling library references as the attached image. It is done by going to Tools>>References in the module editing window.
Oddly enough, I have been searching for solutions for 3 weeks now and luckily came across this solution just today. :)
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
add a comment |
k, thanks for promptly answering.
Did a deeper dig and came with a solution that involves enabling library references as the attached image. It is done by going to Tools>>References in the module editing window.
Oddly enough, I have been searching for solutions for 3 weeks now and luckily came across this solution just today. :)
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
k, thanks for promptly answering.
Did a deeper dig and came with a solution that involves enabling library references as the attached image. It is done by going to Tools>>References in the module editing window.
Oddly enough, I have been searching for solutions for 3 weeks now and luckily came across this solution just today. :)
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
answered Nov 19 '18 at 10:34
Jeff PJeff P
212
212
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53369247%2fproblem-exporting-excel-sheet-data-to-spss-syntax-format-sps-using-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Since later in the process you are going to use SPSS syntax, you could consider automating this syntax production in SPSS instead of VBA (can be done easily)
– eli-k
Nov 19 '18 at 6:43