Checking Against Excel List via QM

[philipq]

Greetings,

I have a list of names in sheet1 and sheet2 of Excel.
From sheet1, I need to first extract the first name from right untill space and check the result against the list given in sheet2 for a match.
If matched then a set of procedures follows, if not another set of procedures...

Note: The list in sheet2 may grow from time to time.
So the program needs to determine itself the dynamic list in sheet2.

Kindly advise how best to go about this.
1) How to extract the first names
2) How to check the match against list
3) How to choose excel list dynamically
Best Regards,
Philip

[Gintaras]

Did you try ExcelSheet class functions?

extract the first name from right untill space

This is unclear.

[philipq]

I did a bit...

Ok, maybe you can clear this one by one.

In the below code I have chosen the entire G column in Excel, which includes the empty cells too.
1) How do I choose just the ones with data.
Note: This column is dynamic and so keeps increasing and decreasing.

2) How do I check if a cell is empty? In the below code, I have tried a simple empty quotes method. Is there a better foolproof way?

Macro NC VCC Release Corrections

Code:

Copy      Help

ARRAY(str) tempCell
int r

int w1=act(win("Microsoft Excel - LR Sales" "XLMAIN"))
ExcelSheet es.Init
es.Activate(1 "VCC Release Spool") ;;Activate Sheet
es.Activate(4) ;;Bring Excel Window Forward
;es.SelectCell("G1")
;'D
es.CellsToArray(tempCell "G:G")
for r 1 tempCell.len ;;for each row
,if tempCell[0 r]=""

[Gintaras]

Code:

Copy      Help

for r 0 tempCell.len ;;for each row
,if tempCell[0 r].len
,,out tempCell[0 r]

[philipq]

Yes, this answers the non-empty cells part.
However, won't this still keep checking the entire G column?
Let's say there exists only 50 rows, it should stop checking after 50.

[Gintaras]

It does not stop after 50?

[philipq]

The for loop does not exit after 50 counts.
It continues till about 600.
I think it "sees" the previously used cells even though there are no data in it now.
The for loop should stop after the last cell with data in it.
Any suggestions?

[philipq]

See below code, I am trying a work around to get the used range in colum G, dynamically
Could you help me out with the queries in comments:
Macro NC VCC Release Corrections

Code:

Copy      Help

ExcelSheet es.Init
es.Activate(1 "VCC Release Spool") ;;Activate Sheet
es.Activate(4) ;;Bring Excel Window Forward

es.SelectRange("G:G") ;;How to get the used range here?
Excel.Range tempRange = es._Range("sel")
str tempStr=tempRange.Address ;;How to convert the address fo the used range to string?

es.CellsToArray(tempCell tempStr)

for r 1 tempCell.len ;;for each row
,if tempCell[0 r].len
,,out tempCell

Or below is a Excel VBA solution; would it be possible to implement something like in this in QM and use the count in for loop?

Range("A" & Rows.Count).End(xlup).Select
int r=Selection.Count

[Gintaras]

Did you try SelectRange flag 1?

[philipq]

Ok I am trying that now.

How do I refer to the selected cells in 'cellsToArray'

I get the below error for the below code:

Macro NC VCC Release Corrections

Code:

Copy      Help

str tempStr=tempRange.Address

Error in <open ":1421: /375">NC VCC Release Corrections: expected 3 to 5 arguments, not 0.

[Gintaras]

Click CellsToArray in the code editor and press F1 or look in status bar. It shows

range examples: "" (all), "3:3" (row), "C:C" (column), "A1:C3" (range), "sel" (selection), ExcelRow(row) (row as variable). flags: 1 date as number, 2 formula, 3 text

Macro Macro2575

Code:

Copy      Help

ExcelSheet es.Init

;es.SelectRange("G:G") ;;don't need for CellsToArray
ARRAY(str) a
es.CellsToArray(a "G:G")
;es.CellsToArray(a "sel") ;;if need intersection of selection and used range
out a.len

[philipq]

Did that...

So here is my code so far:
How do I check one list within the other:

Macro NC VCC Release Corrections

Code:

Copy      Help

;Obtain 2 lists into arrays
es.Activate(1 "Data") ;;Activate Sheet
es.SelectRange("M:M" 1) ;;obtain used range
es.CellsToArray(SANamesMaster "sel") ;;Obtain Master list of Sales Advisor Names from 'Data' Sheet

es.Activate(1 "VCC Release Spool") ;;Activate Sheet
es.SelectRange("G:G" 1) ;;obtain used range
es.CellsToArray(SANamesCheck "sel") ;;Obtain 'To Be Checked' list of Sales Advisor Names from 'VCC Release Spool' Sheet

;;Check one array against another and change those not in Master list
for r 1 SANamesCheck.len ;;for each row
,if SANamesCheck[0 r].len
,,if SANamesCheck[0 r] ;;*******How to check if SANamesCheck is in SANamesMaster?********
,,,out r

[Gintaras]

QM does not have a function "compare arrays" or "find in array". Use an inner for loop and compare each cell.

[philipq]

ok I have done that workaround...
But now the below code gives error

Macro NC VCC Release Corrections

Code:

Copy      Help

es.SelectRange("M:M" 1) ;;obtain used range

Error (RT) in <open ":1421: /297">NC VCC Release Corrections: 0x80020009, Exception occurred.
0x3EC, Select method of Range class failed.

[philipq]

I have no idea what's going wrong.

The selectRange method randomly keeps failing at every run, whereever it is coded:

Macro NC VCC Release Corrections

Code:

Copy      Help

ARRAY(str) SANamesMaster, SANamesCheck
int counter1, repeatFlag

int w1=act(win("Microsoft Excel - LR Sales" "XLMAIN"))
ExcelSheet es.Init

repeatFlag=0

;Obtain 2 Excel lists(dynamic ranges) into arrays
es.Activate(1 "Data") ;;Activate Sheet
wait 0.5
es.SelectRange("M:M" 1) ;;obtain used range
es.CellsToArray(SANamesMaster "sel") ;;Obtain Master list of Sales Advisor Names from 'Data' Sheet
es.SelectRange("A1")

es.Activate(1 "VCC Release Spool") ;;Activate Sheet
wait 0.5
es.SelectRange("G1") ;;Set Excel cursor at column G1

;es.SelectRange("G:G" 1) ;;obtain used range

;nextCell
if repeatFlag<=5
,'D ;; Go Down 1 cell
,es.CellsToArray(SANamesCheck "sel") ;;Store to array 'To Be Checked' cell of Sales Advisor Names from 'VCC Release Spool' Sheet
,es.SelectRange("A1")
else
,ret ;;Ends Macro as the 'SANamesCheck' list is now fully checked.


if SANamesCheck.len ;;Check if array is not empty
,goto arrayCheckRoutine
else ;;If empty increase checkFlag by 1 and repeat above provess again for upto 5 times to make sure we have reached the end of the list
,repeatFlag=repeatFlag+1
,goto nextCell

;arrayCheckRoutine
;;Check one array against another and change those not in Master list
for counter1 1 SANamesMaster[0 counter1] ;;Loop through Master List Array
,if SANamesCheck=SANamesMaster[0 counter1] ;;Check if current SANamesCheck is in SANamesMaster list
,,break
,else
,,goto obtainSANameFromAL

repeatFlag=0 ;;Reset Flag
goto nextCell


;obtainSANameFromAL
;;Check Autoline system for the correct SA Name and replace in Excel
out SANamesCheck

[Gintaras]

'D ;; Go Down 1 cell

Mixing keys with ExcelSheet functions is not recommended because key is not always synchronous and usually not necessary.
Also usually don't need SelectRange if you use CellsToArray etc and don't use keys.

[philipq]

1) Ok so how do I write ActiveCell.Offset(0,1) in QM?
2) And if I don't use SelectRange below, how do I select the used range to be stored in cellsToArray
Macro NC VCC Release Corrections

Code:

Copy      Help

es.SelectRange("M:M" 1) ;;obtain used range
es.CellsToArray(SANamesMaster "sel") ;;Obtain Master list of Sales Advisor Names from 'Data' Sheet

3) Plus, how do I loop/offset through ExcelCells in G coloumn without first setting cursor to G1?
Any better ways

[philipq]

Anyways, I re-wrote the whole code successfully with result and shortened it, after a better understanding of how ExcelSheet itself is used.
However, I still have a problem with selecting only the used range:

Macro NC VCC Release Corrections

Code:

Copy      Help

;;Declare Variables
ARRAY(str) removeNamesList, SANamesCheckList
int counter1, counter2

int w1=act(win("Microsoft Excel - LR Sales" "XLMAIN")) ;;Obtain the particular sales file handle and activate it.
ExcelSheet esData.Init("Data") ;;Initialize an ExcelSheet object
ExcelSheet esVCC.Init("VCC Release Spool") ;;Initialize an ExcelSheet object

;;Initialize all variables to be sure
counter1=0
counter2=0

;Obtain 2 Excel lists(dynamic ranges) into arrays
esData.CellsToArray(removeNamesList "M:M") ;;Obtain list of names from 'Data' Sheet that have to be replaced/removed - Master List
esVCC.CellsToArray(SANamesCheckList "G:G") ;;Obtain list of name from 'VCC Release Sheet' that need to be checked against Master List

;arrayCheckRoutine
;;Check one array against another and change those not in Master list
for counter1 1 SANamesCheckList.len
,for counter2 1 removeNamesList.len ;;Loop through Master List Array
,,;out SANamesCheckList[0 counter1]
,,;out removeNamesList[0 counter2]
,,if SANamesCheckList[0 counter1]=removeNamesList[0 counter2] ;;Check if current SANamesCheck is in SANamesMaster list
,,,goto obtainSANameFromAL
,,,break

ret ;;Ends Macro as the 'SANamesCheck' list is now fully checked.

;obtainSANameFromAL
;;Check Autoline system for the correct SA Name and replace in Excel
out SANamesCheckList[0 counter1]

[Gintaras]

I tested, this always shows the last used row number, as displayed in Excel.

out removeNamesList.len
out SANamesCheckList.len

If other columns have more rows, the array will have some empty elements in tail. Then skip empty cells: if(array[...].len=0) continue

[philipq]

Thanks Gintaras, I have implemented your suggestion and it works fine.
However, isn't that a waste of computer processing and resources?
I mean, if we were able to choose just exactly the non-empty cells into array then we would not be processing to check 'if non-empty'...

1) Maybe you could include a '<used>' flag in cellsToArray for a particular column/row.
2) Also if you could include a 'compareArray' or 'ifInArray' function as well in QM that acts as an abstraction and therefore expedites development time; as businesses often needs to compare data a lot.

Thanks Again.

Philip

[Gintaras]

CellsToArray gets intersection of the specified range with the used range. Otherwise it would get 65536 rows if range is whole column, eg "A:A".

[philipq]

Yes that's right.
However, could cellsToArray somehow get smarter to avoid data-empty cells ignoring the formatted cells in Excel.

[Gintaras]

If you know SQL, you can use Excel files as databases. Example:
Macro Macro2580

Code:

Copy      Help

Database d.Open(d.CsExcel("$documents$\Book1.xls"))
ARRAY(str) a; int c
d.QueryArr("SELECT c FROM [Sheet1$] WHERE c IS NOT NULL" a) ;;here c is first row cell text
for c 0 a.len
,out a[0 c]

[philipq]

Excellent solution.
Yes, I do know SQL and this definitely would come in handy a lot.
Will implement this promptly.

Best Regards,
Philip

[philipq]

In the database solution you gave me, how do I make it refer to a praticular column like "G:G"?

[Gintaras]

Column name is cell text in first row. I don't remember how to use G:G, look in Excel Help.

[Gintaras]

Also look in CsExcel help and Database help. F1.

[philipq]

Thanks Gintaras, I will look into that part.

Now, in continuation to the above program, after I obtain the odd data to be replaced from Excel, I need to go to the column B cell in the same row.
All I have is the found Array variable.
Can I refer back to the Excel Address of this found arrray and thus offset to the corresponding rows B cell in Excel?
Or is there a better way to achieve all this like a total database route in QM?

The basic raw Algorithm of what I want to achieve is as below:
1) Goto cell G1 of sheet("VCC Release")
2) Start checking each cell downwards in G(dynamic) against master data in sheet("Data") column M:M(dynamic)
3) If there is an odd data, not mentioned in master list, then goto the column B in Excel of the corresponding odd data cell.
4) Take the data in B and find the correct data from CRM (separate long procedure - 2nd part I can do myself)
5) Replace the G column odd data in Excel with obtained correct data.

Kindly point me in the right direction in relation to my above last attempt.

Thanks,
Philip
Thanks

Best Regards,
Philip

[Gintaras]

When you find odd data, you know the row index. Then use function Cell to get cell from column B.

Macro Macro2582

Code:

Copy      Help

ARRAY(str) master.create(1 3); master[0 0]="zero"; master[0 1]="one"; master[0 2]="two" ;;for testing

;find cell in column G that is not in master array
ExcelSheet es.Init
ARRAY(str) a
es.CellsToArray(a "G:G")
int i j
for i 0 a.len
,str& r=a[0 i]
,if(!r.len) continue
,for(j 0 master.len) if(r=master[0 j]) break
,if(j=master.len) break ;;r not found in master
if(i=a.len) out "all G cells exist in master"; ret

;get column B text in the found row
str B=es.Cell("B" i+1) ;;+1 because this func uses 1-based index
out B
;replace with correct data
es.SetCell("correct data" "B" i+1)