VBA Excel Makro: Tabellenblatt als CSV-Datei speichern

VBA eignet sich hervorragend, um bestimmte wiederkehrende Aufgaben im Bereich von Microsoft Office zu automatisieren. So kann es sein, dass die Daten einer Excel-Datei bzw. eines Tabellenblatt als CSV-Datei speichern möchte oder einfach nur zur weiteren Verarbeitung als CSV exportieren möchte. Je nach Bedarf und Anwendungsfall gibt es hier mehrere Möglichkeiten.

Die nachfolgend gezeigten Code-Beispiele sind dann natürlich auf den individuellen Anwendungsfall anzupassen. Auch kann es unterschiedliche Anforderungen geben zur Formatierung in der CSV-Datei und dem Trennzeichen. Ggf. müssen die Werte auch in Anführungszeichen gesetzt werden, wenn das Trennzeichen, wie ein “Komma” auch innerhalb der Werte vorkommen kann.

Beispiel Excel-Datei

Die nachfolgend aufgeführten Code-Beispiele spielen wir anhand einer kleinen Excel-Datai durch, die die verschiedene formatierte Daten zu Produkten und deren Preise enthält. Daran sieht man dann auch gleich schön, was mit der Formatierung beim Export in eine *.csv-Datei passieren kann.

In allen Codebeispielen ist eine Error-Handling (Application.DisplayAlerts) eingefügt, um Fehlermeldungen während des speicherns zu unterdrücken. Man kann den Code aber auch weglassen.

Gespeichert wird die CSV-Datei unter demselben Pfad, wo auch die Excel-Datei liegt (fileNameCSV = ThisWorkbook.Path). Dies kann man aber beliebig anpassen.

Tabellenblatt als CSV-Datei speichern

Mit dem folgenden VBA-Code wird ein komplettes Tabellenblatt als neue CSV-Datei gespeichert. Das ist praktisch und macht den VBA-Code relativ schlank

Code

In unserem Beispiel heißt das Tabellenblatt “ProductList“. Deswegen aktivieren wir dieses mit diesem Namen vor dem Export.

Die Hauptfunktion erfüllt in diesem Fall die Methode SaveAs. Mit dem Parameter FileFormat:=xlCSV legt man fest, dass es als CSV gespeichert werden soll. Von Bedeutung ist in diesem Fall auch der Parameter Local. Setzt man diesen Parameter nicht, dann ist der Standardwert false und es werden die Sprach- und Ländereinstellungen vom VBA-Editor genommen, welche standardmäßig English/USA sind. Das kann dann dazu führen, dass wenn die Währung als Euro formatiert ist in der CSV-Datei dann Dollar stehen.

Sub exportWorksheetToCSV()
    
    Dim fileNameCSV As String
    Dim myWorksheet As Workbook
    
    'use this to suppress error messages during running the macro
    Application.DisplayAlerts = False
    On Error GoTo errorhandling
    
    'define path and filename of csv file here
    fileNameCSV = ThisWorkbook.Path & "\" & "products.csv"

    'activate the regarding worksheet
    ThisWorkbook.Sheets("ProductList").Activate
    ActiveSheet.Copy
    Set myWorksheet = ActiveWorkbook
    'use parameter Local to get local settings of excel, defaulf value is false
    '(VBA local setting is used that by default is Englisch (USA)
    myWorksheet.SaveAs Filename:=fileNameCSV, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    myWorksheet.Close
    
errorhandling:
    Application.DisplayAlerts = True
End Sub

Ergebnis

In den nachfolgenden Bildern sehen wir, dass beim Speichern der CSV-Daten versucht wird die Formatierung beizubehalten. Jedoch war in diesem Fall der Parameter Local auf False gesetzt. Dies führt dazu, dass aus den Euro-Zeichen plötzlich Dollar-Zeichen in der CSV-Datei werden.

Setzt man Local auf True dann erhält man folgende Ausgabe. Man sieht, dass Dollar-Zeichen ist verschwunden. Stattdessen ist jetzt <0x80> ausgegeben, was laut ASCII-Tabelle dem €-Symbol entspricht.

Nur bestimmten Bereich eines Tabellenblatt als CSV exportieren

Diese Variante ist der zuvorgenannten Variante sehr ähnlich nur mit dem Unterschied, dass man anstelle des kompletten Tabellenblatt nur einen bestimmten Bereich (“Range“) als CSV speichert. Dadurch kann man etwas genauer bestimmen, was exportiert wird und was ausgeschlossen wird.

