Considere uma situação em que você se depara com vários arquivos do Excel diariamente e deseja um mecanismo rápido para ajudá-lo a encontrar a contagem de planilhas presentes em cada pasta de trabalho. Se você tiver um problema semelhante, não deve perder este artigo, pois ele o ajudará muito.
Neste artigo, aprenderemos como contar planilhas em vários arquivos com código VBA.
Pergunta:Preciso de uma macro que possa ler uma lista de nomes de arquivo e retornar o número de planilhas que estão presentes em cada um dos arquivos (este é um mecanismo de auditoria para garantir que o número correto de planilhas esteja presente em uma série de arquivos criados via outro processo). A macro precisaria estabelecer o caminho da pasta onde os arquivos estão localizados (todos na mesma pasta), em seguida, localizar o primeiro arquivo, identificar a contagem da planilha e repetir para o próximo arquivo etc.
Achei que poderia fazer isso com uma fórmula simplesmente referenciando os nomes dos arquivos, mas acredito que o Excel não tem uma fórmula direta para contagens de planilhas. Obrigado!
Se você quiser ler a pergunta original, clique aqui
A seguir está o instantâneo dos arquivos salvos em uma pasta com extensão .xlsx
Nota: Não há arquivos protegidos por senha.
Para obter o código, precisamos seguir as etapas abaixo para iniciar o editor VB:
- Clique na guia Desenvolvedor
- No grupo Código, selecione Visual Basic
- Copie o código abaixo no módulo padrão
Sub ListSheetCounts () Dim Cell As Range Dim Conn As Object Dim Cat As Object Dim ConnStr As String Dim n As Long Dim Rng As Range Dim RngEnd As Range Dim WkbPath As Variant Dim Wks As Worksheet 'Caminho da pasta onde as pastas de trabalho estão localizadas. WkbPath = "C: \ Usuários \ Teste" 'Nome da planilha com a lista de pastas de trabalho. Set Wks = Worksheets ("Sheet1") 'Célula inicial da lista de pastas de trabalho. Set Rng = Wks.Range ("A2") 'Obtém o intervalo de células na lista de nomes da pasta de trabalho. Set RngEnd = Wks.Cells (Rows.Count, Rng.Column) .End (xlUp) If RngEnd.Row> = Rng.Row Then Set Rng = Wks.Range (Rng, RngEnd) 'Crie os objetos ADO necessários a partir desta macro . Set Conn = CreateObject ("ADODB.Connection") Set Cat = CreateObject ("ADOX.Catalog") 'Adicione uma barra invertida final ao caminho, se necessário. WkbPath = IIf (Right (WkbPath, 1) "\", WkbPath & "\", WkbPath) 'Percorra cada célula na lista da pasta de trabalho. For Each Cell In Rng 'Obtenha a contagem da planilha para a pasta de trabalho. ConnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" _ & WkbPath & Cell _ & "; Extended Properties =" "Excel 12.0 Xml; HDR = YES; IMEX = 1;" "" Conn.Open ConnStr Set Cat.ActiveConnection = Conn 'Copie a contagem para a célula uma coluna à direita do nome da pasta de trabalho na lista. Cell.Offset (n, 1) = Cat.Tables.Count Conn.Close Next Cell 'Limpar. Set Cat = Nothing Set Conn = Nothing End Sub
- Conforme executamos a macro, obteremos o número de planilhas. Consulte o instantâneo abaixo:
Observação: a macro acima funcionará para a extensão .xlsx e .xls e não para a extensão habilitada para macro .xlsm.
- Todos os arquivos acima têm extensão .xlsx
- Vamos adicionar uma planilha do Excel fictícia, ou seja, a Planilha 10
- Caso tenhamos um arquivo com o mesmo nome com extensões .xlsx e .xls, então precisamos mencionar o nome do arquivo com suas respectivas extensões também em nosso arquivo de teste (coluna A) para que a macro possa identificar o arquivo que estamos referindo-se a e nos dê o resultado correto
- Se não mencionamos ou deixamos de mencionar a extensão do arquivo com o mesmo nome, a macro nos dará a contagem da extensão .xlsx. Consulte o instantâneo abaixo:
- Para obter o número de planilhas para a Planilha 10 com extensões .xlsx e .xls, precisamos mencionar o nome do arquivo com suas respectivas extensões
O instantâneo da saída final é descrito abaixo:
Conclusão: Usando o código de macro acima, podemos contar o número de planilhas em vários arquivos e, se necessário, para obter o resultado personalizado, podemos fazer algumas modificações no código VBA.
Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook.
Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail