Export to excel but clear worksheet first

[mercapto]

I am using a code to export a query to excel, but I find that if I export less rows that are on the worksheet, the old exceeding rows remain on the worksheet.
I need the whole table to be updated.

Is there any way to clear the table before writing the data? Or maybe is there any exporting method to export blank rows that could overwrite the old ones? Any other option?



Deleting the file is not an option for me, I need to keep the same file and structure.

[ItsMe]

I would guess the only way would be to Automate Excel. I would use early binding and the beginning of the code would look something like ...

Dim xlApp As Excel.Application
Dim myBook As Workbook
Dim mySheet As Worksheet
Set xlApp = New Excel.Application
Set myBook = xlApp.Workbooks.Open(strPath & "Template\Product_By_PO.xlsx")
Set mySheet = myBook.Sheets.Item(1) 'Grab the first worksheet

[mercapto]

Quite not satisfied with the solution but I finally used this:

Function BorraExcel(ByVal strRutaHojaExcel As String)

Dim wb As Object
Dim XLapp As Object
Dim tbl As Object
     
Set XLapp = CreateObject("Excel.Application")
XLapp.Visible = False
Set wb = XLapp.Workbooks.Open(strRutaHojaExcel, True, False)
wb.Sheets(1).Range("A1").Select
    
Set tbl = XLapp.ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
XLapp.Selection.ClearContents
tbl.Range("A1").Select

wb.Close True
Set wb = Nothing
XLapp.Quit
Set XLapp = Nothing

End Function

Thanks for answering.

[ItsMe]

I am not an expert but this is the way I understand it. So I will explain it how I understand it and anybody is welcome to chime in if I am not correct.

If you create a reference to Microsoft Office XX.X Object and employ early binding, intellisence will display the object's members.

For instance, the code example I provided uses axlApp as a class and it has access to the objects within. xlApp represents the application, Excel.

Here, xlApp is instantiated and initialized in one line.
Set xlApp = New Excel.Application

From here you can create new objects in memory and access their members. Intellisence will help you by displaying what is available within your objects.
Set myBook = xlApp.Workbooks.Open(strPath & "Template\Product_By_PO.xlsx")


When instantiating the myBook object, you can view objects and classes available within the xlApp class by using the dot notation.
Set myBook = xlApp.

If you simply instantiate your object as a generic object (dim MyObj as Object), you will not have access to intellisense. This is known as late binding. Early binding is when you define the object at the time of instantiation. When using early binding, you need to reference the files using the reference dialog within your VBA editor (under Tools).