Início / computer / Perguntas / 1805785
Accepted
Dave
Asked: 2023-08-26 00:53:16 +0800 CST

Consulta avançada: vincule novamente uma consulta a uma planilha/tabela que foi excluída e recriada

  • 772

Estou usando o Power Query no Excel para Microsoft 365. Estou usando o VBA, em vez da interface gráfica, para executar operações do Power Query.

Suponha que eu tenha uma pasta de trabalho existente, com essa pasta de trabalho contendo duas tabelas Source_te Sink_t(em planilhas denominadas Source_we Sink_w, respectivamente) e que tenho uma consulta chamada Sink_qque obtém dados de Source_t, realiza algumas transformações neles e carrega os resultados em Sink_t.

Considere o seguinte código VBA:

Sub foo()
    Dim Current As Worksheet
    
    ' Side question: Is there a better way to check for the existence of a worksheet before trying to delete it?
    For Each Current In Worksheets
        If (Current.Name = "Sink_w") Then
            Application.DisplayAlerts = False
            Current.Delete
            Application.DisplayAlerts = True
            
            Exit For
        End If
    Next
    
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sink_w"
    ActiveWorkbook.RefreshAll
End Sub

Quando o código VBA é excluído Sink_w(e, ao fazê-lo, Sink_ttambém), Sink_qele se transforma em uma consulta "Somente conexão". Quando o código VBA recria Sink_we executa uma atualização completa, Sink_tnão é recriado (e, obviamente, a saída da consulta não é carregada porque não há tabela para carregá-la).

Mas Sink_wé recriado e posso usar a opção "Carregar para..." da interface gráfica do Power Query para criar uma tabela Sink_we carregar dados nessa tabela. (Mas observe que o nome da tabela criada é Sink_q--- o mesmo nome da consulta --- não Sink_tconforme necessário. Se eu renomear manualmente a tabela para Sink_t, as coisas ainda funcionarão como esperado quando eu atualizar tudo.)

Minhas perguntas

No VBA, como posso:

  1. Vincular Sink_qnovamente para Sink_tque uma atualização de tudo seja criada sink_t(na planilha sink_w) e preencha essa tabela?
  2. Controlar o nome da tabela criada para que fique Sink_t, não Sink_q?
microsoft-excel

1 respostas

  1. Best Answer
    Sub foo()
    Dim Current As Worksheet
    
    For Each Current In Worksheets
        If (Current.Name = "Sink_w") Then
            Application.DisplayAlerts = False
            Current.Delete
            Application.DisplayAlerts = True
            
            Exit For
        End If
    Next
    
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sink_w"
    Range("A1").Select

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sink_q;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sink_q]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sink_t"
        .Refresh BackgroundQuery:=False
    End With
End Sub
    • 0

relate perguntas