Excel Extrair dados do Excel para um formulário do Word (VBE)

[Selton Do Ramo]

Boa tarde pessoal, tudo bem?

Eu trabalho diariamente com preenchimento de dados no Excel e repassando esses dados para um formulário feito no Word para logo após ser feita a impressão, preciso de um código com comentários (para auxilio na edição de campos) para que possa me ajudar na automatização desse processo. E, se possível, me digam um curso de VBA que vocês recomendem e que o seu conteúdo englobe essa parte da interação do Excel com Word.

 

Abaixo está um print da planilha que uso para preenchimento de dados. Desde já agradeço que puder me ajudar.

[Edson Luiz Branco]

Bom dia, @Selton Do Ramo

 

Seria importante anexar seus arquivos, tanto Excel quanto Word, para vermos como estão estruturados. Se necessário, descaracterize informações confidenciais mas mantenha a estrutura e alguns dados para teste.

você já tentou Mala Direta no Word tendo como base sua pasta de trabalho Excel? Nesse caso envolveria pouca ou nenhuma programação VBA.

Seu Formulário ou documento inicial (principal) Word tem que tipo de campos? Indicadores (bookmarks) ou campos de mala direta (MergeFields)? É um documento (docx/docm) ou um modelo (dotx/dotm)?

O resultado final, no Word, será um documento único ou múltiplos documentos?

   

Em 28/05/2020 às 17:43, Selton Do Ramo disse:

...me digam um curso de VBA que vocês recomendem e que o seu conteúdo englobe essa parte da interação do Excel com Word...

 

Não conheço cursos para isso, mas na minha opinião para programar o Word à partir do Excel ou o Excel à partir do Word o importante é estar bem familiarizado com os principais objetos de cada um desses aplicativos e seus respectivos métodos e propriedades.

Os fundamentais objetos do maior pro menor (na hierarquia), no Excel: Application, Workbook, Worksheet, Range, Shape. No Word: Application, Document, Selection, Range, Shape/InlineShape e pra uso em automação FormField, Bookmark, MailMerge, entre outros.

Quanto à ligação entre um aplicativo e outro isso é o mais simples e geralmente é feito em umas  2 ou 3 linhas de código só para abrir e colocar o Documento ou Workbook de interesse numa variável. O restante é manipulação dos objetos e subobjetos destes.

[Selton Do Ramo]

Em 01/06/2020 às 09:33, Edson Luiz Branco disse:

Bom dia, @Selton Do Ramo

 

Seria importante anexar seus arquivos, tanto Excel quanto Word, para vermos como estão estruturados. Se necessário, descaracterize informações confidenciais mas mantenha a estrutura e alguns dados para teste.

você já tentou Mala Direta no Word tendo como base sua pasta de trabalho Excel? Nesse caso envolveria pouca ou nenhuma programação VBA.

Seu Formulário ou documento inicial (principal) Word tem que tipo de campos? Indicadores (bookmarks) ou campos de mala direta (MergeFields)? É um documento (docx/docm) ou um modelo (dotx/dotm)?

O resultado final, no Word, será um documento único ou múltiplos documentos?

   

 

Não conheço cursos para isso, mas na minha opinião para programar o Word à partir do Excel ou o Excel à partir do Word o importante é estar bem familiarizado com os principais objetos de cada um desses aplicativos e seus respectivos métodos e propriedades.

Os fundamentais objetos do maior pro menor (na hierarquia), no Excel: Application, Workbook, Worksheet, Range, Shape. No Word: Application, Document, Selection, Range, Shape/InlineShape e pra uso em automação FormField, Bookmark, MailMerge, entre outros.

Quanto à ligação entre um aplicativo e outro isso é o mais simples e geralmente é feito em umas  2 ou 3 linhas de código só para abrir e colocar o Documento ou Workbook de interesse numa variável. O restante é manipulação dos objetos e subobjetos destes.

     @Edson Luiz Branco Opa, tudo bem? Desculpe a demora, pensei que ninguém iria poder me ajudar com esse dilema... Bom, na verdade, umas de minhas funções do trabalho é gerar ordens de serviço. Registro a solicitação com alguns dados que o solicitante de uma determinada área me passa e, a partir de uma pasta de trabalho com diversas planilhas ( uso de acordo com a planilha do dia do mês como na imagem da descrição do tópico), abro o Word e preencho uma Ordem de Serviço para dar ao técnico, que, logo após, vai lá e averigua a solicitação.

     Meu objetivo é o seguinte: Criar um botão na planilha do Excel chamada "Gerar OS" que, ao selecionar uma linha e clicar nesse botão, ele vai abrir um arquivo de ordem de serviço com diversos campos e repassar esses dados selecionados para os campos da Ordem de Serviço do Word. Assim que feito, só irei analisar para ver se está tudo correto, preencher alguns dados que só devems er preenchidos na OS e imprimir. Em anexo está um exemplo de pasta de trabalho com apenas uma planilha para seguir como base e um exemplo de Ordem de Serviço (baixado na net) em formado .docx para a mesma finalidade. Ambos foram editados por mim. Preciso de uma base de códigos VBA para adaptar à minha real planilha de trabalho.  Espero que possa me ajudar, estou ansioso pela sua resposta. Obrigado pelo contato!

Exemplo de ORDEM DE SERVIÇO.docx Exemplo de Planilha.xlsx

[Edson Luiz Branco]

Bom dia, @Selton Do Ramo!

 

4 horas atrás, Selton Do Ramo disse:

...clicar nesse botão, ele vai abrir um arquivo de ordem de serviço com diversos campos e repassar esses dados selecionados para os campos da Ordem de Serviço do Word. Assim que feito, só irei analisar para ver se está tudo correto, preencher alguns dados que só devems er preenchidos na OS e imprimir.

 

Esse arquivo do Word, após preenchido e impresso você deve salvá-lo ou ele é só pra servir de formulário impresso e pode ser descartado sem salvar?

 

[Selton Do Ramo]

Em 05/06/2020 às 08:24, Edson Luiz Branco disse:

Bom dia, @Selton Do Ramo!

 

 

Esse arquivo do Word, após preenchido e impresso você deve salvá-lo ou ele é só pra servir de formulário impresso e pode ser descartado sem salvar?

 

@Edson Luiz Branco , bom dia!

é só pra servir de formulário impresso e pode ser descartado sem salvar

[Patropi]

Boa tarde @Selton Do Ramo

 

Vou te dar uma dica, para responder não utilize o botão Citar, utilize a janela de resposta que fica logo abaixo da resposta.

Só use citação quando for realmente necessário.

 

[]s

[Edson Luiz Branco]

Bom dia, @Selton Do Ramo

Em 06/06/2020 às 11:27, Selton Do Ramo disse:

é só pra servir de formulário impresso e pode ser descartado sem salvar

 

Diante disso, uma maneira interessante de fazer seria manter seu formulário do Word totalmente imobilizado numa das planilhas (embedding = incorporado no jargão técnico), num velho e bom objeto OLE.

A atualização dos campos se faz através de Indicadores (bookmarks) inseridos manualmente (de maneira definitiva, 1 só vez) no documento, nas posições adequadas.

Utilização:

Selecione na planilha "TabelaControle" uma célula qualquer que esteja na linha que desejar gerar a OS e clique no botão "GERAR FORMULÁRIO WORD". Isso ativará a planilha com o doc incorporado e atualizará as posições com os valores daquela linha.

 

Em 05/06/2020 às 04:08, Selton Do Ramo disse:

...Assim que feito, só irei analisar para ver se está tudo correto, preencher alguns dados que só devems er preenchidos na OS e imprimir.

 

Para preencher esses dados antes da impressão, clique duas vezes no quadro do documento incorporado e edite o que desejar .

 

 

Exemplo de Planilha v1_1[EdsonBR].zip

[Selton Do Ramo]

@Edson Luiz Branco, boa tarde!

está constantemente dando nesse erro abaixo e não sei como resolver.

adicionado 7 minutos depois

@Edson Luiz Branco, por isso era desejável comentários com instruções de edição na linha de código para possibilitar a minha adaptação...

