Add specific CSV to workbook in VBA

Question

I have a an Excel workbook and I want to add a specific CSV as a new sheet and then convert it in a table.

Here is my VBA code, this works fine, the problem is that then when I want to convert the sheet into a tab then Excel give me this error:

A Table cannot overlap a range that contains a Pivot Table report,query results, protected cells or another table.

Sub Macro8()
     '
     '
    Dim strPath As String
    Dim strFile As String
     '
    strPath = "Q:\myfolder\"
    strFile = Dir(strPath & "filename" & Format(Now(), "YYYYMMDD") & ".csv")
    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                .Parent.Name = Replace(strFile, ".csv", "")
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
        End With
        strFile = Dir
     Loop

End Sub


Sub A_SelectAllMakeTable()
    Dim tbl As ListObject
    Dim rng As Range

    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "OPEN"
    tbl.TableStyle = "TableStyleMedium15"
End Sub

Can someone help me please?

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-01-22T10:38:10

A Table cannot overlap a range that contains a Pivot Table report,query results, protected cells or another table.

You need to break the querytable connection first else you will get the error that you are getting. is this what you are trying?

Sub A_SelectAllMakeTable()
    Dim tbl As ListObject
    Dim rng As Range
    Dim ws As Worksheet
    Dim lCol As Long, lRow As Long

    Set ws = ActiveSheet

    With ws
        '~~> Delete the connection
        For Each Cn In .QueryTables
            Cn.Delete
        Next Cn

        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Find last row and column to construct your range
            lRow = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    Lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

            lCol = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    Lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column

            Set rng = .Range(.Cells(1, 1), .Cells(lRow, lCol))

            Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
            tbl.Name = "OPEN"
            tbl.TableStyle = "TableStyleMedium15"
        End If
    End With
End Sub

Add specific CSV to workbook in VBA

1 Answers