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 understood 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 the Excel spreadsheet
Dim objWorkbook As Object: Set objWorkbook = objExcel.Workbooks.Open(pathWorkbook)
- Declare the Excel spreadsheet
Dim ws As Object and select its name Set ws = objWorkbook.Sheets(nomePlanilha) can be the name between quotation marks ("Spreadsheet 1") or index number (1,2,3, etc..)
- With the Ws spreadsheet declared, you can use it to perform the operations in Excel-VBA
- To close properly
objWorkbook.Close (True) True saves the spreadsheet and False does not save.
Note
To see the window of Debug.Print, enable Excel VBA to Immediate Verification.
If the spreadsheet doesn’t exist yet, 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 the Excel objects in the references.
1st Open Access Visual Basic
2nd Enter Tools -> References...
3rd Enable Microsoft Excel Object as shown below:
Good is that to declare a Range, you should name the Workbook and Worksheet to avoid some errors, if the code is inside the Excel spreadsheet. For example:
Set ws = Sheets("Planilha1")and usews.range("A1").Select. See an example of how to declare, may be the lack of statement of which book should be opened and which spreadsheet. You are programming only for the Excel spreadsheet or to interact with other software?– danieltakeshi
@danieltakeshi posts the answer Daniel to be marked as answered.
– Evert
@danieltakeshi, I actually have access queries that generate my spreadsheet and a module responsible for exporting and formatting it. The generated spreadsheet is not empty. I have Workbook and Worksheet declared. I will edit the question for better understanding
– Alexsander Caproni
It is always good to avoid the use of Select and use Ranges, I will check the spreadsheet better later and notice here. And the declaration of variables? how is done?
Dim ... As Object?– danieltakeshi
Dim pathWorkbook As String Dim dic As Object Set dic = Createobject("Scripting.Dictionary") pathWorkbook = path & filename
– Alexsander Caproni