[AccessD] Excel Code

Another thing...and probably THE thing

You are not referencing to the excel object.

Cells(Rows.Count,"A").End(xlUp).Row

Should be

objExcelWorkSheet.cells
And rows() should be 
objExcelWorkSheet.rows.

Before you can create the objExcelWorkSheet object you need to create a 
objExcelWorkbook

And before you can create an objExcelWorkbook you need to create a 
objExcel 


Be sure to set all objects to nothing when finishing your code.

Try not to terminate and restart code execution while developing, but
first try to nicely close all objects.







-----Oorspronkelijk bericht-----
Van: Erwin Craps 
Verzonden: maandag 4 augustus 2003 22:51
Aan: Access Developers discussion and problem solving
Onderwerp: RE: [AccessD] Excel Code


Wauw Bob
Thats some piece of code you got there :-)

I been strugling months trying to create some nicely formated Excel
sheets from Access. Pretty heavy stuff...

First of all, I noticed, while developing that Access looses sometimes
and pretty often  partialy the reference to Excel. Some primary objects
where still working but Cells just constantly gave errors. Saving the
code, closing excel, resetting (stop button) in MSA_VBE was needed to
regain control over the object. Sometimes Excel needed to be terminated
by means of the taskmanager. Closing and reopening access was sometimes
needed. There is an issue with early and late bounding to, in early
bounding I had trouble makeing a object twice to Excel during same
session. Some functions would fail. Late bounding is adviced by MS to
Excel. 
This was all true for MSA2K and Excel 2K.

Ok now for some code.
Please note that I adapted some code to make it readable, the code
beneath will  not work on its on, I supose what you are looking for is
the worksheetfunction or formula property.


'With Cells you can reference by number, thus making programmable
variables able. lngRowposFrom = 1 lngRowposTO = 9 lngRowpos = 10
lngColPos = 1 With ObjExcelSheet
	.Cells(lngRowpos, lngColPos).HorizontalAlignment = xlHAlignLeft
End with


'Calculate Column totals
If itmDLFormat.CalculateColumnSum = True Then
With .Cells(lngRowpos , lngColPos)
	'This line result in a figure (only one line needed)
	.Value = WorksheetFunction.Sum(.Range(.Cells(lngRowposFrom ,
lngColPos), .Cells(lngRowposTO, lngColPos )))
	'This line result in a formula in excel (only one line needed)
	.Formula = "=SUM(" & objExcelWorkSheet.Cells(lngRowposFrom  ,
lngColPos ).Address _
	& ":" _
	& objExcelWorkSheet.Cells(lngRowposTO, lngColPos ).Address _
	& ")"
End With

Erwin


-----Oorspronkelijk bericht-----
Van: Bob Heygood [mailto:bheygood at abestsystems.com] 
Verzonden: maandag 4 augustus 2003 21:36
Aan: Access Developers discussion and problem solving
Onderwerp: RE: [AccessD] Excel Code


Hey Philippe,
Wow I thought I did but sure enough I removed it to accommodate a user
that has a different version of Excel.

Still would love for an Excel guru to help me make the code work.


Thanks,

Bob

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Philippe PONS
Sent: Monday, August 04, 2003 12:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel Code


Hi Bob,

If you can't gain access to the Excel object model from Access, it is
because you did not declare a reference to the Excel library from
Tool/References...

Do it, and you will get it!

Regards,
Philippe.


----- Original Message -----
From: "Bob Heygood" <bheygood at abestsystems.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Monday, August 04, 2003 9:10 PM
Subject: [AccessD] Excel Code


> Hey to the Group,
>
> I have found some great code to go to an excel sheet and total a 
> column.
It
> works in excel fine. But when I try and adapt it to automate excel 
> from Access, I get errors for the "Cells(xxxx)" object. I have been 
> successful
in
> instancing the Excel application and the Excel workbook. I have tried 
> to understand the cell thing but cannot get the Excel object model to 
> show up in the object browser in Access.
>
> So, how do I get this to work in Access. And, how can one explore the
excel
> model from Access.
>
> Code:
>
> Dim cRows As Long
>
>     cRows = Cells(Rows.Count,"A").End(xlUp).Row
>     Cells(cRows+1,"A").FormulaR1C1 = "=SUM(R1C1:R[-1]C1)"
>
> This is for column A, change the "A" and C1 if a different column
required.
>
> Code End:
>
> TIA
>
> Bob
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


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


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