A melhor maneira de acessar dados de outra pasta de trabalho do Excel

Question

Qual seria a melhor maneira de obter acesso somente leitura aos dados armazenados em outra pasta de trabalho do Excel. Existem duas possibilidades que eu conheço, cada uma com seus prós e contras.

Opção 1) Referencie os dados diretamente

Exemplo: = [test1.xls] Folha1! $ A $ 1

• pro: dados atualizados quando a pasta de trabalho é aberta
• pro: funciona mesmo quando a pasta de trabalho indicada é fechada
• con: a operação não pode ser orientada por dados
• pro: funciona em todas as planilhas, sem limitação de segurança

Opção 2) Referencie os dados usando INDIRECT e ADDRESS

Exemplo: = INDIRECTO (ENDEREÇO (B7, B6, 1, VERDADEIRO, B4))

em que B7 contém índice de linha, B6 contém índice de coluna, B4 contém nome de pasta de trabalho / planilha

• pro: a localização dos dados pode ser orientada por dados
• con: não funciona quando o livro de referência está fechado
• con: solução alternativa para carregar automaticamente o livro de referência não funciona no nível de segurança de macro padrão

Answer 1

Se você não fizer muito ou precisar de um valor de célula como no seu exemplo, a Opção 1 é um bom caminho a percorrer. Se você precisa:

1. Faça esse tipo de coisa com frequência
2. Crie consultas dinâmicas com base no que mais pode estar acontecendo em uma pasta de trabalho (ou aceite a entrada do usuário)
3. Puxe muitos dados ou filtre / classifique os dados que você pegou
4. Processe os dados que você recebe antes de apresentá-los
5. etc.

Você pode escrever 1 ou mais macros que usam ADO . Você pode usar o ADO no Excel para extrair dados do Excel, outros bancos de dados ou arquivos de texto para suas planilhas. Eu uso uma tonelada. Tanto assim, eu tenho um módulo de aula dedicado para lidar com os detalhes para mim .

    

Answer 2

Se o motivo pelo qual você está preocupado se a técnica é orientada por dados é porque está preocupado com a falta de referência da referência quando os dados da outra pasta de trabalho são alterados, é possível evitar o problema usando um intervalo nomeado.

Para criar um intervalo nomeado: Na pasta de trabalho que contém os dados, você seleciona as células que têm os dados, clicam na caixa de nome (onde a linha e coluna da (s) célula (s) aparecem) e digitam o nome.

Referenciando o intervalo: Para se referir ao intervalo nomeado de outra planilha:

'[MyData.xls] Folha1'! MyNamedRange

Se você precisar de um valor específico de, digamos, uma tabela, poderá usar as várias funções de pesquisa, como vlookup, hlookup, index para selecionar um valor específico.

Por exemplo, se eu tivesse a tabela a seguir na pasta de trabalho "Sales.xls" na planilha "Mercearia" e eu tivesse definido um intervalo nomeado "tableSales" para a tabela inteira,

Venda de itens Bananas 343 Uvas 123 Ovos 756

Depois, posso pesquisar as vendas de "Ovos" em outra pasta de trabalho com o seguinte:

= VLOOKUP ("Ovos", '[Vendas.xls] Mercearia'! tableSales, 2, false)

(o primeiro parâmetro do vlookup é o valor de pesquisa, o segundo é um intervalo da tabela, o terceiro parâmetro especifica qual coluna recuperar e, se o quarto estiver definido como verdadeiro, ele tentará encontrar uma correspondência aproximada)