Eu usaria o suplemento de consulta de energia para isso. Você pode iniciar uma consulta importando todos os arquivos em uma pasta. Contanto que eles tenham um formato consistente, você obterá um único fluxo de dados com todas as linhas de cada arquivo anexado.
Você pode enviar a saída de uma consulta para uma tabela do Excel.
O Power Query tem muitas outras funcionalidades para transformar e combinar dados pelo caminho, se necessário.