Runt-time Error 1401 When Importing a CSV file Using VBA

Delete any existing QueryTables on the worksheet before clearing cells or adding a new one, and force the refresh to complete before the rest of your code runs.

Sub ImportCSVToSheet(ws As Worksheet, csvFilePath As String)
    Dim i As Long

    ' Step 1: Remove any leftover QueryTables from previous runs
    For i = ws.QueryTables.Count To 1 Step -1
        ws.QueryTables(i).Delete
    Next i

    ' Step 2: Now it's safe to clear the sheet
    ws.Cells.Clear

    ' Step 3: Import the CSV
    With ws.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False   ' wait for import to finish before continuing
    End With

    ' Step 4 (optional but recommended): remove the connection once loaded,
    ' so it doesn't linger in Data > Queries & Connections
    If ws.QueryTables.Count > 0 Then ws.QueryTables(1).Delete
End Sub

Then run one import per sheet like this:

Sub ImportAll()
    ImportCSVToSheet ActiveWorkbook.Sheets("Raw"), ThisWorkbook.Path & "\exportusers-all.csv"
    ' Add more lines here for each additional sheet/CSV file you need to import
    ' ImportCSVToSheet ActiveWorkbook.Sheets("Orders"), ThisWorkbook.Path & "\exportorders-all.csv"
End Sub

If you've already run the broken version several times, there may be leftover connections sitting in your workbook right now. Before running the fixed macro:

1. Go to Data tab → Queries & Connections
2. Delete any entries referencing your CSV file
3. Run the fixed macro going forward — it will keep itself clean automatically from then on

With this structure, you can import as many CSV files into as many worksheets as you need, reliably, every time — not just on the first run.

My answers are voluntary and without guarantee!

Hope this will help you.