Exporting to Pre-Formatted Excel Worksheets

[Krinsky, Alan-2]

Does anyone know of a way to export SPSS data into already formatted Excel Worksheets?

I have learned how to do many things over recent months, including how to use Python with SAVE TRANSLATE (ODBC) to divide a large data file into dozens of smaller files and export each dataset into (multiple sheets in) a separate Excel Workbook. I am at the point where I create the Excel files first from a template and batch rename them, and use the Python to export the data.

What I cannot manage to do is export to a pre-formatted worksheet. And by pre-formatted I mean both cell formatting and page setup print formatting. I have tried using both \REPLACE and \APPEND with SAVE TRANSLATE, and neither seems to do the job. The best I can do so far is export to one sheet in the workbook and then copy and paste the data into another (pre-formatted) sheet, or paste formatting from the pre-formatted sheet over the exported data. The former method works better in terms of preserving the formatting but is also more subject to copy and paste errors. Also, the problem is the need, time, and error risk of opening, fixing, and saving many dozens of files by formatting, deleting tabs, etc. My aim is to automate the entire process, so the Excel files could be produced ready to use and print from. Is this possible?

Thank you for any assistance with this!

Alan

Alan D. Krinsky  PhD, MPH
Senior Data Analyst
UMass Memorial Health Care
38 Oak Ave.
Worcester, MA 01605
Phone: [phone]
Fax: [phone]
E-mail: [hidden email]

---

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, transmission, re-transmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

[Francien Berndsen]

Hi Alan,

I recognize your problem. I have a preformatted Excel file and I export the data from SPSS to a new sheet inside the preformatted excel file. But I solved the copy and paste (and delete) problem with a macro (VBA) I have made in Excel. Do you repeat it often? Than a macro could solve your problem. It's not ideal, but you can't make  copy and paste mistakes anymore. 

Kind regards,

Francien

Op 29 mrt. 2012 om 16:57 heeft "Krinsky, Alan" <[hidden email]> het volgende geschreven:

Does anyone know of a way to export SPSS data into already formatted Excel Worksheets?

I have learned how to do many things over recent months, including how to use Python with SAVE TRANSLATE (ODBC) to divide a large data file into dozens of smaller files and export each dataset into (multiple sheets in) a separate Excel Workbook. I am at the point where I create the Excel files first from a template and batch rename them, and use the Python to export the data.

What I cannot manage to do is export to a pre-formatted worksheet. And by pre-formatted I mean both cell formatting and page setup print formatting. I have tried using both \REPLACE and \APPEND with SAVE TRANSLATE, and neither seems to do the job. The best I can do so far is export to one sheet in the workbook and then copy and paste the data into another (pre-formatted) sheet, or paste formatting from the pre-formatted sheet over the exported data. The former method works better in terms of preserving the formatting but is also more subject to copy and paste errors. Also, the problem is the need, time, and error risk of opening, fixing, and saving many dozens of files by formatting, deleting tabs, etc. My aim is to automate the entire process, so the Excel files could be produced ready to use and print from. Is this possible?

Thank you for any assistance with this!

Alan

Alan D. Krinsky  PhD, MPH
Senior Data Analyst
UMass Memorial Health Care
38 Oak Ave.
Worcester, MA 01605
Phone: [phone]
Fax: [phone]
E-mail: [hidden email]

---

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, transmission, re-transmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.