Como manter referências de célula quando as colunas da tabela de consulta mudam

Question

No Excel, tenho um querytable vinculado a uma tabela SQL. As fórmulas dentro da minha planilha contêm referências às células dentro da tabela, usando referências absolutas, como Z9 .

EuusoovbaparaatualizaraconsultaSQLparaatabela.OproblemacomeçaquandoeumudoacolunaqueeupegodoSQL.Asreferênciasdecélulaquebram,mesmoqueosdadosnosquaiseuestejainteressadoestejamnamesmacélula.

Obviamente, tenho o mesmo problema ao usar a metodologia de referência =Table1[Column1] porque o nome da coluna é alterado. Existe uma maneira de alterar uma coluna em uma tabela de consulta sem quebrar as referências de fórmula de folha a essa coluna?

    

Answer 1

Eu não consegui encontrar uma maneira de evitar quebras de #REF quando meu código vba alterou a consulta SQL. Eu consegui contornar o erro! Em vez de alterar a consulta com base nas entradas do usuário, obtive todo o conjunto de dados de uma só vez e, em seguida, usei pesquisas de correspondência de índice nesse conjunto universal para capturar a coluna desejada.

Uma solução típica de excel: funcional, mas deselegante. Selecionará sua resposta se você tiver algo melhor!

    

Answer 2

Eu tive um problema semelhante ao baixar dados de um site, usando o método QueryTables.Add. As referências à planilha que continha os dados da consulta mudavam sempre que eu executava novamente a consulta. Alterei a abordagem para copiar e colar dados da planilha de consulta na outra planilha. No início, isso também não funcionou, pois os valores de alteraram as colunas com as consultas subsequentes. Para resolver isso, fiz o seguinte:

1. Limpa a planilha de consulta antes de emitir a consulta.
2. Reduziu a consulta para recuperar apenas dados essenciais, com uma redução de 20 vezes.

3. Usou o seguinte código para copiar / colar:

   Sheets("Data").Cells(FromRow, FromCol).Copy
   Destination:=Sheets("Port").Cells(ToRow, ToCol)
   

Isso parece funcionar. Consultas subseqüentes que preencheram a planilha "Dados" foram coladas corretamente na planilha "Dados". Eu suspeito que houve um problema de sincronização quando a consulta era grande, com referências na outra planilha sendo executada antes da consulta ser concluída.