Range in Excel VBA

Question

I have the following code snippet:

ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\Contoso\Desktop\ArquivosDoContoso.csv", Destination:=Range _
    ("$A$1"))

In part Destination:=Range _ ("$A$1")) have how I put the name of the dynamic table?

For example: I created a Dynamic Table with the name TBDados, has how I make the Destination:=Range _ ("$A$1") is this table? Regardless of the row the table starts?

I tried so Destination:=Range _ ("$TBDados$"), but returns me the following error:

Range method of the_Global object failed.

Answer 1

Hi,
I have never used this method, but to select a dynamic table you can use the PivotTables. In your case, ex:

Dim pt As PivotTable
Dim tabelarange As Range

Set pt = ActiveSheet.PivotTables("TBDados")
Set tabelarange = pt.TableRange1 'SÓ SELECIONA A TABELA E SEU CABEÇALHO, PARA SELECIONAR OS FILTROS USE TableRange2

ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\Contoso\Desktop\ArquivosDoContoso.csv", Destination:=tabelarange)

See if it works, unfortunately, now I have no way to test the QueryTables.Add.

Answer 2

Dynamic tables are reports assembled from a data source. It is not possible to overwrite it. The logic of the flow of mounting a report in Excel is: 1 - Define a data source 2 - Point a dynamic table to this data source.

In your case, (1) is represented by you creating a Querytable in a spreadsheet, say "helper" (which can be hidden) and (2) is creating and configuring a dynamic table that points to this data source.

Answer 3

Edit:

Sub Main()
    'Altere aqui de acordo com sua necessidade:
    Const FILE_PATH As String = "c:\temp\exemplo.csv"

    Dim oList As Excel.ListObject
    Dim iFF As Integer
    Dim sLine As String
    Dim lRows As Long
    Dim lCols As Long
    Dim lRow As Long
    Dim vLines() As Variant

    'Altere aqui para sua necessidade:
    Set oList = ThisWorkbook.Worksheets("Plan1").ListObjects("Tabela1")

    oList.ListColumns(1).Range.Resize(, 4).ClearContents
    If oList.ListRows.Count > 1 Then
        oList.ListRows(2).Range.Resize(oList.ListRows.Count - 1).Delete
    End If

    iFF = FreeFile
    Open FILE_PATH For Input As #iFF
    Do Until EOF(iFF)
        Line Input #iFF, sLine
        lRows = lRows + 1
        ReDim Preserve vLines(1 To lRows)
        vLines(lRows) = sLine
    Loop
    Close #iFF

    oList.Resize oList.Range.Resize(lRows + 1)

    Application.Calculation = xlCalculationManual
    For lRow = 1 To UBound(vLines)
        oList.ListRows(lRow).Range(1).Resize(, 4) = Split(vLines(lRow), ";")
    Next lRow
    Application.Calculation = xlCalculationAutomatic

End Sub