Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Search question
#1
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
#2
Missing \ before some .

Code:
Copy      Help
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]
#3
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
#4
Probably possible. I could help you to create such macro if you'll provide a sequence of actions.
#5
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
#6
For which action you need help?
#7
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 Sad

So from the fourth step i think, i have (with your help Big Grin ) output and now it has to be pasted and compared in a excelsheet if possible.
#8
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?
#9
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.
#10
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)?
#11
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
#12
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?
#13
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...
#14
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.

Code:
Copy      Help
;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)
#15
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 Cry any idea?
#16
Which line hangs?
#17
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.
#18
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.

Code:
Copy      Help
;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
#19
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
#20
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.
#21
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?
#22
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
#23
Very easy.
#24
Could you please tell me how?
#25
Similarly like it shows found numbers.
#26
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?
#27
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 Big Grin
#28
there is a ton of info in the qm help section on how 2 make a macro


Forum Jump:


Users browsing this thread: 1 Guest(s)