copy and paste specific cells from EXCEL into specific SPSS cells

Question

I am trying to copy data from excel into SPSS. I have 80 excel files and need to copy and paste the same cells from each workbook into my main SPSS database. Is there any way I can do this aside from manually copying and pasting each individual value?

Answer 1

Another approach if you have at least SPSS Statistics 17 would be to use the SPSSINC PROCESS FILES extension command available from the SPSS Community website (www.ibm.com/developerworks/spssdevcentral). This can loop over a set of files and apply SPSS syntax to each, including GET DATA to read the files. So it could read through the Excel files you want, open them in SPSS Statistics, and then select out whatever portion of the dataset is appropriate.

This requires the Python Essentials/plugin for your Statistics version also available from the Community website, but no skills other than with SPSS syntax are required.

HTH, Jon Peck

Answer 2

I use SPSS at my work, and we import data from excel on a daily basis. We do a save-as to tab delimited text format, and use a SPSS Syntax script to import the text files. Since you have so many excel files you could automate the whole "save-as" process with some vba if that would make things easier.

UPDATE

Here is a small bit of code to loop through (Excel) files in a directory, open each one, then save as a tab delimited text file. Change the .xls designation if your files are .xlsx or .xlsm, also you'll want to change the folder paths to something meaningful...

Sub TestMe()
Dim wb As Workbook
Dim INfldr As String
Dim OUTfldr as String

OUTfldr = "C:\WhereIPutStuff\"
INfldr = "C:\WhereIKeepStuff\"
strFNAME = Dir(INfldr & "*.xls")

i = 1

Do
Set wb = Application.Workbooks.Open(INfldr & strFNAME)
wb.SaveAs Filename:=OUTfldr & "OutputFile(" & i & ").txt", _
          FileFormat:=xlText, _
          CreateBackup:=False

wb.Close False

i = i + 1  
strFNAME = Dir
Loop Until strFNAME = ""

End Sub