Find "Search Keywords" from an Excel file in a Word file. Topic is solved

[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

[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.

[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

[Salah]

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

[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

[Salah]

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

[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

[Salah]

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

[Salah]

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?

[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

[Salah]

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

[flyingDman]

It means it still open. Close it first.

[Salah]

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?

[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

[Salah]

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

[flyingDman]