OpenOffice.info

das ist ne fake liste , die adressen gibts net wirklich...außerdem sinds nich meine kollegen sondern ich habe die adressen selber gesucht die sind im netz öffentlich(wikipedia) und die logins hab ich mir ausgedacht keine sorge, die daten stehen bei den banken im impressum=) Die adressen sind von banken, zb wo die sparkasse steht und nich von leuten die da arbeiten :lol: ich überleg mir schon sachen bevor ich sie mache. Die daten findest du alle bei google, deshalb habe ich nichts verbrochen ^^, aber ich nehms mal sicherheitshalber trotzdem raus..man weiss ja nie was da einen so erwarten kann
Würde trotzdem gern wissen wie die msgbox geht .=)

Hallo
Das ist hier ein frei zugängliches Forum, deine Kollegen werden begeistert sein wenn sie hier ihre kompletten Adressangaben wiederfinden - [b]Nimm das Beispielsdok schleunigst wieder raus !![/b]

Gruß Karo

sub spaltenDurchsuchen

   'variablen Festlegen
    Dim oDoc as Object
    Dim oSheet as Object
    Dim oSearch as Object, oResult as Object
    Dim oFound as Object
    Dim n as Long
    Dim oSpalte as Object
    Dim oZeile as Object
    Dim column_index as Object
    Dim email_title as String
    Dim email_regex as String
    Dim email_column as Integer
    Dim telephone_title as String
    Dim telephone_regex as String
    Dim telephone_column as Integer
    Dim leer_regex as String
    Dim mycolor as Long
    Dim id_title as String
    Dim id_regex as String
    Dim id_column as Integer
    Dim login_title as String
    Dim login_regex as String
    Dim login_column as Integer
    Dim company_title as String
    Dim company_regex as String
    Dim company_column as Integer 
    Dim title_title as String
    Dim title_regex as String
    Dim title_column as Integer 
    Dim firstname_title as String
    Dim firstname_regex as String
    Dim firstname_column as Integer  
    Dim surname_title as String
    Dim surname_regex as String
    Dim surname_column as Integer     
    Dim street_title as String
    Dim street_regex as String
    Dim street_column as Integer 
    Dim postcode_title as String
    Dim postcode_regex as String
    Dim postcode_column as Integer    
    Dim city_title as String
    Dim city_regex as String
    Dim city_column as Integer     
    Dim country_title as String
    Dim country_regex as String
    Dim country_column as Integer    
    Dim gender_title as String
    Dim gender_regex as String
    Dim gender_column as Integer

    
          
         
        
    mycolor = RGB(255,0,0)
    email_title = "email"
    email_regex = "^[a-z0-9_\-]+(\.[a-z0-9_\-]+)*@([0-9a-z][0-9a-z\-]*[0-9a-z]\.)+([a-z]{2,4}|museum)$"
    telephone_title = "telephone"
    telephone_regex = "[0-9 / ]"
    leer_regex = "."
    id_title = "id"
    id_regex = "^[a-z0-9\_\-]+$"
    login_title = "login"
    login_regex = "^[a-z][a-z0-9@\.\-\_]+$"
    company_title = "company"
    company_regex = "^[a-zA-Z0-9ÄÜÖäüöß \(\)\-\+\_\\/]{1,}$" 
    title_title = "title"
    title_regex = "^[a-zA-ZÄÜÖäüöß \-\.\\/\(\)]{1,}$" 
    firstname_title = "firstname"
    firstname_regex = "^[a-zA-ZÄÜÖäüöß \-]{1,}$" 
    surname_title = "surname"
    surname_regex = "^[a-zA-ZÄÜÖäüöß \-]{1,}$" 
    street_title = "street"
    street_regex = "^[0-9a-zA-ZÄÜÖäüöß \-\.\\/]{1,}$" 
    postcode_title = "postcode"
    postcode_regex = "^[0-9]{5}$" 
    city_title = "city"
    city_regex = "^[a-zA-ZÄÜÖäüöß \-\\/]{1,}$" 
    country_title = "country"
    country_regex = "[a-zA-ZÄÜÖäüöß \-\\/]{1,}" 
    gender_title = "gender"
    gender_regex = "^(mr|ms)$" 
    
    
       
   'Dokument ansprechen
    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByIndex(0)
    oSpalte = ThisComponent.Sheets(0).Columns(email_column)
    oZeile = ThisComponent.Sheets(0).Rows(0)
      
    'email spalte finden
    email_column = findeSpalte("email",oZeile)
    'emails färben
    faerbeSpalte(mycolor,leer_regex,email_column,email_regex)

   
    'telefon spalte finden
    telephone_column = findeSpalte("telephone",oZeile)
    'telephone färben
    faerbeSpalte(mycolor,leer_regex,telephone_column,telephone_regex)
    
    'id spalte finden
    id_column = findeSpalte("id",oZeile)
    'id färben
    faerbeSpalte(mycolor,leer_regex,id_column,id_regex)
    
    'login spalte finden
    login_column = findeSpalte("login",oZeile)
    'login färben
    faerbeSpalte(mycolor,leer_regex,login_column,login_regex)
    
   'company spalte finden
    company_column = findeSpalte("company",oZeile)
    'company färben
    faerbeSpalte(mycolor,leer_regex,company_column,company_regex)
    
    'title spalte finden
    title_column = findeSpalte("title",oZeile)
    'title färben
    faerbeSpalte(mycolor,leer_regex,title_column,title_regex)
    
   'firstname spalte finden
    firstname_column = findeSpalte("firstname",oZeile)
    'firstname färben
    faerbeSpalte(mycolor,leer_regex,firstname_column,firstname_regex)
    
    'surname spalte finden
    surname_column = findeSpalte("surname",oZeile)
    'surname färben
    faerbeSpalte(mycolor,leer_regex,surname_column,surname_regex)
    
    
    'street spalte finden
    street_column = findeSpalte("street",oZeile)
    'street färben
    faerbeSpalte(mycolor,leer_regex,street_column,street_regex)
    
        'postcode spalte finden
    postcode_column = findeSpalte("postcode",oZeile)
    'postcode färben
    faerbeSpalte(mycolor,leer_regex,postcode_column,postcode_regex)
    
    'city spalte finden
    city_column = findeSpalte("city",oZeile)
    'city färben
    faerbeSpalte(mycolor,leer_regex,city_column,city_regex)
    

    
    'gender spalte finden
    gender_column = findeSpalte("gender",oZeile)
    'cender färben
    faerbeSpalte(mycolor,leer_regex,gender_column,gender_regex)
  
       'country spalte finden
    country_column = findeSpalte("country",oZeile)
    If country_column = -1 Then '<-------
        
       end'<-------
    End If
    'country färben
    faerbeSpalte(mycolor,leer_regex,country_column,country_regex) 
   
 
End Sub

Function findeSpalte(suchbegriff As String, oZeile As Object)
    findeSpalte = -1 '<----------
    oSearch = oZeile.createSearchDescriptor
    oSearch.SearchString = suchbegriff
    oSearch.SearchRegularExpression = true
    oResult = oZeile.findAll(oSearch)
    If isNull(oResult) Then end
    For n = 0 To oResult.count - 1
        oFound = oResult(n)
        if oFound.supportsservice("com.sun.star.table.Cell") then    'einzelne Fundzelle
            findeSpalte = ofound.celladdress.column
        end if
    next
End Function

Sub faerbeSpalte(mycolor as Long,leer_regex as String,column as Integer,regex as String)
   
    oSpalte = ThisComponent.Sheets(0).Columns(column)
    oSearch = oSpalte.createSearchDescriptor
    oSearch.SearchString = leer_regex
    oSearch.SearchRegularExpression = true 
    oResult = oSpalte.findAll(oSearch)
    For n = 0 To oResult.count - 1
        oFound = oResult(n)
        oFound.CellBackColor = mycolor
 
    Next n
    oSearch = oSpalte.createSearchDescriptor
    oSearch.SearchString = regex
    oSearch.SearchRegularExpression = TRUE
    oResult = oSpalte.findAll(oSearch)
    For n = 0 To oResult.count - 1
        oFound = oResult(n)
        oFound.CellBackColor = RGB(255,255,255)
        oZeile = ThisComponent.Sheets(0).Rows(0)
        oZeile.CellBackColor = RGB(255,255,255)
        
    Next n
   
