Mixing
Problem exporting Excel Sheet data to SPSS Syntax format (.sps) using Excel VBA
up vote
2
down vote
favorite
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 yesterday
Imran Malek
1,5121714
asked yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
2
down vote
favorite
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 yesterday
Imran Malek
1,5121714
asked yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
yesterday
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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 yesterday
Imran Malek
1,5121714
asked yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 yesterday
Imran Malek
1,5121714
asked yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
Imran Malek
1,5121714
asked yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
Imran Malek
1,5121714
edited yesterday
Imran Malek
1,5121714
edited yesterday
Imran Malek
1,5121714
1,5121714
asked yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked yesterday
Jeff P
212
asked yesterday
Jeff P
212
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
yesterday
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
yesterday
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
yesterday
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
yesterday
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
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 yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
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 yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
1
down vote
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 yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
1
down vote
up vote
1
down vote
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 yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered yesterday
Jeff P
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered yesterday
Jeff P
212
answered yesterday
Jeff P
212
212
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Jeff P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Jeff P is a new contributor. Be nice, and check out our Code of Conduct.
Jeff P is a new contributor. Be nice, and check out our Code of Conduct.
Jeff P is a new contributor. Be nice, and check out our Code of Conduct.
Jeff P is a new contributor. Be nice, and check out our Code of Conduct.
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
yesterday