You need to add the excel refernce to Access. From Access VBA window

Tools - Reference - Microsft Excel 11.0 object library.

Or latest version on your PC.
11.0 is excel 2003
10.0 is excel 2002
9.0 is excel 2000

I figured out what was wrong at work. I was getting a read-only workbook
using createobject. I changed to getobject and the code is working except I
get a Pop up window asking if I want to save in Excel 5.0. I have excel 2002
at home where I'm working. the pop up window may be hidden behind other
window. If the code looks like it has stopped close some of the windows
until you find the pop up. I have to leave now. I'm not sure if you get the
pop up window since you are using excel 2000. I you get the pop up and want
me to fix the problem let me know and I will work on it tomorrow. I'm
SUMMING column G. You can change the column to any column and add additional
columns. I' also using column A to determine the last row. This can also be
changed.

Private Sub Command0_Click()


FName = "c:\temp\file.xls"
MyQuery = "Excel Exportb"
MyQuery = "AC_WC_DEMOG_CRSTAB Query"
DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:=MyQuery, _
OutputFormat:=acFormatXLS, _
OutputFile:=FName, _
AutoStart:=True

Set bk = GetObject(pathname:=FName)
With bk.Sheets(MyQuery)
LastRow = .Range("A" & 65536).End(xlUp).Row
NewRow = LastRow + 1
.Range("G" & NewRow).Formula = _
"=SUM(G2:G" & LastRow & ")"
End With
bk.Close savechanges:=True
Set obj = Nothing
End Sub


"microsoft" wrote:

Hi

This line of code is giving an error

"LastRow = .Range("A" & 65536).End(xlUp).Row"

Error - Run time error 1004
Application defined or object defined error

Hope to hear from you soon.

Thanks again

"microsoft" wrote:

Hi

I created an Access database which exports data to Excel. I would like
assistance in creating the required code which would allow me to sum some
selected columns automatically after the data is exported (kindly note that I
do not want any additional columns created to capture the total figure).

Note that the rows from the database may vary hence the excel rows will also
vary. Code to determine the row count would need to be included.

Thanks in advance