Applying Trim to Cells...Value generates application defined error

Question

The below code should import a text file to Excel.

I want to trim some columns as some of the fields have blank spaces instead of nulls, which gives me problems when importing to an access as the data type for those fields are dates.

I run into a application defined error in the Trim portion of the code.

Dim xl As excel.Application
Dim wbk As excel.Workbook
Dim wst As excel.Worksheet
Dim DFPath
'DFPath reference folder, not the file
DFPath = Forms!DailyFileMacro!OutFilePath
Dim DFname
DFname = Forms!DailyFileMacro!OutFileName

Set xl = CreateObject("Excel.Application")
With xl
    .Visible = True
    Set wbk = .Workbooks.Add
    Set wst = wbk.Worksheets(1)
    With wst.QueryTables.Add(Connection:="TEXT;" & Forms!DailyFileMacro!InFilePath & Forms!DailyFileMacro!InFileName, Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 2, 1, 1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 3, 3, _
        1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    Dim rowcount As Long
    rowcount = Range("A1").SpecialCells(xlCellTypeLastCell).Row
    For i = 2 To rowcount
        If Cells(i, 19).Value Like "Adjustment*" Then
            Cells.Offset(0, 1).Value = Trim(Cells.Offset(0, 1).Value)
            Cells.Offset(0, 2).Value = Trim(Cells.Offset(0, 2).Value)
        End If
    Next i
End With

End With
wbk.SaveAs Filename:=(DFPath & DFname), FileFormat:=56
wbk.Close SaveChanges:=False
xl.Quit

Answer 1

Based on If Cells(i, 19).Value, I think you meant the two Trim lines to be:

Cells(i, 20).Value = Trim(Cells(i, 20).Value)
Cells(i, 21).Value = Trim(Cells(i, 21).Value)

The Cells calls as is (without a row/column reference or parent Range reference) refer to the entire Worksheet, so Offsetting by 1 or 2 columns is impossible and will always throw a run-time error.