Home>
Software/Language

Software used: Access 2016
Language: VBA

Errors encountered
WsObj.cells (3, 1), cells (5, j-1) .AutoFilter


In this line, the range is specified only with Cells, so a syntax error has occurred.
How should I specify the range?

Code listing
Public Sub Format ()
  Dim AppObj As Object 'Excel.Application object declaration
  Dim WBObj As Object 'Excel.Workbook object declaration
  Dim WsObj As Object 'Excel.WorkSheet object declaration
  Dim Path, Exf As String
  Dim i, j As Integer: i = 1: j = 1
  'Specify Excel file path
  Path = "Application.CurrentProject.Path \"
  '* Notation when the Excel file is in the same folder as the Access file used
  Exf = Dir (Path&"* .xlsx")
  '
  Do
    'Runtime binding
    Set AppObj = CreateObject ("Excel.Application")
    'Open the workbook for editing
    Set WBObj = AppObj.WorkBooks.Open (Exf)
    'Set the worksheet to be edited
    Set WsObj = WBObj.Worksheets (1)
    AppObj.Visible = False 'Do not show Excel application
    'Repeat process
    Do
      'Repeat process
      Do
        'Set the format for the text [Start abbreviation]
        With WsObj.cells (i, j) .Font
          'Change font
          .Name = "MS P Gothic"
          'Change text size
          .Size = 16
          'Bold text
          .Bold = True
          'Change the text color
          .Color = RGB (0, 0, 0)
        'Omitted description End
        End With
        'If current row is even
        If j Mod 2 = 0 Then
          'Change cell background color
          WsObj.Range (cells (i, j)). Interior.Color = RGB (153, 204, 255)
        'End branch processing
        End If
        'Move to the next column
        i = i + 1
      'Repeat until there is a blank cell in the current row
      Loop Until WsObj.cells (i, j) = ""
      'Add row count
      j = j + 1
    'Repeat until next row A column cell is empty
    Loop Until WsObj.cells (1, j) = ""
    'Set filters in the 3rd to 5th columns
    'A syntax error will occur on this line.
    WsObj.cells (3, 1), cells (5, j-1) .AutoFilter
    'Save and close Excel
    WBObj.Save 'Save the workbook
    WBObj.Close 'Close workbook
    AppObj.Quit
  Exf = Dir ()
  'Repeat until no more files
  Loop Until Exf = ""
End Sub