How to Fix 'Application-Defined or Object-defined error'

Question

I'm trying to converting an excel file into a text file but every time I run the code, I get 'Application-defined or object-defined error'

Thanks in Advance!!!!!

Sub ExceltoText()
    `Declaration
    Dim FileName As String, sLine As String, Deliminator As String
    Dim LastCol As Integer, LastRow As Integer, FileNumber As Integer

    `Location and File Name
    FileName = "C:\Users\Admin\Documents\New folder\ExceltoText.txt"
    Deliminator = "|"
    LastCol = ActiveSheet.Cells.SpecialCells(xlcelltypelast).Column
    LastRow = ActiveSheet.Cells.SpecialCells(xlcelltypelast).Row
    FileNumber = FreeFile

    `To create txtfile
    Open FileName For Output As FileNumber

    `To read data from excel
    For i = 1 To LastRow
    For j = 1 To LastCol
    If j = LastCol Then
    sLine = sLine & Cells(i, j).Value
    Else
    sLine = sLine & Cells(i, j).Value & Deliminator
    End If
    Next j

    `Wrighting data
    Print #FileNumber, sLine
    sLine = ""
    Next i
    Close #FileNumber
    MsgBox "The text file has been generated"

End Sub

score 1 · Answer 1 · answered Mar 27 '19 at 05:33

You are getting error because of

LastCol = ActiveSheet.Cells.SpecialCells(xlcelltypelast).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlcelltypelast).Row

There is no constant like xlcelltypelast. What you need is xlCellTypeLastCell

I would recommed reading up on Range.SpecialCells method (Excel)

Also if you want to find the last cell in a row or column then you may want to use .Find as shown Here

How to Fix 'Application-Defined or Object-defined error'

1 Answers1