Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Looking for a value in Excel
#1
How can I look for a value in all sheets of Excel and QM tells me sheet and cell?
#2
Code:
Copy      Help
str s="33" ;;search for "33"

ExcelSheet es.Init
ARRAY(str) a
int sheet row col found
int nsheets=es.ws.Application.Worksheets.Count
for(sheet 1 nsheets+1)
,es.Init(sheet)
,es.GetCells(a)
,for row 0 a.len(2)
,,for col 0 a.len(1)
,,,;out a[col row]
,,,if(a[col row]=s) found=1; goto g1
;g1
if(!found) mes "not found"; ret
mes "sheet %i, row %i, column %c" "" "i" sheet row+1 'A'+col

act "Excel"
es.ws.Activate
Excel.Range cell=es.ws.Cells.Item(row+1 col+1)
cell.Select

Works with QM 2.1.7 and later.
#3
Thanks.
#4
Exist a quicker way? If there are several sheets, the time is excesive.
#5
Ok, Sir Gintaras.. I need your help.
How to read and write the cell, if that excelsheet as a activex in dialog multipage.?
thank you for the help.
#6
Example with Microsoft Office Spreadsheet control 11. QM's ExcelSheet here cannot be used because the control does not have Excel's Worksheet class.
Function Dialog41
Code:
Copy      Help
\Dialog_Editor
typelib OWC11 {0002E558-0000-0000-C000-000000000046} 1.0
function# hDlg message wParam lParam
if(hDlg) goto messages

str controls = "3"
str lb3
lb3="&Page0[]Page1[]Page2"
if(!ShowDialog("Dialog41" &Dialog41 &controls)) ret

;BEGIN DIALOG
;0 "" 0x90C80A44 0x100 0 0 265 163 "Dialog"
;3 ListBox 0x54230101 0x204 4 4 96 80 ""
;1001 Static 0x54020000 0x4 104 6 24 10 "Page0"
;1003 Button 0x54032000 0x0 138 4 48 14 "set A3"
;1004 Button 0x54032000 0x0 190 4 48 14 "get A4"
;1002 ActiveX 0x54030000 0x0 104 22 156 114 "OWC11.Spreadsheet {0002E559-0000-0000-C000-000000000046} data:676C9BD4FF8B88A690B422072CD41ECE26C4D3BDDCD1149301"
;1101 Static 0x44020000 0x4 106 4 48 13 "Page1"
;1201 Static 0x44020000 0x4 106 4 48 13 "Page2"
;1 Button 0x54030001 0x4 142 146 48 14 "OK"
;2 Button 0x54030000 0x4 192 146 48 14 "Cancel"
;4 Button 0x54032000 0x4 242 146 18 14 "?"
;5 Static 0x54000010 0x20004 4 138 255 1 ""
;END DIALOG
;DIALOG EDITOR: "" 0x2020105 "" "0" ""


ret
;messages
sel message
,case WM_INITDIALOG
,OWC11.Spreadsheet sp1002
,
,goto selectpage
,case WM_DESTROY
,case WM_COMMAND goto messages2
ret
;messages2
sel wParam
,case 1003
,sp1002._getcontrol(id(1002 hDlg))
,sp1002.Range("A3").Value2="hi"
,
,case 1004
,sp1002._getcontrol(id(1002 hDlg))
,str s=sp1002.Range("A4").Value2
,mes s
,
,case IDOK
,case IDCANCEL
,case LBN_SELCHANGE<<16|3
,;selectpage
,_i=LB_SelectedItem(id(3 hDlg))
,DT_Page hDlg _i
ret 1

Example with Excel workbook in web browser control. QM's ExcelSheet can be used. However it is slow and unstable.
Code:
Copy      Help
\Dialog_Editor
function# hDlg message wParam lParam
if(hDlg) goto messages

str controls = "3 1002"
str lb3 ax1002SHD
lb3="&Page0[]Page1[]Page2"
;ax1002SHD="$personal$\book1.xls" ;;painting problems
if(!ShowDialog("Dialog42" &Dialog42 &controls)) ret

;BEGIN DIALOG
;0 "" 0x90C80A44 0x100 0 0 265 163 "Dialog"
;3 ListBox 0x54230101 0x204 4 4 96 80 ""
;1001 Static 0x54020000 0x4 104 6 24 10 "Page0"
;1003 Button 0x54032000 0x0 138 4 48 14 "set A3"
;1004 Button 0x54032000 0x0 190 4 48 14 "get A4"
;1002 ActiveX 0x54030000 0x0 108 22 154 110 "SHDocVw.WebBrowser"
;1101 Static 0x44020000 0x4 106 4 48 13 "Page1"
;1201 Static 0x44020000 0x4 106 4 48 13 "Page2"
;1 Button 0x54030001 0x4 142 146 48 14 "OK"
;2 Button 0x54030000 0x4 192 146 48 14 "Cancel"
;4 Button 0x54032000 0x4 242 146 18 14 "?"
;5 Static 0x54000010 0x20004 4 138 254 1 ""
;END DIALOG
;DIALOG EDITOR: "" 0x2020105 "" "0" ""


ret
;messages
sel message
,case WM_INITDIALOG
,SHDocVw.WebBrowser we1002
,Excel.Workbook eb
,ExcelSheet es
,0
,we1002._getcontrol(id(1002 hDlg))
,we1002.Navigate(_s.expandpath("$personal$\book1.xls"))
,
,goto selectpage
,case WM_DESTROY
,case WM_COMMAND goto messages2
ret
;messages2
sel wParam
,case [1003,1004]
,we1002._getcontrol(id(1002 hDlg))
,eb=we1002.Document
,es.ws=eb.ActiveSheet
,if(wParam=1003) es.SetCell("hi" 1 3)
,else es.GetCell(_s 1 4); mes _s
,
,case IDOK
,case IDCANCEL
,case LBN_SELCHANGE<<16|3
,;selectpage
,_i=LB_SelectedItem(id(3 hDlg))
,DT_Page hDlg _i
ret 1
#7
yes, that's what i need, Thanks so much..
but, I can not find the Microsoft Office Spreadsheet control 11 in activex list.
Iam using office 2007, how to register the control so i can choose in activex list (Dialog).?
#8
I use 2003. I did not register the control. Probably registered when installing Office. The file is

c:\program files\common files\microsoft shared\web components\11\owc11.dll
#9
How can I include http://www.tushar-mehta.com/excel/tips/findall.html in ExcelSheet Class?
#10
Not sure is it possible to convert to QM.
#11
Do you know why doesn't work?

Macro Macro8
Code:
Copy      Help
ExcelSheet es.Init
es.ws.Application.Goto("[Workbook1]Sheet1!A1")
#12
Look in Excel help. The string must be in R1C1 notation.

"[Book1]Sheet1!R1C1"
#13
I tried, but neither works.

Macro Macro8
Code:
Copy      Help
ExcelSheet es.Init
es.ws.Application.Goto("[Book1]Sheet1!R1C1")

Error (RT) in Macro8: 0x800A03EC,
#14
On my PC:

A1 -> error 0x800A03EC
R1C1 -> works

Maybe workbook name is wrong.
#15
Even I tried es.ws.Application.Goto("R1C1") but doesn't work.

Do you know another way to select the cell of Address(0 0 1 1)?
#16
Another function is Select. Don't know more.

Or use Range object instead of string.

Macro Macro1273
Code:
Copy      Help
Excel.Range r=es.ws.Range("A1")
;r.Select
es.ws.Application.Goto(r)
#17
This way only works if the sheet[x] is selected.

This is the function I was working...

Member function ExcelSheet.findAll
Code:
Copy      Help
function'str str&encontrados str'texto [entera] [insensitive]

if(!ws) Init

Excel.Worksheet w
Excel.Range found found1
foreach w ws.Application.Worksheets
,VARIANT lookat matchcase
,if(entera) lookat=Excel.xlWhole
,else lookat=Excel.xlPart
,if(insensitive)
,,matchcase=FALSE
,else matchcase=TRUE
,found=w.UsedRange.Find(texto @ @ lookat @ 1 matchcase)
,if(!found) continue
,rep
,,str temp=found.Address(0 0 1 1)
,,if(findw(encontrados temp 0 1)>=0) break
,,encontrados.formata("%s[]" temp)
,,found1=found
,,found=w.Cells.FindNext(found1)
encontrados.ordenar(0 0 1 0 0 1)
encontrados.trim

ret numlines(encontrados)
err+ end _error


I try to create a QM dialog to select the found items.
#18
Look for Excel function that to select sheet. For example, ExcelSheet.Init uses it.
#19
Finally, this works.

Macro Macro4
Code:
Copy      Help
ExcelSheet es.Init
es.ws.Application.Goto(es.ws.Application.ConvertFormula(("[Book1]Sheet1!A1") Excel.xlA1 Excel.xlR1C1))
#20
Can you add in next QM a function to find in excel?
#21
yes, useful
this should be OK?
Member function ExcelSheet.Find
Code:
Copy      Help
function! `what ARRAY(Excel.Range)&aFound [flags] [$range] ;;flags: 1 match case, 2 match entire cell, 4 find all, 8 within workbook, 16 by columns, 0x100,0x200 look in (0 formulas, 0x100 values, 0x200 comments)

;Finds cells.
;Returns: 1 found, 0 not found.

;what - text to search for.
;aFound - variable for results.
;flags - find options. The same as in Excel Find dialog. Some flags:
;;;4 - find all matching cells. If not set, finds the first matching cell, and aFound will have 1 element.
;;;8 - search in all worksheets of this workbook. If not set - in this worksheet only.
;range - part of worksheet where to search. Default: "" - all. <help>Excel range strings</help>.

;Added in: QM 2.3.3.

;EXAMPLES
;;find single cell containing "c"
;ExcelSheet es.Init
;ARRAY(Excel.Range) a
;if(!es.Find("c" a)) out "not found"; ret
;;show results
;out a[0].Value; out a[0].Column; out a[0].Row

;;find all cells containing "c"; search in all worksheets
;ExcelSheet es.Init("" 16)
;ARRAY(Excel.Range) a; int i
;es.Find("c" a 4|8)
;;show results
;for i 0 a.len
,;Excel.Range r=a[i]
,;Excel.Worksheet w=r.Parent
,;str sWS(w.Name) sAddr(r.Address(0 0 1))
,;out "%s: %s (R%iC%i)" sWS sAddr r.Row r.Column
,;w.Activate; r.Activate
,;1

... (code not included now)
#22
Ok.
Thanks.


Forum Jump:


Users browsing this thread: 1 Guest(s)