Date: Thu, 15 Jul 1999 14:34:14 -0400
Reply-To: "John S. Painter" <painter@EMAIL.UNC.EDU>
Sender: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From: "John S. Painter" <painter@EMAIL.UNC.EDU>
Subject: Re: script to send output to a specific cell within an excel wor
ksheet
In-Reply-To: <C02FEF109018D3118C9900A024CDCB647B7BCC@HERMES>; from "painter"
at Thu Jul 15 14:34:14 1999
Content-Type: TEXT/PLAIN; CHARSET="US-ASCII"
This has worked, but there is one problem: the Excel shell (the destination) is
being renamed "output.xls" How can I prevent this from happening?
You mention objExcel.Run as a means of running a macro within Excel (I assume). I
have no idea of where to look in Excel to get the rest of what I need to know in
order to make this happen. Can you provide an example?
Very many TIA!
John Painter
-- Begin original message --
> From: "Bauer, John" <bauer@SPSS.COM>
> Date: Fri, 9 Jul 1999 12:45:41 -0500
> Subject: Re: script to send output to a specific cell within an excel wor
> ksheet
> To: SPSSX-L@VM.MARIST.EDU
> Reply-To: "Bauer, John" <bauer@SPSS.COM>
> Comments: To: SPSSX-L@LISTSERV.UGA.EDU
>
> The macro could be "migrated" into the SPSS script. After the text 'You
> could tell Excel to execute a macro here,' add the following:
>
> objExcelApp.Range("A1:L13").Select
> objExcelApp.Selection.Copy
> objExcelApp.Windows("L1SHELL_1.XLS").Activate
> objExcelApp.Range("AA2").Select
> objExcelApp.ActiveSheet.Paste
>
> Of course the whole thing could have been placed in a With block, in which
> case only leading periods need to be added.
>
> In general, arguments may need to be edited also, changing enumerations to
> their actual values and so on.
>
> If the macro is certain to exist in Excel, and its location in a workbook is
> known, the method objExcel.Run could instead be used. The macro name and
> any required parameters would need to be supplied; see the Excel VB Help for
> further information.
>
>
> John Bauer, Ph.D.
> SPSS Developer Support
>
> visit the SPSS Script eXchange:
> http://www.spss.com/software/spss/scriptexchange/
>
>
> -----Original Message-----
> From: John S. Painter [mailto:painter@EMAIL.UNC.EDU]
> Sent: Friday, July 09, 1999 8:22 AM
> To: SPSSX-L@LISTSERV.UGA.EDU
> Subject: Re: script to send output to a specific cell within an excel
> worksheet
>
>
> I can creae an Excel macro that copies from the new Excel output file to the
> one
> I need...but how can I include this new Excel macro in the existing spss to
> excel script? see the example below:
>
> Here is the begining of the spss script, mention is made of telling Excel to
> excute a macro: :
>
> 'Begin Description
> 'This script will export SPSS PivotTables into Excel using BIFF (Binary
> Interchange File Format).
> 'It will also paste charts. Each item is pasted into its own sheet.
> 'End Description
> '
> '******************************************************
> 'You may wish to edit the Excel Macro (below), which
> 're-formats the table after it is pasted into Excel
> '******************************************************
>
> Sub ExcelMacro()
> objExcelApp.Selection.AutoFormat Format:=&HFFFFEFC6, Number:=True, _
> Font:=True, Alignment:=True, Border:=True, Pattern:=True,
> Width:=True
>
> >>>>>>>>>'You could tell Excel to execute a macro here.<<<<<<<<<<<<<<
>
>
>
> End Sub
> '
>
> '******************************************************
>
> I need to insert this:
>
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 7/8/99 by School of Social Work
> '
>
> '
> Range("A1:L13").Select
> Selection.Copy
> Windows("L1SHELL_1.XLS").Activate
> Range("AA2").Select
> ActiveSheet.Paste
> End Sub
>
> How can I simply insert another excel macro into the existing script so the
> the
> new output will be copied and pasted to another worksheet??
>
> TIA,
>
> John P
>
> -- Begin original message --
>
> > From: "James B. Luther, Ph.D." <jluther@POP.NLCI.COM>
> > Date: Thu, 8 Jul 1999 14:49:46 -0500
> > Subject: Re: script to send output to a specific cell within an
> excel
> > worksheet
> > To: SPSSX-L@VM.MARIST.EDU
> > Reply-To: "James B. Luther, Ph.D." <jluther@POP.NLCI.COM>
> > Comments: To: "John S. Painter" <painter@EMAIL.UNC.EDU>
> >
> > You can set individual cells or ranges in Excel using the worksheet
> object.
> > For example, the following snipit takes values from the data cells in a
> SPSS
> > Pivot Table and puts them into cells in the Active worksheet in Excel.
> > (*Note I pulled this from an application I've written - may not run as is,
> > but does give the objects you need to reference*)
> >
> > Dim Cell_I as Long
> > Dim Cell_J as Long
> > Dim objDataCells As ISpssDataCells
> > Dim objExcelApp As Object
> >
> > For Cell_I = 0 to lngNumRows-1
> > For Cell_J = 0 To lngNumColumns -1
> >
> > ObjExcelApp.ActiveSheet.Cells(Cell_I,Cell_J).Value=
> objDataCells.ValueAt
> > (Cell_I,Cell_J)
> >
> > Next
> > Next
> >
> > James B. Luther, Ph.D.
> > Luther Consulting, LLC
> > jluther@lutherconsulting.com
> >
> > -----Original Message-----
> > From: John S. Painter <painter@EMAIL.UNC.EDU>
> > Newsgroups: bit.listserv.spssx-l
> > To: SPSSX-L@LISTSERV.UGA.EDU <SPSSX-L@LISTSERV.UGA.EDU>
> > Date: Thursday, July 08, 1999 3:42 PM
> > Subject: script to send output to a specific cell within an excel
> worksheet
> >
> >
> > >Hello,
> > >
> > >I need to send a table to a specific place in a Excel worksheet.
> Currently
> > we copy-and-paste, we
> > >would like to automate this process, since it must be done about 600
> times.
> > >
> > >I can use the "Export to excel.sbs" to send the table to a separate excel
> > file, but I really need to
> > >send it to a specific cell in an existing Excel file. Sending the table
> to
> > another file doesn't
> > >really solve the problem of cutting and pasting.
> > >
> > >John P
> > >
> >
>
> -- End original message --
>
-- End original message --
| 
