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

0 votes

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
Apr 10, 2023 in Others by narikkadan
• 63,420 points
1,372 views

1 answer to this question.

0 votes

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
answered Apr 10, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,420 points
591 views
0 votes
1 answer

Exporting a table from Amazon RDS into a CSV file

Presumably, you're attempting to export data from ...READ MORE

answered Mar 3, 2022 in Others by gaurav
• 23,260 points
1,929 views
0 votes
1 answer

Convert table in a jpg image to excel using python

I believe you must execute OCR (optical ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,420 points
1,871 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
916 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,240 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
532 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
769 views
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
471 views
0 votes
1 answer

How to rename a workbook other than using (Name) and (FileSystemObject. MoveFile)?

Use  SHFileOperation API Option Explicit Private Declare PtrSafe Function SHFileOperation ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
203 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP