VBA-application definition or object definition error

Question

VBA-application definition or object definition error

I am trying to retrieve the amount of rows from an excel spreadsheet to format it, but I am running into 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?

0

vba visual-basic-6 excel-vba

Author: Alexsander Caproni, 2017-09-22

Source

1 answers

score 0 · Accepted Answer

I got the correct number of lines with this test code, some changes can be made for you to utilize. 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 CORRECTLY DECLARE

create an Excel application object Dim objExcel As Object: Set objExcel = CreateObject("Excel.Application")
create String in file path name Dim pathWorkbook As String: Let pathWorkbook = "Caminho"
open Excel spreadsheet Dim objWorkbook As Object: Set objWorkbook = objExcel.Workbooks.Open(pathWorkbook)
declare the Excel spreadsheet Dim ws As Object and select the same Set ws = objWorkbook.Sheets(nomePlanilha) can be the name in quotation marks ("worksheet 1") or the index number (1,2,3,etc.)
with the ws sheet declared, You can use it to perform operations in Excel-VBA
to close correctly objWorkbook.Close (True) True saves the sheet and False does not save.

Note

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

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

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

Edit:

What You must have forgotten is to enable Excel objects in references.

1o Open Access Visual Basic

2o go to tools - > references...

3o enable Microsoft Excel Object as shown below: