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:
- Resumo
- Setor Alfa
- Setor Beta
- Setor Gamma
Na planilha Resumo, insira três CommandButton e renomeie-os assim:
- cmd_Salvar1
- cmd_Salvar2
- 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:
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:
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. |
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?
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
meu caro amigo, o erro ocorre no seu arquivo 😉
Estranho… baixei o arquivo do servidor, testei e deu tudo certo.
Qual a mensagem de erro?
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
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
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
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?
Qual erro?
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
Ok. Bom.
Abraço
Gabriel, observei que no código que vc mencionou que deu erro possui uma “barra(\)” que não existe no código original. verifica foi isso o causador do erro, pq no meu deu certo.
Valeu!
seu código: *****NovoArquivoXLS.SaveAs mPathSave & “\” & mPlan.Name & “.xls”
código original: NovoArquivoXLS.SaveAs mPathSave & “” & mPlan.Name & “.xls”
para: Exceldoseujeito
Amigo, como disse acima o código deu certo, mas, vc poderia me dizer como eu faço para determinar, no código, onde o novo arquivo será salvo? quero poder determinar uma pasta específica para salvá-lo!
Valeu!
Bom dia Jânyo,
Na rotina Sub CriaArquivo(mPlan As Worksheet, mPathSave As String), o parâmetro mPathSave recebe o caminho onde será salvo o arquivo, ok.
No exemplo, eu passo Thisworkbook.Path que corresponde a mesma pasta do arquivo de origem. Substitua pelo caminho que você quer salvar, como por exemplo:
Call CriaArquivo(Sheets(“Setor Alfa”),”C:\NomeDaPastaOndeQueroSalvar\”)
Abraço
Blz cara, Valeu!
Agora eu queria te pedir mais um favor…
esse comando eu tô criando para “botão de comando” chamado “Salvar”. E eu vou precisar salvar diariamente, então preciso que ele gere a cada dia um novo nome para o arquivo, Ex: na primeiro dia q eu salvar ficará com o nome 001, no segundo dia ficará 002 e assim por diante… assim nunca será exibida aquela tela que diz q já existe um arquivo com esse nome. Existe um código para isso?
Sugestão:
Você pode adicionar a data do arquivo ao nome dele na hora de salvar. Fica até mais fácil de identificar quando foi salvo.
Adicione a instrução, em negrito, na linha de código abaixo
‘Salva o arquivo
NovoArquivoXLS.SaveAs mPathSave & “” & mPlan.Name & “-” & Format(Now, “dd-mm-yyyy”) & “.xls”
Abraço
Infelizmente não passou, deu um bug.
Eu precisaria realmente que gerasse sempre um novo arquivo sem substituir o arquivo gerado anteriormente.
Bem… eu tinha entendido que seria 1 por dia…. mas… ainda pode melhorar dessa maneira e adicionar a hora que foi salva…. nunca seria a mesma data e hora!!!
NovoArquivoXLS.SaveAs mPathSave & “” & mPlan.Name & “-” & Format(Now, “dd-mm-yyyy-hh-mm-ss”) & “.xls”
Para gerar incremento do tipo 001, 002 com vc quer, até dá, mas teria que percorrer os arquivos da pasta, analizar os nomes dos arquivos,… demoraria um pouco mais pra desenvolver… Mas essa idéia que te passei dá pra contornar..
Abç
Amigo, esse post foi uma mão na roda para mim, adaptei ao meu sisteminha e funcionou uma beleza! Sucesso com o blog, tá muito bom…