Posts tagged ‘VBA’

Mit VBA suchen und ersetzen in Textdateien

2020-12-05, 18:27

Heute mal mal mit Excel und VBA. Ziel ist es, in einer Textdatei (komma-separiert, Punkt als Dezimalzeichen) Platzhalter durch Werte zu ersetzen, ohne dass die Datei dabei von Excel kaputt „optimiert“ wird.

Die Excel-Datei sieht dabei so aus:

Einfach nur ein paar Werte untereinander, Dezimaltrenner ist das Komma.

CSV-Quelldatei „Quelle.csv“:

SpalteA,SpalteB,SpalteC
FeldA,WertA,1.12
FeldB,WertB,1.23
FeldC,WertC,1.34
FeldD,WertD,1.45
FeldE,WertE,1.56
FeldF,WertF,1.67
FeldG,WertG,1.78
FeldH,WertH,1.89
FeldI,WertI,1.90
FeldJ,WertJ,1.95

Hinter dem Buttom im Excel liegt der folgende VBA/VBS Code, den ich bei http://www.office-loesung.de gefunden und adaptiert habe.

Option Explicit
 
Sub Schaltfläche1_Klicken()
 
    Dim fso, f, text
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile("E:\SearchReplaceVBA\Quelle.csv", 1)
    text = f.ReadAll
 
    text = Replace(text, "WertA", Replace(Cells(2, 3).Value, ",", "."))
    text = Replace(text, "WertB", Replace(Cells(3, 3).Value, ",", "."))
    text = Replace(text, "WertC", Replace(Cells(4, 3).Value, ",", "."))
    text = Replace(text, "WertD", Replace(Cells(5, 3).Value, ",", "."))
    text = Replace(text, "WertE", Replace(Cells(6, 3).Value, ",", "."))
    text = Replace(text, "WertF", Replace(Cells(7, 3).Value, ",", "."))
    text = Replace(text, "WertG", Replace(Cells(8, 3).Value, ",", "."))
    text = Replace(text, "WertH", Replace(Cells(9, 3).Value, ",", "."))
    text = Replace(text, "WertI", Replace(Cells(10, 3).Value, ",", "."))
    f.Close
 
    Set f = fso.OpenTextFile("E:\SearchReplaceVBA\Ziel.csv", 2, True)
    f.Write (text)
    f.Close
 
End Sub

Das Ergebnis „Ziel.csv“ sieht dann so aus:

SpalteA,SpalteB,SpalteC
FeldA,2.9502085126608,1.12
FeldB,73.1026744983578,1.23
FeldC,55.250551974564,1.34
FeldD,33.2285937834519,1.45
FeldE,42.2559662206547,1.56
FeldF,24.6506686140567,1.67
FeldG,54.0201369859298,1.78
FeldH,26.9352342768415,1.89
FeldI,51.1782693678183,1.90
FeldJ,87.9325752371774,1.95

Es geht natürlich noch viel eleganter, indem man zum Beispiel Ranges nutzt, als Proof-of-Concept reicht dies jedoch schon aus.

Mit named Ranges würde die Lösung so aussehen:

Option Explicit
 
Sub Schaltfläche1_Klicken()
 
    Dim fso, f, text
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile("E:\SearchReplaceVBA\Quelle.csv", 1)
    text = f.ReadAll
 
    text = Replace(text, "WertA", Replace(Range("FeldA").Value, ",", "."))
    text = Replace(text, "WertB", Replace(Range("FeldB").Value, ",", "."))
    text = Replace(text, "WertC", Replace(Range("FeldC").Value, ",", "."))
    text = Replace(text, "WertD", Replace(Range("FeldD").Value, ",", "."))
    text = Replace(text, "WertE", Replace(Range("FeldE").Value, ",", "."))
    text = Replace(text, "WertF", Replace(Range("FeldF").Value, ",", "."))
    text = Replace(text, "WertG", Replace(Range("FeldG").Value, ",", "."))
    text = Replace(text, "WertH", Replace(Range("FeldH").Value, ",", "."))
    text = Replace(text, "WertI", Replace(Range("FeldI").Value, ",", "."))
    text = Replace(text, "WertJ", Replace(Range("FeldJ").Value, ",", "."))
 
    f.Close
 
    Set f = fso.OpenTextFile("E:\SearchReplaceVBA\Ziel.csv", 2, True)
    f.Write (text)
    f.Close
 
 
End Sub

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Schlagwörter: , , ,
Category: Programmierung, MS Office & VBA  |  Kommentare deaktiviert für Mit VBA suchen und ersetzen in Textdateien

Eine IN Funktion für Excel VBA

2017-11-12, 14:55

Hier basierend auf einem Beispiel von Microsoft eine IN-Funktion. Mit dieser lässt sich prüfen, ob ein String in einem zusammengesetzten String enthalten ist.

Option Explicit
 
Function Contains(needle As String, haystack As String, separator As String) As Boolean
 
Dim rv As Boolean, lb As Long, ub As Long, i As Long, field() As String
 
