Sum() command - Automation in Access 2007

[pmcgreevy]

This question is about MS Automation in MS Access 2007. Hope I have the right forum.

I've been developing in Foxpro & VFP for 20 years, but this is my first attempt at an app in Access.

The following code works fine until the oExcel.ActiveCell.Value = mTotal entry on the next to last line.

The field I am concerned with is exported as a General field. I tried changing the NumberFormat to Number with two decimals and negative numbers in red, but didn’t help.

I have received every error # in the book trying to get the Sum() to work for me, from mismatched types to a missing object in command.

This attempt comes to rest on the line following the last line of data, in the correct column, but enters 0.00 in the field instead of the sum. I just ran it with Debug.Print mTotal just before it posts mTotal to the worksheet. It’s value truly is 0, so the Sum() command is definitely not working the way I need it to. But I get no errors from the app with this code.

I have five of these files to run daily, each of which has a different number of records, hence the mRange variables.

I know it will be something simple, but can anyone help me with this Sum() command?

I am calling it from MS Acess 2007 and Excel is from the same suite. Also running Win XP w/SP3.

Any Help would be appreciated!

Pat
----------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Command5_Click()

Dim mTotal As Double
Dim mRange As String
Dim mRange2 As String
Dim mRange3 As String

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "AjWork", "C:\ReqsOut\ajOut.xls", False, ""

Set oExcel = CreateObject("Excel.Application")
oExcelSheetsInNewWorkbook = 1
Set oWorkbook = oExcel.Workbooks.Open("C:\ReqsOut\ajh82FGU.xls")

oExcel.Visible = True
' x = MsgBox(" Enable Macros Now ", 48, " Enable Macros ")
mLastRow = oExcel.ActiveSheet.UsedRange.Rows.Count
Debug.Print mLastRow
oExcel.EnableEvents = True

' Set AutoFit
mlcRows = "2:" & Trim(Str(mLastRow))
oExcel.Rows(mlcRows).Select
oExcel.Selection.EntireColumn.AutoFit

' Bold the first Row
mlcRows = "1:1"
oExcel.Rows(mlcRows).Select
oExcel.Selection.Font.Bold = True

' Set FreezePanes
oExcel.Range("A2").Select
oExcel.ActiveWindow.FreezePanes = True

oExcel.Range("AH1").Select
oExcel.ActiveCell.FormulaR1C1 = "EXT_PRICE"
. Change the rest of the column headers
.
.
mRange = "AH2:AH" & Trim(Str(mLastRow))
mRange2 = "AH2:AH" & Trim(Str(mLastRow + 1))
mRange3 = "AH" & Trim(Str(mLastRow + 1))

oExcel.Range(mRange2).Select
'oExcel.Selection.NumberFormat = "0.00;[Red]0.00"
'mTotal = Evaluate(oExcel.Sum(Val(mRange)))

oExcel.Range(mRange3).Select
oExcel.ActiveCell.Value = mTotal

End Sub

 

[PHV]

Why not simply this ?
oExcel.Range(mRange3).Formula = "=Sum(" & mRange & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pmcgreevy]

Thanks, PHV. At least you got me started in the right direction.

Column AH comes over as a General field, but with text data in it. So, I first changed the NumberFormat to Currency.

However, now I have the number data stored as text in a Currency field. If I run a macro to:
Range("AH2:AH43").value = Range("AH2:AH43").value
it straightens it out and resets the text data as numeric, but won't do it programitically from Access.

Now I have:

mRange = "AH2:AH" & Trim(Str(mLastRow))
mRange2 = "AH2:AH" & Trim(Str(mLastRow + 1))
mRange3 = "AH" & Trim(Str(mLastRow + 1))
oExcel.Range(mRange2).Select
oExcel.Selection.NumberFormat = "#,##0.00"
oExcel.Range(mRange2).Value = oExcel.Range(mRange2).Value
oExcel.Range(mRange3).Select
oExcel.Range(mRange3).FormulaR1C1 = "=oExcel.Sum(" & mRange & ")"

which displays #NAME? in the cell, and displays oExcel.Sum('AH2':'AH41') in the F(x) text box up top. Still no sum.
Anyone have any ideas?

Pat

 

[PHV]

Seems you didn't read my previous post carefully ...
What about this ?

mRange = "AH2:AH" & mLastRow
mRange2 = "AH2:AH" & (mLastRow + 1)
mRange3 = "AH" & (mLastRow + 1)
oExcel.Range(mRange2).NumberFormat = "#,##0.00"
oExcel.Range(mRange2).Value = oExcel.Range(mRange2).Value
oExcel.Range(mRange3).Formula = "=Sum(" & mRange & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pmcgreevy]

PH - Thanks.

I went with strictly your code for that section, and with only a couple of little tweaks, it works!

Had to change the reinitialization line to use mRange instead of mRange2. It was adding an unwanted row at the bottom which had text in a numeric field.

Really fouled up the Sum() command.

Thanks again.

Pat