Any ideas why this doesnt work? (1 Viewer)

xlApp.Sheets("Sheet2").Select
xlApp.ActiveSheet.Range("A1").Select
xlApp.ActiveCell.Offset(2, 0).Cells.Select
xlApp.ActiveCell.CopyFromRecordset RsProj
xlApp.ActiveSheet.Range("N1").Select
xlApp.ActiveCell.Offset(2, 0).Cells.Select
xlApp.ActiveCell.CopyFromRecordset RsDebits
xlApp.ActiveSheet.Range("A1:N70").Select
xlApp.Selection.Copy
xlApp.Sheets("Sheet1").Select
xlApp.ActiveSheet.Range("A30").Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(2, 0).EntireRow.Select
xlApp.ActiveSheet.Paste
xlApp.Sheets("Sheet2").Select
xlApp.ActiveWindow.SelectedSheets.Delete
xlApp.Sheets("Sheet3").Select
xlApp.ActiveSheet.Range("A1:N10").Select
xlApp.Selection.Copy
xlApp.Sheets("Sheet1").Select
xlApp.ActiveSheet.Range("N29").Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(3, 0).EntireRow.Select
xlApp.Selection.End(xlToRight).Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(4, 0).EntireRow.Select
xlApp.Selection.End(xlToLeft).Select
xlApp.ActiveSheet.Paste

xlApp.ActiveSheet.Range("N29").Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(3, 0).EntireRow.Select
xlApp.Selection.End(xlToRight).Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(4, 0).Cells.Select



r = xlApp.ActiveCell.Select
xlApp.ActiveCell.Formula = "=SUM(N30:N" & r - 1 & ")"

did you dim xlApp?
did you create the excel object (Set xlApp = CreateObject("Excel.Application")?

Dave

The Sum Formula doesnt work

well i didnt put my whole code... this is the whole code...

Dim xlApp As Excel.Application
Dim xlwkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim objRST As Recordset
Dim rec As Recordset
Dim RsCntry As Recordset
Dim RsProj As Recordset
Dim RsDebits As Recordset
Dim RsCredits As Recordset
Dim lvlColumn As Integer
Dim i As Integer
Dim f As Variant
Dim startrange As Excel.Range
Dim firstAddress As Variant
Dim w As Object
Dim rsweek As Recordset
Dim LastRow As Long
Dim NewCell As Range
Dim intRow As Integer
Dim intCol As Integer
Dim r As Integer


'Create Sheets of Working Tables
Set rsweek = CurrentDb.OpenRecordset("Select week from week;")
Set rec = Application.CurrentDb.OpenRecordset("SELECT * FROM TDDebitNotes;")
Set RsCntry = CurrentDb.OpenRecordset("SELECT * FROM TD_DB3;")
While Not (rec.EOF)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
Set xlwkbk = xlApp.Workbooks.Open("C:\Input Files\DNote.xlt")

Set objRST = CurrentDb.OpenRecordset("SELECT TYPE,Org,FACCT,CSUB,P_S,SOBP,FPROJ,Cust,Cntry,iet,orgo,schan,Loc,bsac FROM " & rec!SOBPDEBIT & " ORDER BY TYPE;")
Set RsProj = CurrentDb.OpenRecordset("SELECT Proj FROM " & rec!SOBPDEBIT & " ORDER BY TYPE;")
Set RsDebits = CurrentDb.OpenRecordset("SELECT Debits FROM " & rec!SOBPDEBIT & " ORDER BY TYPE;")

xlApp.ActiveSheet.Range("C3").Value = RsCntry!Description
xlApp.ActiveSheet.Range("K8").Value = RsCntry!SOBP
xlApp.ActiveSheet.Range("K9").Value = RsCntry!Description
xlApp.ActiveSheet.Range("M4").Value = Format(Date, "DD/MM/YYYY")
xlApp.ActiveSheet.Range("B8").Value = rec!Description
xlApp.ActiveSheet.Range("B9").Value = rec!SOBP & " - " & rec!Prefix
xlApp.ActiveSheet.Range("C18").Value = "PSA CHARGES" & " - " & rsweek!Week
xlApp.ActiveSheet.Range("C25").Value = Format(Date, "MM/YYYY")
xlApp.ActiveSheet.Range("A30").CopyFromRecordset objRST

xlApp.Sheets("Sheet2").Select
xlApp.ActiveSheet.Range("A1").Select
xlApp.ActiveCell.Offset(2, 0).Cells.Select
xlApp.ActiveCell.CopyFromRecordset RsProj
xlApp.ActiveSheet.Range("N1").Select
xlApp.ActiveCell.Offset(2, 0).Cells.Select
xlApp.ActiveCell.CopyFromRecordset RsDebits
xlApp.ActiveSheet.Range("A1:N70").Select
xlApp.Selection.Copy
xlApp.Sheets("Sheet1").Select
xlApp.ActiveSheet.Range("A30").Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(2, 0).EntireRow.Select
xlApp.ActiveSheet.Paste
xlApp.Sheets("Sheet2").Select
xlApp.ActiveWindow.SelectedSheets.Delete
xlApp.Sheets("Sheet3").Select
xlApp.ActiveSheet.Range("A1:N10").Select
xlApp.Selection.Copy
xlApp.Sheets("Sheet1").Select
xlApp.ActiveSheet.Range("N29").Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(3, 0).EntireRow.Select
xlApp.Selection.End(xlToRight).Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(4, 0).EntireRow.Select
xlApp.Selection.End(xlToLeft).Select
xlApp.ActiveSheet.Paste

xlApp.ActiveSheet.Range("N29").Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(3, 0).EntireRow.Select
xlApp.Selection.End(xlToRight).Select
xlApp.Selection.End(xlDown).Select
xlApp.ActiveCell.Offset(4, 0).Cells.Select



r = xlApp.ActiveCell.Select
xlApp.ActiveCell.Formula = "=SUM(N30:N" & r - 1 & ")"

i thought i colored the error.. sorry about that... i cant get the sume function work.. i am in a certain cell where i want to put the sum of the lines above. if you see the attached excel you will see the difficulty there...

thanks guys...
i will check the Excel object but that works fine...

Also, where does the first error occur? When you say it doesn't work, what DOES it do?

Also, in reference to Dave's thing about declaring and instantiating the Excel object, if you are declaring it as:
Then you would set it as:
If you use
Then Dave's code:
would be the way to instantiate it.

I edited my previous post ...

You can't assign this
r = xlApp.ActiveCell.Select

as xlApp.ActiveCell.Select doesn't return an integer which is what r is expecting.

You should, however, be able to use:
r = xlApp.ActiveCell.Row

See if that helps.

thanks Bob... it worked like a charm...

thanks again