export from access to excel , add/calculate totals 1

[jozino01]

hi,
i have code that exports access data (query) into excel. it works ok, but i need to add below a few columns total value (sum). the number of rows is not constant. i am not sure what is the best way to do it. any sugestion, please?

here is my existing code:
...
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "REVENUE DETAIL", Me.Text22, True, "REVENUE"
...
With objXL.Application
.Visible = True
.Workbooks.Open Me.Text22
'Format Worksheets
.Sheets("REVENUE").Select
.Range("A1:V1").Select
.Selection.AutoFormat Format:=xlRangeAutoFormatClassic2, Number:=True, Font _
:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
.ActiveWindow.LargeScroll ToRight:=-1
.Columns("U:U").Select
.Selection.NumberFormat = "$#,##0.00"
.Cells.Select
.Cells.EntireColumn.AutoFit
End With

 

[PHV]

Have a look at the FAQ area in this forum to discover at least 2 different ways for finding the last used row of a sheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

hi, i was searching for similar issues but couldn't find answer. i have to look again then...

 

[PHV]

http://www.tek-tips.com/faqs.cfm?fid=2112

http://www.tek-tips.com/faqs.cfm?fid=2115

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

If you PULL the data using MS Query, via Data/get External Data...

there is a switch in Data Range Properties to Fill down formulas in columns adjacent to data.

NO CODE REQUIRED!

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[jozino01]

to PHV
yes, i found some code and tried that, but i am getting en error message "object variable or with block variable not set"
i guess i miss some syntax...?

Dim lrow As Long
...
Set objXL = CreateObject("Excel.Application")

With objXL.Application
...
.lrow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
.Range("F" & lrow + 1).Formula = "=SUM(F1:F" & lrow - 1 & ")"
...

 

[PHV]

Set objXL = CreateObject("Excel.Application")
With objXL
...
lrow = .ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
.Range("F" & lrow + 1).Formula = "=SUM(F1:F" & lrow - 1 & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

yes, that makes a diference
thanks