[AccessD] Export to Excel via VBA, Naming Cells

Forgot to add that this is on Access XP, Excel XP. 

Larry Mrazek
LCM Research, Inc.
www.lcm-res.com
lmrazek at lcm-res.com
ph. 314-432-5886
fx. 314-432-3304

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawrence Mrazek
Sent: Friday, March 17, 2006 10:37 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Export to Excel via VBA, Naming Cells

Hi: 

I'm working on a fairly complex routine that exports sales data from Access
into Excel. 

Basically, the Excel report will have multiple worksheets, each containing a
salesman's summary sales data (about 15-20 salesmen), plus one worksheet
with company totals. 

The client wants all calculations to happen in Excel, and since I can't
predict where the data will end up in Excel, I can't really hard code any
formulas that refer to the sales data. 

I haven't had any trouble creating the worksheets/exporting the raw data via
a VBA routine, but I'm stuck on the calculation front.

Can I dynamically name cells/ranges as build the Excel report? I I'd like to
be able to use named ranges for these calculations, as I'll need to refer to
them to build my totals, but have been having a bit of trouble getting them
to work. 

I'd like to be able to assign the names as I build the spreadsheet, but I'm
not quite "getting" the syntax of the naming convention or something.

I've added a sample below:

                While Not rsSales.EOF
                    xlWs.Cells(rownum, 1) = rsSales.Fields("SalesRP")
                    xlWs.Cells(rownum, 2) = rsSales.Fields("salesrepName")
                    xlWs.Cells(rownum, 3) = rsSales.Fields("ProductGroup")
                    xlWs.Cells(rownum, 4) = rsSales.Fields("Item")
                    xlWs.Cells(rownum, 5) =
rsSales.Fields("itemdescription")
                    xlWs.Cells(rownum, 6) = rsSales.Fields("month")
                    RangeName = rsSales.Fields("SalesRP") &
rsSales.Fields("item") & rsSales.Fields("month")
                    CellLocation = rownum & 6 & ":" & rownum & 6
' Just testing to see if I can get the naming working.                   
xlWb.Names.Add Name:=RangeName, RefersTo:=CellLocation, Visible:=True    '
"=" & "!$" & rownum & "$" & 6 & ":" & rownum & "$" & 6
                    xlWs.Cells(rownum, 7) = rsSales.Fields("KGsLastYr")
                    xlWs.Cells(rownum, 8) = rsSales.Fields("KgsCurrYr")
                    xlWs.Cells(rownum, 9) = rsSales.Fields("SalesCurrYr")
                    xlWs.Cells(rownum, 10) = rsSales.Fields("SalesLastYr")
                    xlWs.Cells(rownum, 11) = rsSales.Fields("marginCurrYr")
                    xlWs.Cells(rownum, 12) = rsSales.Fields("MarginLastYr")
                    rownum = rownum + 1
                    rsSales.MoveNext
                Wend

Any suggestions? Also, if anyone specializes in this sort of thing (Access
to Excel automation), I might need to farm this out. 

Thanks in advance. 

Larry Mrazek
LCM Research, Inc.
www.lcm-res.com
lmrazek at lcm-res.com
ph. 314-432-5886
fx. 314-432-3304



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com