Forum Discussion

Contrail1234's avatar
Contrail1234
Copper Contributor
Jul 12, 2026

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

I am beyond frustrated that I cannot make this work. Let me explain.

My spreadsheet needs to import a standard CSV file then parse that information into several different worksheets. The very first time I ran the import code it worked. The second time and every time after that it fails with the "Run-time error '1004': Application-defined or object-defined error" message. I tried this on a different computer with the same results first run it worked, every run after that it fails.

Here is the code:

Sub ImportCSV()
    Dim ws As Worksheet
    Dim csvFilePath As String
    Dim lastRow As Long

    ' Set the worksheet where you want to load the data
    Set ws = ActiveWorkbook.Sheets("Raw")

    ' Specify the path to your CSV file
    csvFilePath = "exportusers-all.csv"

    ' Clear existing data in the worksheet
    ws.Cells.Clear

    ' Import the CSV file
    With ws.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
End Sub

This is the code I've found in numerous places when I search the internet when I search how to do it. I have tried this as a normal user and "Run as Administrator" both fail.

My Trust Center > Macro Settings are as follows:

  • Enable VBA macros - Selected
  • Trust access to the VBA project object model - Checked

The CSV file can be opened in a text editor and Excel without any issues, but if I try to import it, it fails completely. Any help or suggestions to resolve this problem would be greatly appreciated. Thanks.

 

excel
Macros and VBA

3 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jul 15, 2026

    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.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jul 15, 2026

    Are you running this code while the same worksheet is active as last time? If yes, what happens if you right-click cell A1 (assuming it contains an existing import) and choose Refresh?