Posts: 128
Threads: 48
Joined: Jan 2007
Hi there,
I'm trying to find out if it's possible to give findrx a kind of string to find like xx.xx.xx.xx that has to match? If possible later compare the first like 10 or 20 or 25 (so 10.xxx.xxx.xxx will be found but 10.xxx.xxx not and 25.xxx.xxx.xx also)
I have created something like this:
findrx(s1 "((10|20|25)\.\d{1,3}.\d{1,3}.\d{1,3})" 0 4 a) ;;exit if not found
but in this search
10.xx.xxx also is found and is not handy for the use i'm preparing it for.
TIA
Posts: 12,092
Threads: 142
Joined: Dec 2002
Missing \ before some .
str s1="aaaa 10.123.456.789 bbbb[]aaaa 20.123.456 bbbb[]aaaa 25.123.456.789 bbbb"
ARRAY(str) a
if(findrx(s1 "(10|20|25)\.\d{1,3}\.\d{1,3}\.\d{1,3}" 0 4 a)=0) ret ;;exit if not found
;if(findrx(s1 "(10|20|25)(?:\.\d{1,3}){3}" 0 4 a)=0) ret ;;this also works
int i
for i 0 a.len
,out "%s, %s" a[0 i] a[1 i]
Posts: 128
Threads: 48
Joined: Jan 2007
OK THX
Is it possible to store the result in a excelsheet with the date of the day and if the sheet/book doesn't exist it creates one?
Also it would be handy if it compares the data stored in this sheet with the found results.. and pops a message if its the same..
You would be the king if its possible...
TIA
Posts: 12,092
Threads: 142
Joined: Dec 2002
Probably possible. I could help you to create such macro if you'll provide a sequence of actions.
Posts: 128
Threads: 48
Joined: Jan 2007
Something like this?
Input => search for whatever => found => lookup in excelsheet with date of today, if sheet not excist then create and store and store the found => if found in sheet then give message => if not found then store and continue with macro.
If this is not what you want let me know, you did already to much.
Freggel
Posts: 12,092
Threads: 142
Joined: Dec 2002
For which action you need help?
Posts: 128
Threads: 48
Joined: Jan 2007
Sorry,
I'm not a programmer as i said before...
I want the things the macro has found compared with a excelsheet and i don't know how to do that 
So from the fourth step i think, i have (with your help  ) output and now it has to be pasted and compared in a excelsheet if possible.
Posts: 12,092
Threads: 142
Joined: Dec 2002
Comparing is not simple, and I need more information. Eg, what is "found"? Is it simple string, number, 1dim array, 2dim array, etc? How the data is stored in the sheet?
Posts: 128
Threads: 48
Joined: Jan 2007
Sorry again..
Found is the data, what came out of this:
findrx(s1 "(numbers)(?:\.\d{1,3}){3}" 0 4 a)
int i
for i 0 a.len
out "%s, %s" a[0 i]
----------
So the output are ip's ; the sheet can be a single column thing with these ip's and these compared with the result of the new found one.
Posts: 12,092
Threads: 142
Joined: Dec 2002
There are several ways a macro can work with Excel sheets:
1. use keyboard in Excel.
2. use ExcelSheet class and other Excel functions in Excel.
3. use database functions, which manipulate excel workbook file directly, not through Excel.
What would be better for you - 2 (in Excel) or 3 (directly in file)?
Posts: 128
Threads: 48
Joined: Jan 2007
IF possible than would option 3 be handier i think and i think it is faster too. So if you can give me a sample of code i can puzzle with i would be very thankfull....
Freggel
Posts: 12,092
Threads: 142
Joined: Dec 2002
I tried to create sheet using database functions, but unsuccessfully. It would work with Access databases, but not with Excel workbooks. Will use Excel functions, OK?
Posts: 128
Threads: 48
Joined: Jan 2007
Everything is fine with me, i'm also very happy that you are doing so much in your free time to help me this is very kind of you...
Posts: 12,092
Threads: 142
Joined: Dec 2002
This is not complete but you will know how to complete it. This code compares whole data if it match exactly. Maybe you need to find duplicate IPs, i don't know.
;create array for testing
str s1="aaaa 10.123.456.789 bbbb[]aaaa 20.123.456 bbbb[]aaaa 25.123.456.789 bbbb"
ARRAY(str) a
if(findrx(s1 "(10|20|25)(?:\.\d{1,3}){3}" 0 4 a)=0) ret
;int i
;for i 0 a.len
,;out a[0 i]
;ret
;________________________________
int j sheetexists(1) dataexists
;create sheet name from date
str sheet.time("%x")
sheet.replacerx("[\\/]" "-")
;connect to Excel
ExcelSheet es.Init
Excel.Application app=es.ws.Application
;try to connect to today's sheet
es.ws=app.Worksheets.Item(sheet)
err ;;does not exist. create new
,sheetexists=0
,es.ws=app.Worksheets.Add()
,es.ws.Name=sheet
,
if sheetexists
,;get first column and compare with a
,ARRAY(str) a2
,es.GetCells(a2 "A:A")
,if(a2.len=a.len)
,,for(j 0 a.len) if(!(a[0 j]~a2[0 j])) break
,,if(j=a.len) dataexists=1
out "sheet exists=%i, dataexists=%i" sheetexists dataexists ;;debug
;here insert your message box etc
if !sheetexists
,;populate sheet
,for j 0 a.len
,,es.SetCell(a[0 j] 1 j+1)
Posts: 128
Threads: 48
Joined: Jan 2007
THX
First time when sheet doesn't exist it works GREAT, but when sheet is filled with some numbers and i run it a second time it hangs  any idea?
Posts: 12,092
Threads: 142
Joined: Dec 2002
Posts: 128
Threads: 48
Joined: Jan 2007
Sorry,
I think it had to do with the large amount that had to be compared or something, but there is nothing compared, if the string and the excel are the same the outputbox gives only "sheet exists=1, dataexists=0 " and nothing is added to the sheet. I changed the range to compare to a1:a200 for faster handling. also when i add a new number to the string it doesn't recognize it and doesn't add it to the sheet.
Freggel.
Posts: 12,092
Threads: 142
Joined: Dec 2002
Probably this is what you need. It adds found strings that did not exist in sheet, and displays found strings that did exist in sheet.
;create array for testing
str s1="aaaa 10.123.456.789 bbbb[]aaaa 20.123.456 bbbb[]aaaa 25.123.456.789 bbbb"
ARRAY(str) a
if(findrx(s1 "(10|20|25)(?:\.\d{1,3}){3}" 0 4 a)=0) ret
;int i
;for i 0 a.len
,;out a[0 i]
;ret
;________________________________
int j k sheetexists(1)
str s2
ARRAY(str) a2 afound
;create sheet name from date
str sheet.time("%x")
sheet.replacerx("[\\/]" "-")
;connect to Excel
ExcelSheet es.Init
Excel.Application app=es.ws.Application
;try to connect to today's sheet
es.ws=app.Worksheets.Item(sheet)
err ;;does not exist. create new
,sheetexists=0
,es.ws=app.Worksheets.Add()
,es.ws.Name=sheet
,
if sheetexists
,;search for each element and add found elements to afound
,es.GetCells(a2 "A:A")
,for j 0 a.len
,,for(k 0 a2.len) if(a[0 j]~a2[0 k]) break
,,if(k<a2.len)
,,,afound[afound.redim(-1)]=a[0 j]
,,,a[0 j].all ;;clear
,
,;message
,if(afound.len)
,,for(j 0 afound.len) s2.formata("%s[]" afound[j])
,,mes s2 "Found" ""
;add all that did not exist
k=a2.len+1
for j 0 a.len
,if(a[0 j].len) es.SetCell(a[0 j] 1 k)
,k+1
Posts: 128
Threads: 48
Joined: Jan 2007
That is what i want, but it doesn't work, did it work with you? I tried it with office 2003 and i don't get a 'found' message and nothing is added to the sheet after the first time i ran it. The first time with an empy sheet it works perfect. So the found data is copied to the sheet, when i change 1 of the numbers in the string to verify if it works, it gives no feedback.
TIA
Posts: 12,092
Threads: 142
Joined: Dec 2002
Yes it worked well. First time it creates new sheet and adds 2 found strings. Then I change one of them in Excel and run the macro again. It shows message with 1 string that existed in sheet, and adds other string to sheet. Office 2003 too.
Posts: 128
Threads: 48
Joined: Jan 2007
I'm sorry you're right i didn't look :oops:
Only one thing now and i'm happy as was it christmas, i'm getting no feedback (message) that a number has been found, is this happening with your test?
Posts: 128
Threads: 48
Joined: Jan 2007
THX it works!
Is it easy to give a message for the numbers who aren't found in the sheet but in the string?
TIA
Posts: 12,092
Threads: 142
Joined: Dec 2002
Posts: 128
Threads: 48
Joined: Jan 2007
Could you please tell me how?
Posts: 12,092
Threads: 142
Joined: Dec 2002
Similarly like it shows found numbers.
Posts: 128
Threads: 48
Joined: Jan 2007
Hello again,
THX for your help last time,
I noticed someting, when an ip-number has a xx.xxx.xxx.xxx (like 10.123.123.123) it isn't recognized by the compare string and doesn't give a message that it has been used before and ads it to the sheet.
Any idea what causes this?
Posts: 4
Threads: 1
Joined: Mar 2007
ok hello to both of ya i am here to find out how to make a macro i have been intrested in making them for some time now and i wonder if any of u could help me please would be much appreciated as i am real intrested in doing it thank you
dragonfire18368
Posts: 1,338
Threads: 61
Joined: Jul 2006
there is a ton of info in the qm help section on how 2 make a macro
|
