[Funções] Função “ARQUIVOS”: Listando arquivos de diretório (Sem VBA)

Quero demostrar que é possível listar os arquivos de um diretório desejado sem utilizar programação VBA. Para isso, vamos utilizar a função chamada ARQUIVOS.

É bem provável que você esteja pensando agora que não existe esta função no Excel, mas já quero te garantir, ela existe e não é uma UDF e nem parte de algum suplemento.

Existem algumas funções do Excel extremamente úteis no Excel, mas que não estão catalogadas nas listas de funções. Dentre elas, a que creio ser mais conhecida é a DATADIF, que pode ser aplicada diretamente na planilha. Entretanto há mais funções “escondidas” no Excel, mas que para aplicá-las é necessário utilizá-las através do Gerenciador de Nomes. Essas funções são chamadas de funções de macro XLM (Não é XML).

A função ARQUIVOS tem uma sintaxe bastante simples, contendo apenas um argumento de texto que deve conter o endereço e a extensão dos arquivos a serem listados, sendo que podemos utilizar caractere coringa como usávamos no saudoso MS-DOS (ex: c:/*.*).

Para aplicar esta função, precisamos criar um nome definido no gerenciador de nomes. Veja os passos:

1) Escreva o caminho desejado na Célula A1 (Ou em qualquer outra célula que desejar)

2) Pressione CTRL+F3 para abrir o gerenciador de nomes e crie um novo nome, que neste exemplo vou chamar de vArquivos e digite a fórmula: =ARQUIVOS(Plan1!$A$1)

listararquivos1

Você pode utilizar o nome e a referência que desejar, só se atente para que o argumento dentro da função ARQUIVOS seja o endereço absoluto da célula onde está o caminho do diretório que deseja listar os arquivos.

3) A função ARQUIVOS nos retorna um Vetor em linha com cada nome de arquivo do diretório indicado, então podemos revelar estes valores inserindo de modo matricial em células múltiplas na horizontal (um valor em cada coluna), ou podemos utilizar a função TRANSPOR para revelar os valores em células múltiplas na vertical (um valor em cada linha), ou podemos utilizar a função ÍNDICE para ir trazendo cada valor do vetor.

  • Para entrar os valores em múltiplas células na horizontal:

Selecione um intervalo de colunas, e digite =vArquivos e pressione CTRL+SHIFT+ENTER:

listararquivos2

  • Para entrar os valores em múltiplas células na vertical:

Selecione um intervalo de linhas, e digite =TRANSPOR(vArquivos) e pressione CTRL+SHIFT+ENTER:

listararquivos3

Estou demonstrando estes dois métodos como maneira de dizer que existem várias maneiras de revelar um valor de uma matriz. Entretanto, em ambos casos, para se trazer todos os nomes dos arquivos seria necessário selecionar a quantidade de linhas ou colunas igual ou maior a quantidade de arquivo do diretório, pois se selecionar menos linhas ou colunas, os arquivos serão listados pela ordem até preencher as linhas e colunas selecionados, e nomes de arquivos restantes não aparecerão. E, se selecionar linhas ou colunas a mais, as linhas ou colunas após preencher todos nomes ou arquivos ficarão com valores #N/D. Por isso, para esta situação eu recomendo utilizar o procedimento com ÍNDICE, conforme veremos abaixo.

  • Utilizando o ÍNDICE:

Podemos utilizar a função ÍNDICE para ir revelando cada elemento do vetor com nomes de arquivos, para isso selecione uma célula e digite:

=SEERRO(ÍNDICE(vArquivos;LIN(A1));”-“)

Sendo que a função SEERRO é só para realizar o tratamento de erros quanto todos arquivos forem revelados. A função LIN é para gerar o valor índice desejado de busca no vetor, sendo que iniciamos com A1, que retorna 1, e ao arrastar a fórmula teremos o incremento de um e um no valor índice.

listararquivos4

Com a fórmula escrita, é só deslocar as linhas o suficiente para trazer todos os nomes, e após isso aparecerá “-” ou qualquer outro texto que você definir no segundo argumento da função SEERRO.

Com este método, você pode filtrar uma extensão especifica de arquivo, é só alterar o caminho do diretório com a extensão desejada, por exemplo:

  • Filtrar só PDF: C:\Windows\*.pdf
  • Filtrar só xlsx: C:\Windows\*.xlsx
  • Filtrar docx na área de trabalho: C:\Users\rafael.kamimura\Desktop\*.docx

Uma curiosidade que notei ao fazer o exemplo é que neste caso o Excel não faz distinção entre barra e barra invertida. Note que nas imagens estava usando barra normal e funcionou corretamente.

IMPORTANTE:

Apesar de não usarmos VBA, o Excel exigirá que o arquivo seja salvo em extensão xlsm (pasta de trabalho habilitada para macro do Excel). Este é um ponto negativo quando se aplica este tipo de função de macro XLM.

Baixe o modelo deste artigo aqui: Baixar Arquivo (Google Drive)

3 comentários em “[Funções] Função “ARQUIVOS”: Listando arquivos de diretório (Sem VBA)

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Gravatar
Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s