Naty, voce deve criar uma macro onde essa macro onde deverá ter um laço de repetição Do While que identifica em uma pastas esses arquivos.
no laço Do While abr o arquivo e importa as informação para a planilha de destino, depois fecha o arquivo e abre o proximo.

traduzindo isso para uma macro VBA seria algo assim

Dim PastaOrigem as String
dim sArquivo as string


dim Wkb as WorkBook

PastaOrigem = "C:Arquivos"

sArquivo = Dir(PastaOrigem )

Do While sArquivo <>"" 

 'Arquivo, abre a planilha
  set Wkb  = Application.Workbooks.Open(PastaOrigem & "" & sArquivo)

'Depois de aberta, captura as informações com base nas suas regras.

'Fecha o arquivo sem salvar
Wkb.Close False
   sArquivo = Dir(PastaOrigem )
Loop