Posts: 858
Threads: 196
Joined: Apr 2005
How can I look for a value in all sheets of Excel and QM tells me sheet and cell?
Posts: 12,095
Threads: 142
Joined: Dec 2002
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.
Posts: 858
Threads: 196
Joined: Apr 2005
Posts: 858
Threads: 196
Joined: Apr 2005
Exist a quicker way? If there are several sheets, the time is excesive.
Posts: 38
Threads: 12
Joined: Oct 2007
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.
Posts: 12,095
Threads: 142
Joined: Dec 2002
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
\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.
\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
Posts: 38
Threads: 12
Joined: Oct 2007
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).?
Posts: 12,095
Threads: 142
Joined: Dec 2002
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
Posts: 858
Threads: 196
Joined: Apr 2005
Posts: 12,095
Threads: 142
Joined: Dec 2002
Not sure is it possible to convert to QM.
Posts: 858
Threads: 196
Joined: Apr 2005
Do you know why doesn't work?
Macro Macro8
ExcelSheet es.Init
es.ws.Application.Goto("[Workbook1]Sheet1!A1")
Posts: 12,095
Threads: 142
Joined: Dec 2002
Look in Excel help. The string must be in R1C1 notation.
"[Book1]Sheet1!R1C1"
Posts: 858
Threads: 196
Joined: Apr 2005
I tried, but neither works.
Macro Macro8
ExcelSheet es.Init
es.ws.Application.Goto("[Book1]Sheet1!R1C1")
Error (RT) in Macro8: 0x800A03EC,
Posts: 12,095
Threads: 142
Joined: Dec 2002
On my PC:
A1 -> error 0x800A03EC
R1C1 -> works
Maybe workbook name is wrong.
Posts: 858
Threads: 196
Joined: Apr 2005
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)?
Posts: 12,095
Threads: 142
Joined: Dec 2002
Another function is Select. Don't know more.
Or use Range object instead of string.
Macro Macro1273
Excel.Range r=es.ws.Range("A1")
;r.Select
es.ws.Application.Goto(r)
Posts: 858
Threads: 196
Joined: Apr 2005
This way only works if the sheet[x] is selected.
This is the function I was working...
Member function ExcelSheet.findAll
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.
Posts: 12,095
Threads: 142
Joined: Dec 2002
Look for Excel function that to select sheet. For example, ExcelSheet.Init uses it.
Posts: 858
Threads: 196
Joined: Apr 2005
Finally, this works.
Macro Macro4
ExcelSheet es.Init
es.ws.Application.Goto(es.ws.Application.ConvertFormula(("[Book1]Sheet1!A1") Excel.xlA1 Excel.xlR1C1))
Posts: 858
Threads: 196
Joined: Apr 2005
Can you add in next QM a function to find in excel?
Posts: 12,095
Threads: 142
Joined: Dec 2002
yes, useful
this should be OK?
Member function ExcelSheet.Find
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)
Posts: 858
Threads: 196
Joined: Apr 2005
|