Date:         Thu, 14 Dec 2006 13:12:56 -0500
Reply-To:     John Birken <zbq5@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         John Birken <zbq5@CDC.GOV>
Subject:      Re: Passing Excel addresses rather than value
Content-Type: text/plain; charset=ISO-8859-1

Alan:

Thanks for your thoughts.

The essence of my question is how do you write an address rather
than a value to another sheet.

The routine writes numbers perfectly

Say OUT cell A1 gets the value 9 via the routine.


I want the OUT!A1 cell to permanently say  =IN!A1 which makes the
In values appear in out sheet without the routine(after the first run).

Put another way. A routine correctly fills the out sheet with values.
How do I make it fill 10,000s of addresses such as =IN!A1.
Typing is too risky and boring.
Something like changing the value variable term  to "=IN!A1" ?

TIA
John


________________________________________________________________________
I don't have time to dive into the particulars of your code below (trying
to reactivate my office and website). However, you are facing a couple of
challenges IMO. One is that the Excel VBA engine is COM based and COM is
prone with problems. The other is an object model that isn't that great.

Take the plunge when time permits into a .NET Excel model (www.aspose.com)
and it will make your life a lot easier. Read in SAS using OleDB and it
should come together.

If you are planning on being at SGF, I will be presenting a paper along
these lines. Doesn't get you out of the current bind immediately but when
you are dealing with volume, COM does weird things from my experience.

Alan

Alan Churchill
Savian "Bridging SAS and Microsoft Technologies"
www.savian.net



-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of John
Birken
Sent: Thursday, December 14, 2006 9:02 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Passing Excel addresses rather than value

Paul:

Thanks for the info; I've tried those sites with no luck.
It seems like passing an addresses as “  “ or creating a second object
Office.Excel.WorkBookWithNamedAddresses might do it.
Another way might be to write the routine below in SAS pick the addresses
from SAS, which would have all the correct excel addresses.
Maybe bring them in as a  named string portion might work. There are
approaches floating around - I can't quite put them together.

I've gotten clever SAS_L answers along these lines. Maybe such will  appear.


Thanks for your suggestions.

John






John -

You might have more luck in the Excel groups...

http://groups-beta.google.com/group/microsoft.public.excel/
http://groups-beta.google.com/group/microsoft.public.excel.misc/
http://groups-beta.google.com/group/microsoft.public.excel.programming/
http://groups-beta.google.com/group/microsoft.public.excel.worksheet.fun
ctions/

hth

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of John
Birken
Sent: Wednesday, December 13, 2006 2:27 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Passing Excel addresses rather than values

I output 20,000 SAS lines to excel input sheet (IN InS). Its values are
correctly linked to an excel OUT (OS) sheet.  For ~ 10 pages linking the
correct IN values to OUT sheet positions can be done with Out!A1 = In!A1.
For 200+ of pages this becomes error prone.

Some of the 200 page OUT sheet location rows are not adjacent. The correct
out row order might be 1,2,5,8, 11 .....

The following routine smartly recognizes the correct rows and passes the
20,000 values from IN to OUT in seconds(well more like .25 of a minute).


Sub In2Out()
' In2Out Macro
' Macro recorded 5/42006 by John Birken
 Dim OS, InS As Worksheet
 Set OS = Sheets("Out")
 Set InS = Sheets("In")
    lastrow = OS.Cells(Rows.Count, "A").End(xlUp).Row
    datarow = 1
 For i = 10 To lastrow
    If Left(OS.Cells(i, "A"), 4) = "...." Then
        InS.Range(InS.Cells(datarow, "A"), InS.Cells(datarow, "E")).Copy
Destination:=OS.Cells(i, "C")
        datarow = datarow + 1
    End If
 Next i
 End Sub


Leave it it to a customer not to want a neat routine. He wants the
Out!A1 =
In!A1 relations for 200s of pages (truth IS stranger than fiction).
I'm seeking a way to alter the line (or region)

InS.Range(InS.Cells(datarow, "A"), InS.Cells(datarow, "E")).Copy
Destination:=OS.Cells(i, "C")

to write addresses rather than values.
The line sais increment InS row datarow range col "A" through "E".  Copy
this range to OUT starting at Out col "C"

The routine is to write an address RATHER than a values from IN to OUT.
Ideally this will permanently write the relations Out!A1= IN!A1,
Out!A7 = IN!A2..... calculated by the routine. The routines passes VALUES
to correct locations (use ""   "" ?). How do I  pass and permanetly
write
the correct LOCATIONS.

T.I.A
John