field = Split(haystack, separator)
 
    lb = LBound(field)
    ub = UBound(field)
    For i = lb To ub
        If field(i) = needle Then
            rv = True
            Exit For
        End If
    Next i
    Contains = rv
End Function

Nachtrag: Möchte man prüfen, ob ein Wert in einer Range vorhanden ist, kann man die folgende User-Defined Function nutzen:

Function InRange(needle As Variant, haystack As Range) As Boolean
Dim rv As Boolean, cell As Range
 
    For Each cell In haystack
        If cell = needle Then
            rv = True
            Exit For
        End If
    Next cell
    InRange = rv
 
End Function

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Schlagwörter: , ,
Category: Programmierung, MS Office & VBA  |  Kommentare deaktiviert für Eine IN Funktion für Excel VBA

Mit VBA den Namen des Nutzers ermitteln

2017-10-22, 15:42

Ich arbeite momentan an einer kleinen Excel-Anwendung, um die wöchentliche Bestellung von meinen Kollegen und mir bei „Danielz Foodtruck“ (https://www.facebook.com/Danielz-Food-Truck-709172459137209/) zu optimieren. Mit reinem VBA kommt man leider nur an den Login-Namen, nicht aber an den eigentlichen Namen des Nutzers:

Sub showUsername()
   MsgBox VBA.Environ("Username")
End Sub

Die Information steht jedoch im Active Directory, mit ein paar Zeilen VBA (Quelle: https://community.spiceworks.com/topic/361258-using-vba-to-report-user-s-full-name-maybe-from-ad) kann man sie abfragen:

Function GetUsername()
     Set objAD = CreateObject("ADSystemInfo")
     Set objUser = GetObject("LDAP://" & objAD.UserName)
     GetUsername = objUser.DisplayName
End Function

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Schlagwörter:
Category: Programmierung, MS Office & VBA  |  Kommentare deaktiviert für Mit VBA den Namen des Nutzers ermitteln

Excel Funktion zum Zerlegen eines Strings

2015-03-21, 20:22

Die folgende Excel-Funktion ist nützlich, um innerhalb von Excel Strings in ihre Bestandteile zu zerlegen.


Function SplitteString(zeichenkette, separator, vorkommen) As String
Dim feld() As String
feld = Split(zeichenkette, separator)
SplitteString = feld(vorkommen - 1)
End Function

split

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Schlagwörter: , ,
Category: Programmierung, MS Office & VBA  |  Kommentare deaktiviert für Excel Funktion zum Zerlegen eines Strings

Excel: Prüfen, ob eine Zelle eine Formel enthält

2012-04-04, 21:59

Excel stellt leider keine Funktion bereit, um das Vorhandensein einer Formel in einer Zelle zu prüfen. Behelfen kann man sich aber mit einer selbst gestrickten Excel-Funktion (gefunden bei http://www.vbaexpress.com/kb/getarticle.php?kb_id=324):

Option Explicit 
 
Function ISFORMULA(cel As Range) As Boolean 
    ISFORMULA = cel.HasFormula 
End Function

isFormel.xlsm

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Schlagwörter: ,
Category: MS Office & VBA  |  Kommentare deaktiviert für Excel: Prüfen, ob eine Zelle eine Formel enthält

Alle Diagramme einer Excel-Mappe als PDF exportieren

2012-03-23, 20:58

Aus Interesse heraus hab ich heute versucht, per Excel VBA alle Diagramme eines Excel-Sheets als PDF zu exportieren. Google brachte als ersten Treffer einen Blogeintrag, der mich auf die richtige Fährte brachte: http://cschleiden.wordpress.com/2009/09/28/howto-export-excel-charts-as-pdf-to-include-in-latex-document/. Dieser Eintrag, zusammen mit einem aufgezeichneten Makro eines manuellen Export-Vorgangs und einige Codeschnipsel von http://www.vbaexpress.com/kb/getarticle.php?kb_id=482 haben mich innerhalb von 20 Minuten zu dem folgenden VBA Code gebracht:

Sub ExportAllCharts()
 
    If ActiveSheet.ChartObjects.Count > 0 Then
        For Each Diagram In ActiveSheet.ChartObjects
            ActiveSheet.ChartObjects(Diagram.Name).Activate
            Filename = ActiveChart.Name
            ActiveChart.Axes(xlValue).MajorGridlines.Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Uwe\Desktop\" & Filename, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next Diagram
    End If
 
End Sub

Was macht dieser Code? Wenn überhaupt Diagramme im aktuellen Worksheet vorhanden sind, dann wird für jedes gefundene Diagramm der Dateiname bestimmt (als Namen des Diagramms, z.B. „Tabelle1 Diagramm 1.pdf“ oder „Tabelle1 Diagramm 2.pdf“) und die Datei auf dem Desktop als PDF abgelegt. Der Code ist noch rudimentär und kann noch beliebig erweitert werden.

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Schlagwörter: , ,
Category: LaTeX, Tipps & Tricks, MS Office & VBA  |  Kommentare deaktiviert für Alle Diagramme einer Excel-Mappe als PDF exportieren