Excel vba returns error when data is updated via xlwings or any macro but works when data is manually pasted

Question

I have a workbook that I want to update the data in a sheet called Consol with data from a csv called consol.csv. When the data is updated, it should then run a macro vba script. I thought it would be straight forward, but the script returns an error. Runtime error '5':

This is the xlwings code I have:

import xlwings as xw 
import pandas as pd

df2 = pd.read_csv('consol.csv')

wk = xw.Book(r'data.xlsm')
sheet = wk.sheets('Consol')

sheet.clear()
sheet.range('A1').options(index=False).value = df2

updatedates = wk.macro('module1.findnextdates')
updatedates()

wk.save()
wk.close()

I then tried doing it manually and realized that if I manually delete the data in sheet Consol excluding the header row, and paste all the rows from consol.csv then run the the vba script, the script works. The function that is failing is called findnextdates There is a button to run the vba script for updating dates in the sheet called UPDATE DATES

So i tried this vba script as well to replicate this, but it still did not work.

Sub ReplaceDataWithCSV()
    Dim ws As Worksheet
    Dim rng As Range
    Dim csvFilePath As String

    ' Set reference to the "Consol" sheet
    Set ws = ThisWorkbook.Sheets("Consol")

    ' Clear all data below the header row
    Set rng = ws.Range("A2").Resize(ws.UsedRange.Rows.Count - 1, ws.UsedRange.Columns.Count)
    rng.ClearContents

    ' Specify the path to the CSV file
    csvFilePath = ThisWorkbook.Path & "\consol.csv"

    ' Check if the CSV file exists
    If Dir(csvFilePath) <> "" Then
        ' Import data from CSV file
        With ws.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=ws.Range("A2"))
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True ' Assuming the CSV file is comma-delimited
            .TextFileStartRow = 2 ' Skip the first row
            .Refresh
        End With
    Else
        MsgBox "CSV file not found!"
    End If
End Sub

I still got the same error. It also appears that if I add some more data in the same format to the csv file then manually copy and paste, the vba script does not run either.

I am attaching the workbook and csv files. there are two csv files, consol1.csv is the one that is working with manual copy and paste, while consol2.csv with extra rows does not work at all.

Any help with doing this successfully in either xlwings or vba would be greatly appreciated.

FILES

Workbook is here: https://drive.google.com/file/d/1xhmJ3iLLUzkpgHjFG-GfKq3LC6Odi3dl/view?usp=sharing

Partially working csv: https://drive.google.com/file/d/1EjLVaO_npIi5wdp8wdtAfwihivn6Y9yz/view?usp=sharing

Non-working csv: https://drive.google.com/file/d/1ZUTGyrri3xL0aGELbReW1toWPCH_6-cn/view?usp=sharing