VBA - Application definition or object definition error

Question

VBA - Application definition or object definition error

Navigation

#1 by (0 votes)

0

I'm trying to retrieve the amount of rows from an excel worksheet to format it, however I'm encountering the application definition or object definition error. Below is the code snippet.

Private Function formataPlanilha(pathWorkbook As String, dic As Object)

objExcel = CreateObject("Excel.Application")

' ABRE ARQUIVO
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(pathWorkbook)
objExcel.Application.Visible = False

objExcel.Sheets.Add.Name = "TD"
objExcel.Sheets(dic("Sheet_Name")).Select

' INICIO DA FORMATAÇÃO
objExcel.Cells.Select
objExcel.Selection.Font.Name = "Calibri"
objExcel.Selection.Font.Size = 9
objExcel.Cells.EntireColumn.AutoFit
objExcel.Selection.RowHeight = 13.5
objExcel.Selection.AutoFilter

' RECUPERA QUANTIDADE DE LINHAS
objExcel.Range("A1").Select
objExcel.Selection.End(xlDown).Select
maxRows = objExcel.ActiveCell.Row

I believe the error is occurring in xlDown. Can anyone help me?

visual-basic-6 vba excel-vba

asked by anonymous 22.09.2017 / 15:11

1 answer

App crashing when trying to save a file using ObjectOutputStream How do I adjust the size of items in a TListView

score 0 · Accepted Answer

I got the correct number of lines with this test code, some changes can be made for you to use. The code was made in Excel-VBA 7.1. From what I understand the spreadsheet already exists.

Dim objExcel As Object: Set objExcel = CreateObject("Excel.Application")
Dim pathWorkbook As String: pathWorkbook = "C:\Users\nome_usuario\Desktop\Pasta1.xlsm"
Dim objWorkbook As Object: Set objWorkbook = objExcel.Workbooks.Open(pathWorkbook)
Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
Dim ws As Worksheet
Dim nomePlanilha As String
Dim maxRows As Long

objExcel.Visible = False

nomePlanilha = "Planilha1"
Set ws = objWorkbook.Sheets(nomePlanilha)
' INICIO DA FORMATAÇÃO
With ws
    objExcel.Cells.Font.Name = "Calibri"
    objExcel.Cells.Font.Size = 9
    objExcel.Cells.EntireColumn.AutoFit
    objExcel.Cells.RowHeight = 13.5
    objExcel.Cells.AutoFilter
End With

' RECUPERA QUANTIDADE DE LINHAS
maxRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Debug.Print maxRows

On Error Resume Next
objWorkbook.Close (True)
On Error Goto 0
Set objWorkbook = Nothing

STEPS TO DECLARE CORRECTLY

Create an Excel Application object Dim objExcel As Object: Set objExcel = CreateObject("Excel.Application")

Create the String in the path name of the file Dim pathWorkbook As String: Let pathWorkbook = "Caminho"

Open the Excel worksheet Dim objWorkbook As Object: Set objWorkbook = objExcel.Workbooks.Open(pathWorkbook)

Declare the Excel worksheet Dim ws As Object and select the name of the same Set ws = objWorkbook.Sheets(nomePlanilha) can be the name in quotation marks ("Sheet1") or the index number (1,2,3, etc.)

With the spreadsheet ws declared, you can use it to perform the operations in Excel-VBA

To close correctly objWorkbook.Close (True) True saves the worksheet and False does not save.

Note

To see the Debug.Print window, enable Excel Verification in VBA.

If the spreadsheet does not already exist, then you need to add it:

Dim ws As Worksheet
With objWorkbook
    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = "TD"
End With

Edit:

What you may have forgotten is to enable Excel objects in references.

1st Open Access Visual Basic

2nd Enter Tools - > References ...

3rd Enable Microsoft Excel Object as shown below: