Excel do Seu Jeito

Criar um novo arquivo excel dinamicamente

Escrito por: Reinaldo Coral Em 2 - dezembro - 2008

Oi amigos. Hoje vou disponibilizar para vocês um código que eu desenvolvi visando a criação de um arquivo excel, em tempo de execução, ou seja, criar um novo arquivo através de uma macro personalizada.

Neste exemplo prático, teremos 4 planilhas no arquivo original, sendo que três delas conterão o saldo das vendas de três setores distintos de uma empresa e 1delas exibirá o resumo de todos e o total geral das vendas. Até aqui, nada de mais. Nosso código passa a funcionar a partir daí.

Iremos disponibilizar uma funcionalidade nesta planilha de resumos, que fará o seguinte: criará uma cópia de qualquer uma das planilhas dos setores, individualmente, em um novo arquivo, para ser enviado por email, por exemplo, sem necessidade de informar os demais valores dos outros setores da empresa para quem não seja conveniente informar.

Vamos ao trabalho.

Crie seu arquivo com as 4 planilhas:

  1. Resumo
  2. Setor Alfa
  3. Setor Beta
  4. Setor Gamma

Na planilha Resumo, insira três CommandButton e renomeie-os assim:

  1. cmd_Salvar1
  2. cmd_Salvar2
  3. cmd_Salvar3

Abra o editor do VBA (Alt + F11). Selecione a planilha Resumo na janela do VBAProject e exiba seu código (F7) e digite o seguinte código:

?View Code VB
Private Sub cmd_Salvar1_Click()
    Call CriaArquivo(Sheets("Setor Alfa"), ThisWorkbook.Path)
End Sub
 
Private Sub cmd_Salvar2_Click()
    Call CriaArquivo(Sheets("Setor Beta"), ThisWorkbook.Path)
End Sub
 
Private Sub cmd_Salvar3_Click()
    Call CriaArquivo(Sheets("Setor Gamma"), ThisWorkbook.Path)
End Sub

Agora crie um módulo e digite:

?View Code VB
Sub CriaArquivo(mPlan As Worksheet, mPathSave As String)
Dim NovoArquivoXLS As Workbook
Dim sht As Worksheet
 
    'Cria um novo arquivo excel
    Set NovoArquivoXLS = Application.Workbooks.Add
 
    'Copia a planilha para o novo arquivo criado
    mPlan.Copy Before:=NovoArquivoXLS.Sheets(1)
 
    'Salva o arquivo
    NovoArquivoXLS.SaveAs mPathSave & "\" & mPlan.Name & ".xls"
 
    MsgBox "Novo arquivo salvo em: " & mPathSave & "\" & mPlan.Name & ".xls", vbInformation
 
End Sub

Pronto.

Explicando o que fizemos. Ao clicarmos no botão 1, por exemplo, criaremos um novo arquivo com os dados da planilha correspondente ao “Setor Alfa” e salva com o mesmo nome da planilha em questão e na mesma pasta da planilha original. Legal né. Agora é só usar a criatividade e adaptar as suas necessidades.

Um abração a todos. Até a próxima.

>

Alguns termos aprendidos neste artigo.

Application.Workbooks.Add Cria uma nova pasta de trabalho. A nova pasta de trabalho se torna a pasta de trabalho ativa. Retorna um objeto Workbook.
SaveAs Salvas alterações na pasta de trabalho um arquivo diferente.

expressão.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

expressão necessária. Uma expressão que retorna um dos objetos acima.

Filename Variant opcional. Uma seqüência de caracteres que indique o nome do arquivo a ser salvo. Você pode incluir um caminho completo; se não o fizer, o Microsoft Excel salvará o arquivo na pasta atual.

FileFormat Variant opcional. O formato do arquivo que deve ser usado ao salvá-lo. Para obter uma lista de escolhas válidas, consulte a propriedade FileFormat. Para um arquivo existente, o formato padrão é o último formato de arquivo especificado; para um novo arquivo, o padrão é o formato da versão do Excel usada.

Password Variant opcional. Uma seqüência de caracteres distinguindo maiúsculas de minúsculas (de até 15 caracteres) que indique a senha de proteção a ser dada ao arquivo.

WriteResPassword Variant opcional. Uma seqüência de caracteres que indique a senha de reserva de gravação deste arquivo. Se um arquivo for salvo com a senha e esta não for fornecida quando o arquivo for aberto, o arquivo será aberto como somente leitura.

ReadOnlyRecommended Variant opcional. True para exibir uma mensagem quando o arquivo é aberto, recomendando que o arquivo seja aberto como somente leitura.

CreateBackup Variante opcional. True para criar um arquivo de backup.

AccessMode XlSaveAsAccessMode opcional.

XlSaveAsAccessMode pode ser uma das seguintes constantes:
xlExclusive (modo exclusivo)
xlNoChange padrão (não alterar o modo de acesso)
xlShared (lista compartilhada)
Se este argumento for omitido, o modo de acesso não será alterado. Esse argumento é ignorado quando você salva uma lista compartilhada sem alterar o nome do arquivo. Para alterar o modo de acesso, use o método ExclusiveAccess.

ConflictResolution XlSaveConflictResolution opcional.

XlSaveConflictResolution pode ser uma das seguintes constantes:
xlUserResolution (exibir a caixa de diálogo de solução de conflitos)
xlLocalSessionChanges (aceitar automaticamente as alterações do usuário local)
xlOtherSessionChanges (aceitar outras alterações em vez das alterações do usuário local)
Se este argumento for omitido, a caixa de diálogo de solução de conflitos será exibida.

AddToMru Variant opcional. True para adicionar esta pasta de trabalho à lista de arquivos usados recentemente. O valor padrão é False.

TextCodePage Variant opcional. Não usado no Microsoft Excel em inglês americano.

TextVisualLayout Variant opcional. Não usado no Microsoft Excel em inglês americano.

Local Variant opcional. True salva os arquivos no idioma do Microsoft Excel (incluindo as configurações do Painel de controle). False (padrão) salva os arquivos no idioma do Visual Basic for Applications (VBA), que geralmente é o inglês (E.U.A.), a menos que o projeto VBA em que Workbooks.Open está sendo executado seja um projeto VBA XL5/95 antigo e internacionalizado.
Copy Copia a planilha para outro local da pasta de trabalho.

expressão.Copy(Before, After)

expressão necessária. Uma expressão que retorna um dos objetos acima.

Before Variant opcional. A planilha antes da qual a planilha copiada será inserida. Não é possível especificar Before se After for especificado.

After Variant opcional. A planilha após a qual a planilha copiada será inserida. Não é possível especificar After se Before for especificado.
Bookmark and Share
VBA

11 Comentários

  1. Gabriel Disse,

    Caro amigo (to ficando chegado já rs)
    estou com problema com o fim do código, eu baixei ele do seu site e entrou no modo de depurador nesta etapa:
    ‘Salva o arquivo
    *****NovoArquivoXLS.SaveAs mPathSave & “\” & mPlan.Name & “.xls”

    MsgBox “Novo arquivo salvo em: ” & mPathSave & “\” & mPlan.Name & “.xls”, vbInformation

    exatamente onde tem os asteriscos, qual será o problema?

    Reply

    exceldoseujeito Reply:
    julho 1st, 2009 at 15:03

    Quando for copiar o código no site para colar no seu projeto VBA, clique no link “View Code” no topo dos blocos de códigos.
    Dessa maneira, você poderá copiar o texto puro, sem aas formatações HTML.

    O erro ocorre por esse motivo, ao colar o código no módulo VBA, foram coladas as tags HTML.

    Uma alternativa, e até uma facilidade a mais que eu proporciono no meu site é o download do arquivo fonte, logo ao final do artigo.

    Um abraço

    Reply

    Comentário feito em julho 1st, 2009 às 12:38

  2. Gabriel Disse,

    meu caro amigo, o erro ocorre no seu arquivo ;)

    Reply

    exceldoseujeito Reply:
    julho 2nd, 2009 at 11:10

    Estranho… baixei o arquivo do servidor, testei e deu tudo certo.
    Qual a mensagem de erro?

    Reply

    Comentário feito em julho 2nd, 2009 às 10:55

  3. Gabriel Disse,

    pode ter sido erro meu… devo ter aberto mas não salvei em uma pasta do pc e tentei salvar fora… o erro é q naum encontrava pasta para salvar

    Reply

    Comentário feito em julho 2nd, 2009 às 13:07

  4. NPONTES Disse,

    Muito bom esse artigo. Porém tenho aqui mais um desafio e gostaria de saber sobre a possibilidade de execução:
    Preciso de uma ajuda, quero automatizar a gravação de um arquivo de Excel, com nome+seqüência numérica, por exemplo, arquivo001.xls, arquivo002.xls, etc

    O arquivo é uma Ordem de Serviço, e pretendo também colocar na planilha um campo com o número da OSE que será gerado automaticamente a cada abertura pelo usuário.
    Tem idéia de como consigo fazer isso?
    Por exemplo, abri o arquivo 001.xls, faço as alterações necessárias e automaticamente ao clicar sobre salvar, o Excel deve entender que é uma seqüência, ou seja, além de modificar automaticamente um campo na planilha (por exemplo o número da Ordem de Serviço, que era 001, para 002), deve também gravar o arquivo como arquivo 002.xls sem que o usuário tenha que utilizar Salvar Como ou alterar na mão o nome do arquivo….
    Se tiver dica me ajude (preciso de “receita de bolo”, passo a passo de como fazer

    Reply

    exceldoseujeito Reply:
    agosto 21st, 2009 at 20:17

    Você pode definir uma rotina no Evento Workbook_BeforeSave e combinando com as dicas deste artigo.

    Se quiser, mande uma planilha pelo link Orçamento. Podemos montar essa macro para você. Informe no pedido de orçamento que já fez um comentário sobre o assunto aqui no site.

    Um abraço

    Reply

    Comentário feito em agosto 12th, 2009 às 15:58

  5. Ismael Disse,

    Caro,

    Não consegui adaptar ó código para vba, tenho um userform “editar” e add um commandbutton, e clico mas não gera o arquivo, está dando erro.

    Tem como me ajudar?

    Reply

    exceldoseujeito Reply:
    agosto 23rd, 2009 at 14:32

    Qual erro?

    Reply

    Comentário feito em agosto 23rd, 2009 às 5:43

  6. osman Disse,

    O Código virou uma especie de backup´- tipo: 1 – salva do arquivo origial na mesma pasta – 2 mostra se já existe e da opção de substituir – 3 fecha o arquivo novo e da foco no original //// ficou assim
    Sub CriaArquivo(mPlan As Worksheet, mPathSave As String)
    Dim NovoArquivoXLS As Workbook
    Dim sht As Worksheet

    ‘Cria um novo arquivo excel
    Set NovoArquivoXLS = Application.Workbooks.Add

    ‘Copia a planilha para o novo arquivo criado
    mPlan.Copy Before:=NovoArquivoXLS.Sheets(1)

    ‘Salva o arquivo
    NovoArquivoXLS.SaveAs mPathSave & “\” & mPlan.Name & “.xls”

    MsgBox “Novo arquivo salvo em: ” & mPathSave & “\” & mPlan.Name & “.xls”, vbInformation

    Workbooks(“Saldo do Setor Beta.XLS”).Close ‘ linha nova

    End Sub

    Reply

    Reinaldo Coral Reply:
    novembro 28th, 2009 at 11:28

    Ok. Bom.

    Abraço

    Reply

    Comentário feito em novembro 13th, 2009 às 16:14

Deixar um comentário

Get Adobe Flash playerPlugin by wpburn.com wordpress themes