Script that turns header and lines of Excel/Calc into a pop up menu

Post your working scripts, libraries and tools
nilsonrdg
Posts: 15
Joined: 16 Feb 2017, 16:05
Location: Brazil
Contact:

Script that turns header and lines of Excel/Calc into a pop up menu

01 Nov 2018, 12:34

This script has the same function of a direct mail of windows.

I think it is useful because it allows you to modify any document or change it into a template.


It turns a plan like this:

Name Address Status
Sidartha St. Principal, 33A Processing


into a pop up menu like this:

Name: Sidartha
Address: St. Principal, 33A
Status: Processing



English Code:

Code: Select all


/*

Script to Copy Fields from an Excel or Calc Spreadsheet

This script has the same function of a direct mail of windows.

I think it is useful because it allows you to modify any document or change it into a template.


It turns a plan like this:

Name      Address               Status
Sidartha  St. Principal, 33A    Processing


into a pop up menu like this:

Name: Sidartha
Address: St. Principal, 33A
Status: Processing
    
--------------------------------------------

How to use:

1) Open the worksheet.

2) Press Control + Enter to start the script.

3) Copy the header line (the first line where the labels are).

4) Copy the line where is the data you want to use.

5) Press Control + Space to show the menu to insert the values

6) It will be shown a list where you can click to insert the data into a document.


You can also insert fields in a document template:

    1) Press Alt+Space to display the menu where you can select the fields to be placed in the document.
    2) To edit the template: positionate the cursor over a field and press Win+A to replace the field with related data.

*/



Menu, MenuList, Add  ; Creates a pop up menu by adding a line

ShowMessage("To start press Control+Enter",3000)


^Enter::

Clipboard=

ShowMessage("Please, copy the header.",0)

Clipwait
sleep, 300

ToolTip, Collected: %Clipboard%
sleep, 500
ToolTip,

Text01  = %Clipboard%

; Removing quotes
StringReplace, Text01, Text01, `", , All


Clipboard=

ShowMessage("Please, copy the line.",0)

Clipwait
sleep, 300

ToolTip Collected: %Clipboard%
sleep, 500
ToolTip,

Text02  = %Clipboard%

Text01 := CleaningText(Text01)

Text02 := CleaningText(Text02)

ShowMessage("Click on some place and...`n`nControl+Space to insert the value.`n`nAlt+Space to insert the field.`n`nWinL+A to replace a field.`n`nWin+X to exit.",0)

Arquivo = %A_ScriptDir%\DataFile.txt
FieldsFile = %A_ScriptDir%\FieldsFile.txt

FileDelete, %Arquivo%

FileDelete, %FieldsFile%



; ------ Matching header tags to line values

k1 := 1

Loop, parse, Text01, `r
{
k2 := 1

	Line01 =  %A_LoopField%

	Loop, parse, Text02, `r
	{


	if k1=%k2%
	{
	ResultantLine = %A_LoopField%


	; Removing line breaks

	ResultantLine := RegExReplace(ResultantLine, "\n","")
	ResultantLine := RegExReplace(ResultantLine, "\r","")
		
	StringReplace, ResultantLine, ResultantLine, ",,All

	Line01 := RegExReplace(Line01, "\n","")
	Line01 := RegExReplace(Line01, "\r","")
	Line01 := RegExReplace(Line01, "\/","")

	StringReplace, Line01, Line01, ",,All
	StringReplace, Line01, Line01,%A_Space%,,All

	Line01 := LimparCampo(Line01)
	
	Lista01=%Lista01%`n%Line01%|r|%ResultantLine%

	Lista02=%Lista02%`n%Line01%|r|%Line01%
	}

	k2 := k2 + 1
	}

k1 := k1 + 1

}

; ------ END - Matching header tags to line values


Sleep 1000
ToolTip

Menu, MenuList, Add  ; Adds a line in menu



return


; -->Hotkey to show the colected menu
^Space::
Menu, MenuList, DeleteAll
sort, Lista01
CreateMenu(Lista01)
Menu, MenuList, Show
return

; --> Hotkey to insert field in a model
!Space::
Menu, MenuList, DeleteAll
sort, Lista02
CreateMenu(Lista02)
Menu, MenuList, Show
return

^!R::
reload
return


; Hotkey to replace field for related values
#a::

MouseClick, left
MouseClick, left
sleep, 500
send, ^c
sleep, 500

SelectedFile = %A_ScriptDir%\DataFile.txt

SelectedText = %Clipboard%

    Loop, parse, Lista01, `n
    {
	CurrentText := RegExReplace(A_LoopField,".+\|r\|","")
	StringReplace, CurrentTitle,CurrentTitle,|r|,, All
	
	CurrentTitle := RegExReplace(A_LoopField,"\|r\|.+","")
	StringReplace, CurrentText,CurrentText,|r|,, All

	IfInString, SelectedText, %CurrentTitle%
	{
	StringReplace, ModifiedText,SelectedText,%CurrentTitle%,%CurrentText%, All
        }
    
}


send, %ModifiedText%

ModifiedText=
Clipboard=

return


; Functions

CleaningText(TextToClean)
{

TextToClean  := RegExReplace(TextToClean, "\t", "`r") 

return TextToClean
}


LimparCampo(TextToClean)
{
TextToClean  := RegExReplace(TextToClean, "\: ", "") 

TextToClean  := RegExReplace(TextToClean, "\:", "") 

TextToClean  := RegExReplace(TextToClean, "\-", "")

TextToClean  := RegExReplace(TextToClean, ",", "")

return TextToClean
}


CreateMenu(Lista)
{
    Loop, parse, Lista, `n
    {
	IfNotEqual, Text, %A_Space%
	{
	CurrentText := RegExReplace(A_LoopField,"\|r\|"," -> ")
	Menu, MenuList, Add, %CurrentText%, MenuHandlerML
	Menu, MenuList, Add,
	}
    }
}


ShowMessage(Message,time)
{
Progress, m2 b fs13 zh0,  %Message%

if (time>0){
sleep, %time%
}
else {
KeyWait, LButton, D
}

Progress, Off

}


; ------ Menu Hander ---------

MenuHandlerML:
OutPut = %A_ThisMenuItem%
OutPut := RegExReplace(OutPut,".+-> ","")
OutPut := RegExReplace(OutPut,"-> ","")

ShowMessage("Press Shift to insert the value where you want.",1500)

tooltip,

keywait, Shift, D

Send, %OutPut%

return


#x::exitapp


Código em Português:

Code: Select all


/*

Script para copiar campos e valores de uma planilha do Excel ou Calc

Este script tem a mesma finalidade do recurso mala direta.
Acho ele prático, pois permite pegar qualquer documento pronto e ir modificando ou transformando-o em um modelo.


Por exemplo, o script transforma uma planilha assim:

Nome       Endereços           Situação
Sidarta    Rua Principal, 33A  Processando


Num pop up menu assim:

Nome: Sidarta
Endereço: Rua Principal, 33A
Situação: Processando
    


Como usar:

1) Abra a planilha.

2) Pressione Control+Enter para iniciar o Script.

3) Copie a linha do cabeçalho (primeira linha onde estão os rótulos).

4) Copie a linha onde estão os dados a serem usados.

5) Pressione Control+Espaço.

6) Será mostrada uma lista onde se poderá clicar para inserir os dados num documento.


Também é possível inserir campos num modelo de documento:

    1) Pressione Alt+Espaço para mostrar o menu com os campos a serem colocados no documento.
    2) Para edita o modelo: posicione o cursor sobre um campo e pressione Win+A para substituir pelo dado relacionado.

*/


Menu, MenuLista, Add  ; Cria um pop up menu adicionando uma linha

ExibirMensagem("Para iniciar pressione Control+Enter",3000)


^Enter::

Clipboard=

ExibirMensagem("Copie o cabeçalho.",0)

Clipwait
sleep, 300

ToolTip, Inserido: %Clipboard%
sleep, 500
ToolTip,

Texto01  = %Clipboard%

; Remover aspas
StringReplace, Texto01, Texto01, `", , All


Clipboard=

ExibirMensagem("Copie a linha desejada.",0)

Clipwait
sleep, 300

ToolTip Inserido: %Clipboard%
sleep, 500
ToolTip,

Texto02  = %Clipboard%


Texto01 := LimparTexto(Texto01)

Texto02 := LimparTexto(Texto02)

ExibirMensagem("Agora clique no local desejado e...`n`nPara inserir texto: Control+Espaço.`n`nPara inserir campo: Alt+Espaço.`n`nPara substituir campo: WinL+A.`n`nPress: Win+X para sair.",0)

Arquivo = %A_ScriptDir%\DadosPlanilha.txt
ArquivoCampos = %A_ScriptDir%\ArquivoCampos.txt

FileDelete, %Arquivo%

FileDelete, %ArquivoCampos%



; ---------- Associando títulos do cabeçalho com os respectivos valores da linha

k1 := 1

Loop, parse, Texto01, `r
{
k2 := 1

	Linha01 =  %A_LoopField%

	Loop, parse, Texto02, `r
	{


	if k1=%k2%
	{
	LinhaResultante = %A_LoopField%


	; Removendo quebra de linhas

	LinhaResultante := RegExReplace(LinhaResultante, "\n","")
	LinhaResultante := RegExReplace(LinhaResultante, "\r","")
		
	StringReplace, LinhaResultante, LinhaResultante, ",,All

	Linha01 := RegExReplace(Linha01, "\n","")
	Linha01 := RegExReplace(Linha01, "\r","")
	Linha01 := RegExReplace(Linha01, "\/","")

	StringReplace, Linha01, Linha01, ",,All
	StringReplace, Linha01, Linha01,%A_Space%,,All

	Linha01 := LimparCampo(Linha01)
	
	Lista01=%Lista01%`n%Linha01%|r|%LinhaResultante%

	Lista02=%Lista02%`n%Linha01%|r|%Linha01%
	}

	k2 := k2 + 1
	}

k1 := k1 + 1

}

; ---------- Associando títulos do cabeçalho com os respectivos valores da linha


Sleep 1000
ToolTip  ; deliga a mensagem na tela.

Menu, MenuLista, Add  ; Adiciona um linha no menu.



return


; -->Mostrar Menu Clipboard Coletado
^Space::
Menu, MenuLista, DeleteAll
sort, Lista01
CriaMenu(Lista01)
Menu, MenuLista, Show
return

; --> Usar para inserir campos num modelo
!Space::
Menu, MenuLista, DeleteAll
sort, Lista02
CriaMenu(Lista02)
Menu, MenuLista, Show
return

^!R::
reload
return


; Atalho para substituir campos por item relacionados
#a::

MouseClick, left
MouseClick, left
sleep, 500
send, ^c
sleep, 500

ArquivoSelecionado = %A_ScriptDir%\DadosPlanilha.txt

TextoSelecionado = %Clipboard%

    Loop, parse, Lista01, `n
    {
	TextoAtual := RegExReplace(A_LoopField,".+\|r\|","")
	StringReplace, TituloAtual,TituloAtual,|r|,, All
	
	TituloAtual := RegExReplace(A_LoopField,"\|r\|.+","")
	StringReplace, TextoAtual,TextoAtual,|r|,, All

	IfInString, TextoSelecionado, %TituloAtual%
	{
	StringReplace, TextoModificado,TextoSelecionado,%TituloAtual%,%TextoAtual%, All
        }
    
}


send, %TextoModificado%

TextoModificado=
Clipboard=

return


; Funções

LimparTexto(TextoaLimpar)
{

TextoaLimpar  := RegExReplace(TextoaLimpar, "\t", "`r") 

return TextoaLimpar
}


LimparCampo(TextoaLimpar)
{
TextoaLimpar  := RegExReplace(TextoaLimpar, "\: ", "") 

TextoaLimpar  := RegExReplace(TextoaLimpar, "\:", "") 

TextoaLimpar  := RegExReplace(TextoaLimpar, "\-", "")

TextoaLimpar  := RegExReplace(TextoaLimpar, ",", "")

return TextoaLimpar
}


CriaMenu(Lista)
{
    Loop, parse, Lista, `n
    {
	IfNotEqual, Texto, %A_Space%
	{
	TextoAtual := RegExReplace(A_LoopField,"\|r\|"," -> ")
	Menu, MenuLista, Add, %TextoAtual%, MenuHandlerML
	Menu, MenuLista, Add,
	}
    }
}


ExibirMensagem(Mensagem,tempo)
{
Progress, m2 b fs13 zh0,  %Mensagem%

if (tempo>0){
sleep, %tempo%
}
else {
KeyWait, LButton, D
}

Progress, Off

}


MenuHandlerML:
Saida = %A_ThisMenuItem%
Saida := RegExReplace(Saida,".+-> ","")
Saida := RegExReplace(Saida,"-> ","")

ExibirMensagem("Pressione Shift para inserir o dado onde deseja.",1500)

tooltip,

keywait, Shift, D

Send, %Saida%

return


#x::exitapp
Last edited by nilsonrdg on 08 Nov 2018, 20:38, edited 1 time in total.
burque505
Posts: 957
Joined: 22 Jan 2017, 19:37

Re: Script that turns header and lines of Excel/Calc into a pop up menu

01 Nov 2018, 14:39

@nilsonrdg, thanks for the script. I've been testing it, and it works fine. For me, I had to change the hotkeys to keep from interfering with Evernote and some others, but that was minor.
Regards,
burque505
nilsonrdg
Posts: 15
Joined: 16 Feb 2017, 16:05
Location: Brazil
Contact:

Re: Script that turns header and lines of Excel/Calc into a pop up menu

08 Nov 2018, 20:59

I am happy it works, so maybe it will be usefull to other people.
I posted the script again and finally the Code tag is working, so now it's easier to people copy the code.
xnyan
Posts: 1
Joined: 10 Mar 2017, 11:41

Re: Script that turns header and lines of Excel/Calc into a pop up menu

16 Dec 2018, 17:25

Works on everything I've tried. Very useful for quick edits, as well as for more normal users who need light templates without coding. Thanks!

Return to “Scripts and Functions”

Who is online

Users browsing this forum: Bing [Bot], loupis45 and 35 guests