Calling VBA macros
Locked
 
|
Helloo,
Currently, I have numerous SPSS programs/macros which fire out excel output files. After running these programs I run a excel macro to apply various formatting and small tweaks to get the output files into a desirable format.
My questions is, instead of running the SPSS code first, followed by a VBA macro is it possible to do all this in one step? If anyone could outline the possible methods of achieving this that would be great. If not possible in one step then would there be a way to call a VBA macro from SPSS somehow to be run on specified excel file?
Much appreciated.
Jignesh
|
|
|
You can use the SCRIPT command to run the
VBA code that tweaks the Excel file. There is an example of
something similar on the SPSS Community website (www.ibm.com/developerworks/spssdevcentral).
Look for ExportTablesToExcelFiles in the Utilities Collection. It
first exports a batch of tables, and then it starts Excel and uses Excel
VBA apis to massage the exported files.
HTH, Jon Peck Senior Software Engineer, IBM [hidden email] 312-651-3435 From: J Sutar <[hidden email]> To: [hidden email] Date: 03/24/2011 03:59 AM Subject: [SPSSX-L] Calling VBA macros Sent by: "SPSSX(r) Discussion" <[hidden email]> Helloo, Currently, I have numerous SPSS programs/macros which fire out excel output files. After running these programs I run a excel macro to apply various formatting and small tweaks to get the output files into a desirable format. My questions is, instead of running the SPSS code first, followed by a VBA macro is it possible to do all this in one step? If anyone could outline the possible methods of achieving this that would be great. If not possible in one step then would there be a way to call a VBA macro from SPSS somehow to be run on specified excel file? Much appreciated. Jignesh |
|
|
Hi Jon,
What I have are not SPSS tables from .SPO outputs but excel files generated from SAVE TRANSLATE. I couldn't find the ExportTablesToExcelFiles file you detailed below. Having briefly worked with similar scripts that paste tables from .SPO to excel, I believe Excel needs to be already open?
My idea is to run SPSS macros which create Excel files saved in a particular folder and then another bit of code which can open these files and run necessary VBA macros which I have already created but at the moment I run manually...
Could you direct me to the script at developer work?
Many thanks,
Jignesh
On 24 March 2011 12:50, Jon K Peck <[hidden email]> wrote: You can use the SCRIPT command to run the VBA code that tweaks the Excel file. There is an example of something similar on the SPSS Community website (www.ibm.com/developerworks/spssdevcentral). Look for ExportTablesToExcelFiles in the Utilities Collection. It first exports a batch of tables, and then it starts Excel and uses Excel VBA apis to massage the exported files. |
|
|
Jon Peck Senior Software Engineer, IBM [hidden email] 312-651-3435 From: J Sutar <[hidden email]> To: Jon K Peck/Chicago/IBM@IBMUS, [hidden email] Date: 03/24/2011 08:50 AM Subject: Re: [SPSSX-L] Calling VBA macros Sent by: [hidden email] Hi Jon, What I have are not SPSS tables from .SPO outputs but excel files generated from SAVE TRANSLATE. I couldn't find the ExportTablesToExcelFiles file you detailed below. Having briefly worked with similar scripts that paste tables from .SPO to excel, I believe Excel needs to be already open? My idea is to run SPSS macros which create Excel files saved in a particular folder and then another bit of code which can open these files and run necessary VBA macros which I have already created but at the moment I run manually... >>>That is the sort of thing that the script I mentioned does. First it exports some tables to Excel files; then it opens Excel and merges these tables. All this from an SPSS Basic script. Could you direct me to the script at developer work? >>>Go to the SPSS Community site (www.ibm.com/developerworks/spssdevcentral) Click on the IBM SPSS Statistics link in the Welcome text. Click on Utilities for working with IBM SPSS Statistics (sort by Name when you get there) You will see ExportTablesToExcelFiles.zip in the list. You must be registered with developerWorks and logged in to download any files. Many thanks, Jignesh On 24 March 2011 12:50, Jon K Peck <peck@...> wrote: You can use the SCRIPT command to run the VBA code that tweaks the Excel file. There is an example of something similar on the SPSS Community website (www.ibm.com/developerworks/spssdevcentral). Look for ExportTablesToExcelFiles in the Utilities Collection. It first exports a batch of tables, and then it starts Excel and uses Excel VBA apis to massage the exported files. HTH, Jon Peck Senior Software Engineer, IBM peck@... 312-651-3435 From: J Sutar <jsutar@...> To: [hidden email] Date: 03/24/2011 03:59 AM Subject: [SPSSX-L] Calling VBA macros Sent by: "SPSSX(r) Discussion" <[hidden email]> Helloo, Currently, I have numerous SPSS programs/macros which fire out excel output files. After running these programs I run a excel macro to apply various formatting and small tweaks to get the output files into a desirable format. My questions is, instead of running the SPSS code first, followed by a VBA macro is it possible to do all this in one step? If anyone could outline the possible methods of achieving this that would be great. If not possible in one step then would there be a way to call a VBA macro from SPSS somehow to be run on specified excel file? Much appreciated. Jignesh |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |