Column Sum from Access to Excel

Question

I have the following code below in Excel. It works great but I want to use this same code in access so I can export data along with adding a sum row at the end but it errors out.

lastrow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 2).End(xlUp).Row

ThisWorkbook.Sheets("SHEET1").Range("B" & lastrow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("SHEET1").Range("B2:B" & lastrow))

lastrow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 3).End(xlUp).Row

ThisWorkbook.Sheets("SHEET1").Range("C" & lastrow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("SHEET1").Range("C2:C" & lastrow))

lastrow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 4).End(xlUp).Row

ThisWorkbook.Sheets("SHEET1").Range("D" & lastrow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("SHEET1").Range("D2:D" & lastrow))

lastrow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 5).End(xlUp).Row

ThisWorkbook.Sheets("SHEET1").Range("E" & lastrow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("SHEET1").Range("E2:E" & lastrow))

lastrow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 6).End(xlUp).Row

ThisWorkbook.Sheets("SHEET1").Range("F" & lastrow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("SHEET1").Range("F2:F" & lastrow))

ThisWorkbook.Sheets("SHEET1").Range("A" & lastrow + 1) = "Total:"

ThisWorkbook.Sheets("SHEET1").Range("A" & lastrow + 1).Font.Bold = True

ThisWorkbook.Sheets("SHEET1").Range("B" & lastrow + 1).Font.Bold = True

ThisWorkbook.Sheets("SHEET1").Range("C" & lastrow + 1).Font.Bold = True

ThisWorkbook.Sheets("SHEET1").Range("d" & lastrow + 1).Font.Bold = True

ThisWorkbook.Sheets("SHEET1").Range("e" & lastrow + 1).Font.Bold = True

ThisWorkbook.Sheets("SHEET1").Range("f" & lastrow + 1).Font.Bold = True

Answer 1

I changed both lines and now getting "run-time error 1004" Application-defined or object defined error.

LastRow = wks.Cells(wks.Rows.Count, 2).End(xlUp).Row

wks.range("B" & LastRow + 1) = objXLS.WorksheetFunction.Sum(wks.range("B2:B" & LastRow))

Answer 2

Access has no clue what ThisWorkbook is, only Excel does.

Have you tried something along the lines of (aircode):

lastrow = wks.Cells(wks.Rows.Count, 2).End(xlUp).Row

...

Where you replace

ThisWorkbook.Sheets("SHEET1")

by the variable you've already set to represent the sheet in question 

wks

Also, don't forget if you are using late binding (as you appear to be) then you need to have defined all the Excel constants you use within your code, such as: xlUp

Public Const xlUp = -4162

Then something like

ThisWorkbook.Sheets("SHEET1").Range("B" & lastrow + 1) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("SHEET1").Range("B2:B" & lastrow))

would become

wks.Range("B" & lastrow + 1) = objXLS.WorksheetFunction.Sum(wks.Range("B2:B" & lastrow))

Also, unless you are adding new rows ... there is no need to repeatitively recalculate the value of lastrow it will not have changed since the previous line.  You need only recalculate it if you effectively add or delete a row which you do not appear to be doing in the code supplied.

You can look at http://www.devhut.net/2012/04/19/ms-access-vba-export-records-to-excel/ for some sample code that might help you see how to code some of this properly.

Answer 3

I created a command button on a form.   Here is the rest of the code.  This code works in access but the other errors out.  "Method or data member not found".  It does not like .worksheet function.  ....but does work in Excel. 

Dim objXLS As Object

  Dim wks As Object

  Dim rsc As Recordset

  Dim idx As Long

  Set rsc = Me.RecordsetClone

  rsc.MoveLast

  rsc.MoveFirst

  Set objXLS = CreateObject("Excel.Application")

  objXLS.Workbooks.Add

  Set wks = objXLS.Worksheets(1)

  For idx = 0 To rsc.Fields.Count - 1

    wks.Cells(1, idx + 1).Value = rsc.Fields(idx).Name

  Next

  wks.Range(wks.Cells(1, 1), wks.Cells(1, rsc.Fields.Count)).Font.Bold = True

  wks.Range("A2").CopyFromRecordset rsc, rsc.RecordCount, rsc.Fields.Count

  'Autofit columns to see entire data

  wks.Range("A:M").Columns.AutoFit

  'Formatting Range of Cells

  wks.Range("E2:E5000").NUMBERFORMAT = "General"

  wks.Range("F2:F5000").NUMBERFORMAT = "0.00"

  wks.Range("G2:G5000").NUMBERFORMAT = "$#,##0.00"

  wks.Range("H2:H5000").NUMBERFORMAT = "$#,##0.00"

  wks.Range("I2:I5000").NUMBERFORMAT = "$#,##0.00"

  wks.Range("J2:J5000").NUMBERFORMAT = "$#,##0.00"

  wks.Range("K2:K5000").NUMBERFORMAT = "0%"

   'Inserting a Column at Column A

   wks.Range("A1").EntireColumn.Insert

Answer 4

Automating Excel from Access requires a few minor adjustments.  Have a look at...

Excel Manipulation from Access...

http://regina-whipp.com/blog/?p=691

Hopefully, it will point you in the right direction.

Answer 5

Hi. How exactly were you using the above code in Access? Were you using Excel Automation?