End Sub

hey leute,
langsamm werde ich fast zum spammer, aber ein mal traue ich noch ein thread und zwar:

ich habe ein makro dass mir mittels searchdescriptor "rechtschreibfehler" sucht und markiert
kann man irgendwie mit msgbox die fehler zählen lassen dass am ende ein popp-up kommt "es wurden so und so viele fehler gefunden" und wenn keine mehr sind steht da ebend "es wurden 0 fehler gefunden"...ich weis dass es geht aber ich weiss net wie ich das auf meine stuation umschreibe, weil das immer anders läuft als gewollt.
Bitte um hilfe.

hier der code:[code]sub spaltenDurchsuchen

'variablen Festlegen
Dim oDoc as Object
Dim oSheet as Object
Dim oSearch as Object, oResult as Object
Dim oFound as Object
Dim n as Long
Dim oSpalte as Object
Dim oZeile as Object
Dim column_index as Object
Dim email_title as String
Dim email_regex as String
Dim email_column as Integer
Dim telephone_title as String
Dim telephone_regex as String
Dim telephone_column as Integer
Dim leer_regex as String
Dim mycolor as Long
Dim id_title as String
Dim id_regex as String
Dim id_column as Integer
Dim login_title as String
Dim login_regex as String
Dim login_column as Integer
Dim company_title as String
Dim company_regex as String
Dim company_column as Integer
Dim title_title as String
Dim title_regex as String
Dim title_column as Integer
Dim firstname_title as String
Dim firstname_regex as String
Dim firstname_column as Integer
Dim surname_title as String
Dim surname_regex as String
Dim surname_column as Integer
Dim street_title as String
Dim street_regex as String
Dim street_column as Integer
Dim postcode_title as String
Dim postcode_regex as String
Dim postcode_column as Integer
Dim city_title as String
Dim city_regex as String
Dim city_column as Integer
Dim country_title as String
Dim country_regex as String
Dim country_column as Integer
Dim gender_title as String
Dim gender_regex as String
Dim gender_column as Integer





mycolor = RGB(255,0,0)
email_title = "email"
email_regex = "^[a-z0-9_\-]+(\.[a-z0-9_\-]+)*@([0-9a-z][0-9a-z\-]*[0-9a-z]\.)+([a-z]{2,4}|museum)$"
telephone_title = "telephone"
telephone_regex = "[0-9 / ]"
leer_regex = "."
id_title = "id"
id_regex = "^[a-z0-9\_\-]+$"
login_title = "login"
login_regex = "^[a-z][a-z0-9@\.\-\_]+$"
company_title = "company"
company_regex = "^[a-zA-Z0-9ÄÜÖäüöß \(\)\-\+\_\\/]{1,}$"
title_title = "title"
title_regex = "^[a-zA-ZÄÜÖäüöß \-\.\\/\(\)]{1,}$"
firstname_title = "firstname"
firstname_regex = "^[a-zA-ZÄÜÖäüöß \-]{1,}$"
surname_title = "surname"
surname_regex = "^[a-zA-ZÄÜÖäüöß \-]{1,}$"
street_title = "street"
street_regex = "^[0-9a-zA-ZÄÜÖäüöß \-\.\\/]{1,}$"
postcode_title = "postcode"
postcode_regex = "^[0-9]{5}$"
city_title = "city"
city_regex = "^[a-zA-ZÄÜÖäüöß \-\\/]{1,}$"
country_title = "country"
country_regex = "[a-zA-ZÄÜÖäüöß \-\\/]{1,}"
gender_title = "gender"
gender_regex = "^(mr|ms)$"



'Dokument ansprechen
oDoc = ThisComponent
oSheet = oDoc.Sheets.getByIndex(0)
oSpalte = ThisComponent.Sheets(0).Columns(email_column)
oZeile = ThisComponent.Sheets(0).Rows(0)