Code

Der Bereich wird über die Variable myRange definiert. Danach kopiert man den Inhalt von myRange in ein per VBA neu erzeugtes Tabellenblatt. Der Rest ist dann wie beim vorherigen Beispiel einfach nur speichern des neuen Tabellenblatts.

Sub exportRangeToCSV()
    
    Dim fileNameCSV As String
    Dim myWorkbook As Workbook
    Dim myRange As Range
    
    'use this to suppress error messages during running the macro
    Application.DisplayAlerts = False
    On Error GoTo errorhandling
    
    'define path and filename of csv file here
    fileNameCSV = ThisWorkbook.Path & "\" & "products_range.csv"
    'define and copy range to new worksheet
    Set myRange = Range("A1:E5")
    myRange.Copy
    
    Set myWorkbook = Application.Workbooks.Add(1)
    myWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
    'use parameter Local to get local settings of excel, defaulf value is false
    '(VBA local setting is used that by default is Englisch (USA)
    myWorkbook.SaveAs Filename:=fileNameCSV, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    myWorkbook.Close
    
errorhandling:
    Application.DisplayAlerts = True
End Sub

Ergebnis

Das Ergebnis sieht jedoch etwas anders aus. Durch das Kopieren des Bereiches in eine neues Tabellenblatt geht die Formatierung verloren. Deswegen sieht man hier als Ausgabe keine Währungssymbole oder Prozentzeichen, sondern nur die Rohwerte.

CSV-Daten manuell zusammenstellen und ausgeben

Bei der nachfolgenden Code-Variante werden die CSV-Daten mittels For-Schleifen manuell erstmal in einer String-Variablen zusammengestellt und dann in eine Datei gespeichert. Diese Methode bietet die größte Flexibilität, weil man sich einerseits genau die Excel-Felder auswählen kann, die exportiert werden sollen und andererseits weil man durch Einfügen von zusätzlichen Code die Werte genau so formatieren kann, wie man diese später in der CSV benötigt. So kann man zum Beispiel selbst festlegen, ob man ein Währungszeichen oder ein Prozentzeichen mit exportiert oder ob man nur Rohwerte exportiert.

In dieser Variante kann man auch das Separationszeichen selbst festlegen.

Code

Sub exportRangeManuallyToCSV()
    Dim FSO As New FileSystemObject ' need to set a reference to the VB "Microsoft Scripting runtime" script run-time library
    Dim fileNameCSV As String
    Dim myWorkbook As Workbook
    Dim myRange As Range
    Dim rowNum As Integer
    Dim colNum As Integer
    Dim csvText As String
    
    
    'use this to suppress error messages during running the macro
    Application.DisplayAlerts = False
    On Error GoTo errorhandling
    
    'define path and filename of csv file here
    fileNameCSV = ThisWorkbook.Path & "\" & "products_range_manuelly.csv"
    'define and copy range to new worksheet
    Set myRange = Range("A1:E5")
    rowNum = myRange.Rows.Count
    colNum = myRange.Columns.Count
    csvText = ""
    
    For i = 1 To rowNum
        For j = 1 To colNum
            csvText = csvText & Chr(34) & myRange(i, j).Value & Chr(34) & Chr(59)
        Next
        'remove last separator sign
        csvText = Left(csvText, Len(csvText) - 1)
        csvText = csvText & vbCrLf
    Next
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToCreate = FSO.CreateTextFile(fileNameCSV)
    FileToCreate.Write csvText
    FileToCreate.Close
    
errorhandling:
    Application.DisplayAlerts = True
End Sub

Bei diesem Code durchläuft man spalten- und zeilenweise den gewünschten Excel-Bereich (Variable myRange). Über Chr(34) werden die Excel-Werte einer Zelle in Anführungszeichen gesetzt. Chr(59) ist das Separationszeichen, in diesem Fall ein Semikolon.

Das Setzen in Anführungszeichen kann wichtig sein, wenn die Werte dasselbe Zeichen wie das Separationszeichen enthalten.

Ergebnis

Führt man dann dieses VBA-Makro aus erhält man die folgenden CSV-Daten.

Da man nur auf die Value-Eigenschaft der jeweiligen Excel-Zelle zugreift, hat man auch hier erstmal nur die Rohwerte, also ohne Währungs- oder Prozenz-Formattierung, stehen. Man hat aber hier die Möglichkeit im Code selbst festzulegen, welche Formatierungen mit ausgegebenen werden indem man den String csvText entsprechend anpasst.