Trying to import a CSV file and convert it into a table using VBA

Question

I'm attempting to import a CSV file and create a table from it. When I press the button, my code successfully imports the CSV and displays the data, however I get the following error:

When attempting to convert the data to a table, the following message appears: "A table cannot span a range that contains a PivotTable report, query results, protected cells, or another table." I don't think any tables are now in use, so I'm not sure what's wrong.

Sub ImportAssets()
    Dim csvFile As Variant
    csvFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
    If csvFile = False Then Exit Sub
    
    'Import the data into an existing sheet
    Dim importSheet As Worksheet
    Set importSheet = ThisWorkbook.Sheets("Asset Tool")
    
    'Delete any existing tables or PivotTables in the worksheet
    Dim tbl As ListObject
    For Each tbl In importSheet.ListObjects
        tbl.Delete
    Next tbl
    
    Dim pt As PivotTable
    For Each pt In importSheet.PivotTables
        pt.TableRange2.Clear
        pt.RefreshTable
    Next pt
    
    With importSheet.QueryTables.Add(Connection:= _
        "TEXT;" & csvFile, Destination:=ActiveSheet.Cells(5, 1))
        .Name = "Imported CSV Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ""
        .TextFileColumnDataTypes = Array(1, 1, 1) 'Change the number of columns and the data type for each column as necessary
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    'Insert the data into a table
    Dim importRange As Range
    Set importRange = importSheet.UsedRange
    Dim table As ListObject
    Set table = importSheet.ListObjects.Add(xlSrcRange, importRange, , xlYes)
    table.Name = "AssetData"
    table.TableStyle = "TableStyleMedium2"
    
End Sub

Answer 1

You cannot convert a range that contains a query result into a Table, according to the error notice. Nevertheless, after importing the file, you can delete the QueryTable to turn your range into a Table if you don't need to keep the workbook connection.

With importSheet.QueryTables.Add(Connection:= _
    "TEXT;" & csvFile, Destination:=ActiveSheet.Cells(5, 1))
    .Name = "Imported CSV Data"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = ""
    .TextFileColumnDataTypes = Array(1, 1, 1) 'Change the number of columns and the data type for each column as necessary
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    .Delete
End With