I really don't know enough about Queries to be definate about this.

Looking at the QueryString, I think that maybe:

In the Workbook's VBA Editor, the Project Explorer, (see View menu,) must be showing a "Sheet1" without the parenthesis.

It doesn't need to have a "(Sheet1)"

In the VBAProject Explorer, the Non-Parenthesized name is the sheet's CodeName, and the One with Parens is the Sheet's Name, often called it's Tab name.

Try this code and see if it changes things
a = 1

Do Until Name = ""

    sheetValue = "Sheet" & a
    Connect.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & Path & Name & ";"
    QueryString = "INSERT INTO " & sheetValue & " VALUES ('test')"
    
Call query1.Open(QueryString, Connect)
Connect.Close
        
    Name = Dir
    a = a + 1
Try
QueryString = "INSERT INTO Sheets(""" & sheetValue & """) VALUES ('test')"
The correct number of double quotes is on you

The resulting two Query strings I am thinking of are
1.) INSERT INTO Sheet1 VALUES ('test') [Uses Sheet1's CodeName]
and
2.) INSERT INTO Sheets("Sheet1") VALUES ('test') [Uses Sheets1's Tab Name.]