Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Formula in Excel via Access/VBA (XP SP 2)

    I am attempting to insert a formula in a spreadsheet via vba. I recorded a macro in Excel, copied, pasted and made some simple edits. However, I am getting an error when trying to insert the formula (.ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])")

    Can anyone point me in the right direction?

    Function CreateSpreadsheet(strSourceName As String, strFileName As String, strWorksheet As String)
    Dim xlWrkbk As Excel.Workbook
    Dim xlChartObj As Excel.Chart
    Dim xlSourceRange As Excel.Range
    Dim xlColPoint As Excel.Point
    Dim xlApp As Excel.Application
    On Error GoTo Err_CreateSpreadsheet

    ' Create an Excel workbook file based on the
    ' object specified in the second argument.
    'THIS CREATES THE SPREADSHEET
    ' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    strSourceName, strFileName, False

    DoCmd.OutputTo acOutputReport, strSourceName, acFormatXLS, strFileName

    ' Create a Microsoft Excel object.
    Set xlApp = CreateObject("Excel.Application")

    ' Open the spreadsheet to which you exported the data.

    Set xlWrkbk = xlApp.Workbooks.Open(strFileName)

    ' Determine the size of the range and store it.
    Set xlSourceRange = xlWrkbk.Worksheets(strWorksheet).Range("a1").Curre ntRegion

    xlWrkbk.Worksheets(1).Cells.Select
    ''''''''''''''''''''''''
    With xlWrkbk.Worksheets(1)
    With .Cells.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic

    End With
    Dim r As Long
    ' Max row number
    Dim m As Long
    Dim Cell As String
    ' Last row
    m = .Range("A65536").End(xlUp).Row
    r = m
    Cell = "I" & r
    .Range("I2").Select
    ' Error on following line ugh!
    .ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])"
    ' Object doesn't support this property or method
    .Selection.AutoFill Destination:=Range("I2:" & Cell), Type:=xlFillDefault

    .Range("A2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9, 10, 12, 13, 14, 16, 17)
    .Columns.AutoFit

    'Delete second total row
    m = .Range("A65536").End(xlUp).Row
    r = m
    .Range("A" & r).EntireRow.Delete
    End With

    ''''''''''''''''''''''''''''''''''''''''''''''''
    Exit_CreateSpreadsheet:

    Set xlSourceRange = Nothing
    Set xlColPoint = Nothing
    Set xlChartObj = Nothing
    ' Close and save the workbook
    xlWrkbk.Close SaveChanges:=True
    Set xlWrkbk = Nothing
    ' Quit Excel
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

    Err_CreateSpreadsheet:
    MsgBox CStr(Err) & " " & Err.Description
    Resume Exit_CreateSpreadsheet

    End Function


    Thanks,
    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula in Excel via Access/VBA (XP SP 2)

    Try replacing

    .Range("I2").Select
    ' Error on following line ugh!
    .ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])"

    with

    .Range("I2").FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])"

    or with

    .Range("I2").FormulaR1C1 = "=MIN(RC[-2],RC[-1])"

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Formula in Excel via Access/VBA (XP SP 2)

    Thanks Hans!

    Worked very well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •