EXPORT OUTPUT to Excel not working
Locked
EXPORT OUTPUT to Excel not working
 
|
I have a series of reports that I run every quarter. To produce those
reports, I run a bunch of custom tables in SPSS, use OMSEND to save the output, and then OUTPUT EXPORT with the MODIFYSHEET option to paste the table output into already existing sheets in an excel workbook (.xls). That workbook contains a sheet for each output table and a bunch of extra sheets with the "pretty" tables that I actually PDF and share. The "pretty" tables contain cell references that pull in the data from the output sheets. This process has been working well for years but they recently moved us to SPSS 24 and Windows 10 and now it won't work. I was told by a researcher at another agency that they had to switch from .xls to .xlsx when moving to SPSS 24, so I thought that was the issue. I edited my code (example syntax below and attached) to include .xlsx and converted my excel workbook to .xlsx. However, this did not fix the issue. If I run the code shown below, the first part will produce the desired custom tables and save them in two .spv files (Output1 and Output2); however, the OUTPUT EXPORT commands do not work as expected. If I run them and then try and open the excel workbook, I get an error message from excel stating, "We found a problem with some content in 'Example Workbook.xlsx'. Do you want us to try and recover as much as we can?" Clicking, Yes does not help. See below and attached for my syntax. I've also attached example data and an example excel workbook. Does anyone know what I might be doing wrong? I would love it if I could still export to .xls like before. That would save me a lot of time changing syntax and converting excel workbook file types, but if that's not possible, I at least need to figure out how to export into .xlsx Thank you for your time and assistance. ~Jennifer Example_Data.sav <http://spssx-discussion.1045642.n5.nabble.com/file/t341920/Example_Data.sav> Example_Syntax.sps <http://spssx-discussion.1045642.n5.nabble.com/file/t341920/Example_Syntax.sps> Example_Workbook.xlsx <http://spssx-discussion.1045642.n5.nabble.com/file/t341920/Example_Workbook.xlsx> FILE HANDLE Desktop /NAME="C:\Users\XS7341\Desktop". GET FILE = "Desktop/Example Data.sav". *Run Output1. OMS /SELECT TABLES /IF COMMANDS = ['Ctables'] /EXCEPTIF SUBTYPES= ['Notes' 'Case Processing Summary'] /DESTINATION FORMAT = SPV OUTFILE = 'Desktop\Output1.spv' VIEWER = YES. CTABLES /VLABELS VARIABLES=State Race DISPLAY=BOTH /TABLE State BY Race [COUNT F40.0, ROWPCT.COUNT PCT40.1] /CATEGORIES VARIABLES=State ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=BEFORE /CATEGORIES VARIABLES=Race ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER /CRITERIA CILEVEL=95. OMSEND. *Run Output2. OMS /SELECT TABLES /IF COMMANDS = ['Ctables'] /EXCEPTIF SUBTYPES= ['Notes' 'Case Processing Summary'] /DESTINATION FORMAT = SPV OUTFILE = 'Desktop\Output2.spv' VIEWER = YES. CTABLES /VLABELS VARIABLES=State Sex DISPLAY=BOTH /TABLE State [C] BY Sex [COUNT F40.0, ROWPCT.COUNT PCT40.1] /CATEGORIES VARIABLES=State ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=BEFORE /CATEGORIES VARIABLES=Sex ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER /CRITERIA CILEVEL=95. OMSEND. *Exporting into Example Workbook --- THIS IS THE PART THAT DOES NOT WORK AS EXPECTED. *Export Output1. OUTPUT OPEN FILE = 'Desktop\Output1.spv'. OUTPUT EXPORT /XLSX DOCUMENTFILE='Desktop\Example Workbook.xlsx' OPERATION=MODIFYSHEET SHEET='Output1' LOCATION=STARTCELL('A1'). OUTPUT CLOSE *. *Export Output2. OUTPUT OPEN FILE = 'Desktop\Output2.spv'. OUTPUT EXPORT /XLSX DOCUMENTFILE='Desktop\Example Workbook.xlsx' OPERATION=MODIFYSHEET SHEET='Output2' LOCATION=STARTCELL('A1'). OUTPUT CLOSE *. -- Sent from: http://spssx-discussion.1045642.n5.nabble.com/ ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|
|
Perhaps this is just an artifact of the post, but the data file and Excel references in the syntax have names that do not match the files that were posted. The names in the syntax have a blank after Example, but the files supplied have "_". After I fixed that, the code ran properly, but I am using the current version of Statistics, which is 27.0.1. On Tue, May 25, 2021 at 3:25 PM Jennifer Owens <[hidden email]> wrote: I have a series of reports that I run every quarter. To produce those |
Re: EXPORT OUTPUT to Excel not working
|
|
The original file names did not have a “_”. I think that’s a product of uploading the file to the group. The original excel file just has a space between the two words in the file name.
I have SPSS 24 with both fix packs. Maybe this is something that was fixed in newer versions.
I’d be interested to hear from someone who might have 24. I think it will be quite some time before we get another update.
~Jennifer
From: Jon Peck <[hidden email]>
Sent: Tuesday, May 25, 2021 5:47 PM To: Owens, Jennifer L <[hidden email]> Cc: SPSS List <[hidden email]> Subject: Re: [SPSSX-L] EXPORT OUTPUT to Excel not working Perhaps this is just an artifact of the post, but the data file and Excel references in the syntax have names that do not match the files that were posted.
The names in the syntax have a blank after Example, but the files supplied have "_".
After I fixed that, the code ran properly, but I am using the current version of Statistics, which is 27.0.1.
On Tue, May 25, 2021 at 3:25 PM Jennifer Owens <[hidden email]> wrote:
I have a series of reports that I run every quarter. To produce those |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |