Page 1 of 1
Find "Search Keywords" from an Excel file in a Word file.
Posted: 15 May 2022, 14:45
by Salah
Hello, everyone,
I'm trying to adapt the following code (
viewtopic.php?f=76&t=101642&sid=1ecd7f2c0ad372a3a1bfb19936fcebe7):
Code: Select all
st := a_tickcount
Clipboard := ""
Send, ^c
Clipwait, 0.2
needle := clipboard, clipboard := ""
if !needle
{
msgbox Clipboard was empty
exitapp
}
filepath := "C:\Users\" A_UserName "\Scripts\test123B.xlsx"
xl := ComObjCreate("excel.application")
xl.DisplayAlerts := False
wrkbk := xl.workbooks.open(filepath)
found := xl.Cells.Find(needle,,,1)
if !found
msgbox % "Nothing found `n" a_tickcount - st
else
{
found.entirerow.cells.copy
msgbox % clipboard "`n" a_tickcount - st
}
wrkbk.close(0)
xl.quit()
return
To create a script that searches for a "Search-Keywords" from an Excel file in a Word file. And if there is a match, outputs the result to an Excel file (or TXT file).
i.e. in the open Word file after the first search term from Excel (first row in the first column, A1), then the second search string (A2) and so on (up to A-n).
If there is a match, then the whole row (A1:B1) of the searched string must be copied from Excel and written to a new Excel (or TXT) file as a result (it can be one match or more).
Would be grateful for any help.
Thanks and regards
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 15 May 2022, 19:41
by flyingDman
So, if I get it correctly, you want to check whether the content of cells in the first column of a spreadsheet appears somewhere in a word document. If so, you want that cell and all cells in that same row to be copied to a text file or an other spreadsheet.
In this script both Word and Excel are assumed open: (if not use ComObjCreate)
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
for a,b in arr
loop, % colcnt
lst .= b[1,a_index] . (a_index = colcnt ? "`n" : "`t")
msgbox % lst ; or fileappend,%lst%,xxx.txt
return
Because the rows are copied to safearrays, it should not be too difficult to paste these in a new spreadsheet (xl.range("A1:A4") := sarr). Hope this helps.
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 15 May 2022, 20:03
by flyingDman
Copying these rows to a new workbook:
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
nwsht := xl.workbooks.add.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b
return
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 16 May 2022, 03:14
by Salah
flyingDman wrote: ↑15 May 2022, 19:41
So, if I get it correctly, you want to check whether the content of cells in the first column of a spreadsheet appears somewhere in a word document. If so, you want that cell and all cells in that same row to be copied to a text file or an other spreadsheet.
Thank you for your help, yes it is correct, that is exactly what is expected.
I tried the code, unfortunately I get an empty message box, is there an explanation for this?
Thanks in advance
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 16 May 2022, 11:44
by flyingDman
To troubleshoot:
- make sure to run the script on its own. close other scripts, if any
- copy paste the script from the forum
- make sure Word and Excel are open and that the documents are the only ones in each application
- add msgboxes to read variable when then are getting populated. Like
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
msgbox % var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
msgbox % colcnt
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
{
msgbox % c.value
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
}
for a,b in arr
loop, % colcnt
lst .= b[1,a_index] . (a_index = colcnt ? "`n" : "`t")
msgbox % lst ; or fileappend,%lst%,xxx.txt
return
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 17 May 2022, 03:00
by Salah
flyingDman wrote: ↑16 May 2022, 11:44
To troubleshoot:
- make sure to run the script on its own. close other scripts, if any
- copy paste the script from the forum
- make sure Word and Excel are open and that the documents are the only ones in each application
- add msgboxes to read variable when then are getting populated. Like
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
msgbox % var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
msgbox % colcnt
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
{
msgbox % c.value
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
}
for a,b in arr
loop, % colcnt
lst .= b[1,a_index] . (a_index = colcnt ? "`n" : "`t")
msgbox % lst ; or fileappend,%lst%,xxx.txt
return
Many Thanks.
I did exactly as you described:
>> "msgbox % var := oWord.selection.text": the values from Word can be read and displayed correctly.
>> "msgbox % colcnt": it only counts one column even though there are 2 columns (returns the number 1).
>> "msgbox % c.value": msgbox is empty (cannot read the values from excel).
>> "msgbox % lst": is also empty (which is to be expected since the values cannot be read from excel).
I use an ".xlsx" file, I also tried with ".csv" file, is the same problem.
The problem seems to be when reading from Excel, is there a specific function for this?
Many thanks for all your efforts
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 17 May 2022, 10:32
by flyingDman
There is no function needed here. It seems you might not connect properly to Excel. Have you noticed other issues using COM? Like, does this report the content of cell A1?:
Code: Select all
xl := ComObjActive("excel.application")
msgbox % xl.range("a1").text
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 17 May 2022, 12:37
by Salah
flyingDman wrote: ↑17 May 2022, 10:32
There is no function needed here. It seems you might not connect properly to Excel. Have you noticed other issues using COM? Like, does this report the content of cell A1?:
Code: Select all
xl := ComObjActive("excel.application")
msgbox % xl.range("a1").text
>> does this report the content of cell A1?:
unfortunately the content of cell A1 is not output.
So if I enter the exel path explicitly then it works:
Code: Select all
#!h::
{
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
msgbox % var := oWord.selection.text
filepath := "xxx\test.csv"
Xl := ComObjCreate("excel.application")
Xl.DisplayAlerts := False
wrkbk := Xl.workbooks.open(filepath)
msgbox % Xl.range("a1").text
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
msgbox % colcnt
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
{
msgbox % c.value
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
}
for a,b in arr
loop, % colcnt
lst .= b[1,a_index] . (a_index = colcnt ? "`n" : "`t")
msgbox % lst ; or fileappend,%lst%,xxx.txt
return
}
Thank you very much for your help, I am also satisfied with this alternative, thank you
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 18 May 2022, 05:22
by Salah
flyingDman wrote: ↑15 May 2022, 20:03
Copying these rows to a new workbook:
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
nwsht := xl.workbooks.add.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b
return
Hallo,
I'm trying to copy the output to a new workbook:
Code: Select all
#!h::
{
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
;msgbox % var := oWord.selection.text
filepath := "C:\xxx\test.csv"
Xl := ComObjCreate("excel.application")
Xl.DisplayAlerts := False
wrkbk := Xl.workbooks.open(filepath)
;msgbox % Xl.range("a1").text
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
{
msgbox % c.value
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
}
nwsht := Xl.workbooks.add.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b
return
}
Unfortunately, it doesn't work, what am I doing wrong?
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 18 May 2022, 11:02
by flyingDman
This line:
Code: Select all
nwsht := xl.workbooks.add.activesheet
Creates a new workbook (and sets a "pointer" to the activesheet in that new workbook).
Once you have established a COM link with Excel using
xl := ComObjActive("excel.application"), you do not need to run
xl := ComObjCreate("excel.application") again.
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
path := "C:\xxx\test.csv" ; path to existing file
wrkbk := xl.workbooks.open(path)
nwsht := wrkbk.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b ; it will start a A1
return
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 18 May 2022, 11:49
by Salah
flyingDman wrote: ↑18 May 2022, 11:02
This line:
Code: Select all
nwsht := xl.workbooks.add.activesheet
Creates a new workbook (and sets a "pointer" to the activesheet in that new workbook).
Once you have established a COM link with Excel using
xl := ComObjActive("excel.application"), you do not need to run
xl := ComObjCreate("excel.application") again.
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
path := "C:\xxx\test.csv" ; path to existing file
wrkbk := xl.workbooks.open(path)
nwsht := wrkbk.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b ; it will start a A1
return
Thank you very much for your help,
I have modified the code, unfortunately it does not work.
What I have noticed, when I try to open the Excel file, I get the message that it is blocked for editing (), here is the entire code:
Code: Select all
#NoEnv
#Persistent
#SingleInstance,Force
#NoTrayIcon
#!h::
{
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
msgbox % var
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
path := "C:\xxx\test2.xlsx" ; path to existing file
wrkbk := Xl.workbooks.open(path)
nwsht := wrkbk.activesheet
for a,b in arr
;msgbox % a
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b ; it will start a A1
;msgbox % b
return
}
I have no idea what to do next!
Thank you for everything
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 18 May 2022, 13:25
by flyingDman
It means it still open. Close it first.
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 19 May 2022, 07:25
by Salah
flyingDman wrote: ↑18 May 2022, 13:25
It means it still open. Close it first.
It was closed and when I try to open it I keep getting the message "Document IN USE, xxx.xls is locked for editing by 'Windows User" ..."
Something goes wrong when trying to write the data to the new worksheet, I think the script is blocking access to Excel, is that possible?
Re: Find "Search Keywords" from an Excel file in a Word file. Topic is solved
Posted: 19 May 2022, 08:43
by flyingDman
At times, when experimenting, you will have multiple instances of excel open without you knowing it because there is no visible window. I have a specific script to kill all instances of Excel just for that reason. When you get the message "Document IN USE, xxx.xls is locked for editing by ", it means it is in use by another invisible Excel instance. Go to task manager and close these instances or reboot your computer.
I just ran this exact script and it works without any error message:
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
path := a_scriptdir "\aaaa.xlsx"
wrkbk := xl.workbooks.open(path)
nwsht := wrkbk.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b
return
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 19 May 2022, 11:52
by Salah
flyingDman wrote: ↑19 May 2022, 08:43
At times, when experimenting, you will have multiple instances of excel open without you knowing it because there is no visible window. I have a specific script to kill all instances of Excel just for that reason. When you get the message "Document IN USE, xxx.xls is locked for editing by ", it means it is in use by another invisible Excel instance. Go to task manager and close these instances or reboot your computer.
I just ran this exact script and it works without any error message:
Code: Select all
oWord := ComObjActive("word.application")
oWord.Selection.WholeStory
var := oWord.selection.text
Xl := ComObjActive("excel.application")
arr := [],colcnt := Xl.Activesheet.UsedRange.Columns.count
for c in Xl.Activesheet.UsedRange.Columns(1).Cells
if instr(var,c.value)
arr.push(c.entirerow.value) ; arr is an array containing safearrays
path := a_scriptdir "\aaaa.xlsx"
wrkbk := xl.workbooks.open(path)
nwsht := wrkbk.activesheet
for a,b in arr
nwsht.range(nwsht.cells(a,1),nwsht.cells(a, colcnt)) := b
return
Thanks very much
worked, thank you very much
Re: Find "Search Keywords" from an Excel file in a Word file.
Posted: 19 May 2022, 12:12
by flyingDman