[Edson Luiz Branco]

Talvez seu Office seja de uma versão mais nova ou mais antiga que a minha, então esqueci de dizer pra marcar a referência à biblioteca do Word de sua versão: no VBA vá no menu Ferramentas>Referências, veja se há alguma referência do Word cujo final esteja escrito "(ausente)" e desmarque. Role então abaixo até encontrar "Microsoft Word xx.x Object Library", onde xx.x é a versão de seu Office.

 

Marque também, se já não estiver marcada, a referência à "OLE Automation" como na imagem abaixo.

 

Obs.:

41 minutos atrás, Selton Do Ramo disse:

...por isso era desejável comentários com instruções de edição na linha de código para possibilitar a minha adaptação

 

Não fiz comentário linha a linha no código pra poupar tempo/trabalho pois não sabia se essa solução iria atender seu propósito. Mas no que você tive dúvida, só perguntar.

[Selton Do Ramo]

@Edson Luiz Branco, fiz a correção conforme suas instruções e agora está funcionando. Então se eu quiser fazer o mesmo para a pasta de trabalho da empresa que trabalho, terei que inserir o formulário do Word na planilha e adaptar aquele código para ela?

E como fica para a pasta de trabalho com múltiplas planilhas, sendo que o número de planilhas sempre são de acordo com o número de dias do mês?

adicionado 5 minutos depois

@Edson Luiz Branco, visto que terei que adaptar para a pasta de trabalho da empresa, quais são as modificações que terei que fazer? Visto que é uma pasta de trabalho grande, outro formulário de OS e outros campos das planilhas?

[Edson Luiz Branco]

53 minutos atrás, Selton Do Ramo disse:

se eu quiser fazer o mesmo para a pasta de trabalho da empresa que trabalho, terei que inserir o formulário do Word na planilha e adaptar aquele código para ela?

 

Isso mesmo. O passo-a-passo para fazer isso seria:

1. No Word:
   • Crie o formulário desejado no Word e formate como necessário, deixando ele pronto. Se não quiser, nem precisaria salvá-lo pois ele será incorporado no Excel ao final;
   • Para facilitar a visualização da próxima etapa, vá no menu Arquivo > Opções > Avançado > role até a seção "Mostrar o conteúdo do documento" e marque a caixa "Mostrar indicadores";
   • Localize os pontos onde serão inseridas as variáveis, digite ali pelo menos um caractere qualquer (um espaço em branco, por exemplo) pra que o indicador a ser definido seja do tipo correto;
   • De um por um, selecione cada um desses caracteres, vá no menu Inserir > grupo Links clique em "Indicador" e dê um nome a ele, de preferência relacionado ao conteúdo que terá aquele campo, por exemplo bkmEmpresa;
   • Revise tudo e, estando ok, dê CTRL + T para selecionar tudo e CTRL + C para copiar para a Área de Transferência;
2. No Excel:
   • Clique com o botão direito na célula que você quer inserir o documento (planilha "FormulárioImpressãoOS"), em Opções de Colagem escolha Colar Especial... > Colar > Como: "Objeto Documento do Microsoft Word".
   • Com o objeto selecionado, na Caixa de Nomes renomeie de "Objeto 1" para "objWord" que é o nome que ele está sendo referido no código.
   • Ajuste a área de impressão conforme necessário.

53 minutos atrás, Selton Do Ramo disse:

E como fica para a pasta de trabalho com múltiplas planilhas, sendo que o número de planilhas sempre são de acordo com o número de dias do mês?

 

Pois é, por isso teria sido importante você anexar o arquivo mais próximo de sua realidade pra evitar retrabalho.

O que poderia ser feito aqui seria inserir um botão em cada uma das 31 planilhas ou, talvez melhor, um atalho de teclado que chame o procedimento; então mover a macro da classe da planilha para um módulo convencional e readequar o código para identificar qual o range e de qual worksheet  que o chamou (qual foi o Caller). Não seria necessário e nem desejável inserir um documento Word para cada uma das 31 planilhas.

 

[Selton Do Ramo]

@Edson Luiz Branco o passo-a-passo do Word e Excel eu fiz conforme suas instruções e parei aí. Já a segunda parte ainda não ficou muito claro para mim como realizar esses procedimento com multiplas planilhas

[Edson Luiz Branco]

Ok, continuemos amanhã...

[Selton Do Ramo]

Certo @Edson Luiz Branco! Obrigado pelo apoio! Assim que tiver à vontade voltamos aqui

[Selton Do Ramo]

Exemplo de Planilha.xlsx@Edson Luiz Branco, bom dia!

Conforme o solicitado, criei uma pasta de trabalho um pouco mais próximo do original, dessa vez com 15 planilhas, acredito que o que funcionar com elas, funcione com a pasta de trabalho com 30 ou 31.

[Edson Luiz Branco]

Veja agora, @Selton Do Ramo

 

Disponibilizei 1 botão em cada planilha, eliminei a necessidade de fazer prévia referência ao Word e incluí um botão na planilha do relatório para voltar para a planilha de OS do dia que originou o chamado. Esse botão se oculta ao imprimir e reaparece depois.

Exemplo de Ordem de Serviço Word Incorporada ao Excel v2_0.zip

[Selton Do Ramo]

@Edson Luiz Branco, boa tarde!

Está sempre dando nesse erro ao gerar a OS

 

adicionado 3 minutos depois

E ao depurar, o excel sinaliza essa linha

 

[Edson Luiz Branco]

, acho que devo ter deletado sem querer um dos indicadores, o bkmDescr do campo Descrição da O.S. na janela do Word.

Para se certificar disso, ao chegar nesse erro clique em Depurar e, na janela de "Verificação Imediata" digite:

?bkmNome

veja qual o retorno.

Se for, clique 2 vezes pra entrar no objeto Word e recrie o bookmark excluído, conforme descrito no passo nº 1, terceiro e quarto subitens do post #11 acima.

_________

Edit:

Realmente, foi o que ocorreu. Segue novamente o arquivo com essa correção.

Exemplo de Ordem de Serviço Word Incorporada ao Excel v2_1.zip

[Selton Do Ramo]

Certo, @Edson Luiz Branco! Muito bem explicado, consegui corrigir o erro com as suas instruções. Tá funcionando direitinho. Agora quais os campos devo editar para levar essa mesma funcionalidade dos códigos para a pasta de trabalho da empresa?

[Edson Luiz Branco]

Se você não vai usar o arquivo que enviei e sim começar do zero na pasta de sua empresa, acompanhe a imagem anexa e faça o seguinte:

1. No VBE, copie o código que está na classe EstaPastaDeTrabalho (item 1 da figura), para a equivalente da de sua empresa. Ali tem o código para esconder o botão Voltar durante a impressão.
2. Copie também o código que está na classe da planilha FormulárioImpressãoOS para a equivalente de seu modelo que abrigará o formulário Word com a O.S. (item 2 da figura). Ali está o código para o botão Voltar.
   • Para não correr o risco de que caso a planilha com a O.S. fosse renomeada lá no Excel, dentro do código usei o CodeName (item 2b da figura) ao invés do Name (destacado logo abaixo da 2b) onde se referia a essa planilha. Portanto você deverá renomear na janela Propriedades (F4) o CodeName para wsWordForm.
   • No Word, crie seu formulário de O.S. com os bookmarks já definidos, copie e cole no Excel conforme descrito no tópico #11. Anote em separado o nome que você deu a esses indicadores.
   • Na planilha, crie o botão Voltar (tipo ActiveX) e renomeie ele para btnVoltar.
3. Crie um módulo convencional e nele cole o código do módulo mdOSExcelToWord (item 3 da figura).
   • dentro desse módulo, nas linhas semelhantes a:
     AtualizaIndicadores "bkmLoc", rg.Cells(11).Value
     altere cada nome do bookmark que está entre aspas (bkmLoc, por exemplo, foi usado no campo Localização) para o nome que você anotou no passo 2 acima. Altere também o índice da coluna que contém o valor correspondente (no caso acima, a 11ª coluna era a coluna "Localização" = coluna K).
4. Crie, numa só das abas dos dias, digamos a planilha "1", o botão "GERAR O.S. DA LINHA SELECIONADA" e posicione-o como desejado. Aqui você deve usar ao invés de botões ActiveX, botões tipo "Controles de Formulários".
   • Renomeie-o para btnGerarOS
   • Clique com o botão direito sobre ele e escolha Atribuir Macro... Note que a macro btnGerarOS_Clique não aparece como disponível para escolha, então você terá que digitar na caixa esse nome. Isso ocorre porque a macro foi definida como Private, não Public.
   • Copie o botão para a Área de Transferência
   • Selecione a próxima planilha, digamos "2" e sem clicar em nenhuma célula dê CTRL+V pra colar na mesma posição relativa à planilha anterior. A vantagem de se usar botões legados (de Formulário) aqui é que não é necessário escrever no evento Click de cada um em cada módulo de planilha pra direcionar pra o procedimento único btnGerarOS_Clique. Inclusive ao copiar o botão o nome também ficou o mesmo e a macro atribuída a eles também. você também pode criar uma planilha modelo completa já com o botão e replicar a planilha, se elas ainda não existirem.

_____________________________________

Obs:

• a macro leva em consideração a coluna F (OS/FMP) para definir qual a última linha que contém dados válidos para geração do formulário de OS.
• pode ser mais fácil dispensar os procedimentos do item 2 e ao invés disso copiar a planilha FormulárioImpressãoOS do arquivo que fizemos aqui para o seu Workbook da empresa. Para isso, mantenha os 2 arquivos abertos e nesse que fizemos clique com o botão direito sobre a "orelha" da planilha, selecione "Mover ou Copiar...", em "Para pasta:" clique na seta e escolha seu Workbook da empresa e por fim marque a caixa "Criar uma cópia".

 

[Selton Do Ramo]

@Edson Luiz Branco, boa tarde!

 

Segui todos os passos e os refiz diversas vezes para garantir que estou fazendo conforme as instruções passadas. Porém, no final sempre dá no mesmo erro e acredito que o senhor possa identificar. Talvez possa ter deixado de citar algo nas instruções que esteja resultando nesse erro. Ocorre sempre que clico no botão de GerarOS. Deixei o print do momento do erro e a da pasta de trabalho anexos. 

Planilha v1 Selton.rar

[Edson Luiz Branco]

Em 08/06/2020 às 14:03, Edson Luiz Branco disse:

...

2. No Excel:

• ...
• Com o objeto selecionado, na Caixa de Nomes renomeie de "Objeto 1" para "objWord" que é o nome que ele está sendo referido no código.

 

 

você pulou esse item do post #11, @Selton Do Ramo

[Selton Do Ramo]

@Edson Luiz Branco, eu tinha esquecido desse item, agora está tudo ok! só mais uma dúvida: Junto com os dados da linha, também queria preencher o campo "Data Entrada" do formulário OS, nesse mesmo campo, já criei um indicador chamado "bkmData". Só preciso que ao clicar no botão "Gerar OS" esse mesmo campo do indicador bkmData seja preenchido com os valores da célula C7 do relatório (célula indicada no relatório que se encontra a data).

[Edson Luiz Branco]

@Selton Do Ramo, não existe campo de data na C7 do arquivo que você anexou, pois a tabela já começa em A1. Então estou presumindo que o layout real de suas planilhas 1 a 31 é na verdade esse da imagem, correto?

Se sim, é só acrescentar mais uma linha na atualização dos indicadores:

  AtualizaIndicadores "bkmData", rg.Parent.Range("C7").Text

 

[Selton Do Ramo]

Show @Edson Luiz Branco, deu certo aqui! também tem uma questão sobre uma de suas respostas que cita a colagem do botão GerarOS em outras planilhas a partir da posição que copiei. Eu tentei fazer isso mas nas minhas planilhas sempre há uma célula ativa, sendo assim a colagem cai sob ela. O que posso fazer para colar a partir de uma posição relativa?