'email spalte finden
email_column = findeSpalte("email",oZeile)
'emails färben
faerbeSpalte(mycolor,leer_regex,email_column,email_regex)


'telefon spalte finden
telephone_column = findeSpalte("telephone",oZeile)
'telephone färben
faerbeSpalte(mycolor,leer_regex,telephone_column,telephone_regex)

'id spalte finden
id_column = findeSpalte("id",oZeile)
'id färben
faerbeSpalte(mycolor,leer_regex,id_column,id_regex)

'login spalte finden
login_column = findeSpalte("login",oZeile)
'login färben
faerbeSpalte(mycolor,leer_regex,login_column,login_regex)

'company spalte finden
company_column = findeSpalte("company",oZeile)
'company färben
faerbeSpalte(mycolor,leer_regex,company_column,company_regex)

'title spalte finden
title_column = findeSpalte("title",oZeile)
'title färben
faerbeSpalte(mycolor,leer_regex,title_column,title_regex)

'firstname spalte finden
firstname_column = findeSpalte("firstname",oZeile)
'firstname färben
faerbeSpalte(mycolor,leer_regex,firstname_column,firstname_regex)

'surname spalte finden
surname_column = findeSpalte("surname",oZeile)
'surname färben
faerbeSpalte(mycolor,leer_regex,surname_column,surname_regex)


'street spalte finden
street_column = findeSpalte("street",oZeile)
'street färben
faerbeSpalte(mycolor,leer_regex,street_column,street_regex)

'postcode spalte finden
postcode_column = findeSpalte("postcode",oZeile)
'postcode färben
faerbeSpalte(mycolor,leer_regex,postcode_column,postcode_regex)

'city spalte finden
city_column = findeSpalte("city",oZeile)
'city färben
faerbeSpalte(mycolor,leer_regex,city_column,city_regex)



'gender spalte finden
gender_column = findeSpalte("gender",oZeile)
'cender färben
faerbeSpalte(mycolor,leer_regex,gender_column,gender_regex)

'country spalte finden
country_column = findeSpalte("country",oZeile)
If country_column = -1 Then '<-------

end'<-------
End If
'country färben
faerbeSpalte(mycolor,leer_regex,country_column,country_regex)


End Sub

Function findeSpalte(suchbegriff As String, oZeile As Object)
findeSpalte = -1 '<----------
oSearch = oZeile.createSearchDescriptor
oSearch.SearchString = suchbegriff
oSearch.SearchRegularExpression = true
oResult = oZeile.findAll(oSearch)
If isNull(oResult) Then end
For n = 0 To oResult.count - 1
oFound = oResult(n)
if oFound.supportsservice("com.sun.star.table.Cell") then 'einzelne Fundzelle
findeSpalte = ofound.celladdress.column
end if
next
End Function

Sub faerbeSpalte(mycolor as Long,leer_regex as String,column as Integer,regex as String)

oSpalte = ThisComponent.Sheets(0).Columns(column)
oSearch = oSpalte.createSearchDescriptor
oSearch.SearchString = leer_regex
oSearch.SearchRegularExpression = true
oResult = oSpalte.findAll(oSearch)
For n = 0 To oResult.count - 1
oFound = oResult(n)
oFound.CellBackColor = mycolor

Next n
oSearch = oSpalte.createSearchDescriptor
oSearch.SearchString = regex
oSearch.SearchRegularExpression = TRUE
oResult = oSpalte.findAll(oSearch)
For n = 0 To oResult.count - 1
oFound = oResult(n)
oFound.CellBackColor = RGB(255,255,255)
oZeile = ThisComponent.Sheets(0).Rows(0)
oZeile.CellBackColor = RGB(255,255,255)

Next n

End Sub




[/code]

die fehler werden mit "mycolor" markiert
Ich will das bei vielen anderen makros dann selber machen deshalb währe eine erklärung wie man sone msgbox logisch aufbaut sehr hilfreich :)
Liebe Grüße
Schnubber