# Spalte in Excel per VBA formatieren



## hanshans (19. Mai 2010)

Hallo an alle,

ich habe folgendes Excelproblem. Ich lese über ein Makro eine CSV ein, bearbeite die Daten und spucke eine bearbeitete CSV, die seperat gespeichert wird, wieder aus. 
In einer Spalte stehen jede Menge IDs. Haben diese jedoch zu viele Ziffern, ändert Excel den Zellenwert in sowas 8,76E+11 um. Ich brauche die ID jedoch ausgeschrieben. Die formatierung ist auf standart gesetzt. 

Wie kann ich eine ganze Spalte per VBA in Zahl ohne Nachkommastelle umformatieren


Danke im Vorraus.


----------



## tombe (19. Mai 2010)

Schau mal in der Excel Hilfe nach der STR-Funktion, damit werden numerische Werte in Strings umgewandelt.

Gruß Thomas


----------



## hanshans (19. Mai 2010)

Danke für die schnelle Antwort, aber das hilft mir leider nicht. Wenn ich die CSV manuell formatiere, dann komm ich lediglich über 2 Formatierungswege zum richtigen ergebnis. Einmal wen ich die Zellen als Zahl mit 0 Nachkommastellen formatiere oder als Bruch. Wenn ich die Zelle als Text formatiere, dann wir die zelle trotzdem in der oben beschriebenen Form angezeigt. 

Es kommt dazu, dass ich VBA vorher nie benutzt habe. Also frag ich einfach mal in den Raum ob ich mit diesem Code grundsätzlich richtig liege:

For i = 1 To Anzahl_Zeilen Step 1
        Range(i, 6).Value = Format(Range(i, 6).Value, "0.#")
Next i

Hab ich mir aus dem schon vorhandenen code zusammen gereimt. leider fehlt mir die zeit mich richtig in die Materie reinzuarbeiten. Der VB-Code ansich ließt sich wie ein Buch, aber mit dem Excel-Zusatz komm ich nicht klar.


----------



## tombe (19. Mai 2010)

An sich ist der Code schon richtig.

Wenn du ein bisschen mehr zeigst, z.B. von der CSV Datei oder vom restlichen Code findet sich bestimmt eine Lösung.

Die ganze Spalte kannst du auch so formatieren:


```
Range("A:A").Select
Selection.NumberFormat = "0.#"
```

Thomas


----------



## hanshans (19. Mai 2010)

Sub Create_Copy()
ActiveWorkbook.Worksheets(1).Cells.Copy    'kopiert aus Tabelle1 die Werte
   With ThisWorkbook.ActiveSheet.Cells        'fügt in leeres Hauptblatt die Werte ein
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlFormats
   End With

   Application.CutCopyMode = False

   ActiveWorkbook.Close                       'schließt die Quelldatei

   ThisWorkbook.ActiveSheet.Name = Asheet     'benennt Tabelle1 wie die Quelltabelle

   Application.DisplayAlerts = False

   For i = 1 To Anzahl_Zeilen Step 1
        Range(i, 5).Value = Format(Range(i, 5).Value, "0.#")

   Next i


   ActiveWorkbook.SaveAs Filename:=NewDataName & ".xls"  'speichert das Workbook als "bereinigt"
   Application.DisplayAlerts = True
End Sub

Funktioniert leider nicht wie gewünscht  hab wohl irgendwo ein Denkfehler. Anzahl_Zeilen ist eien Funktion mit Rückgabewert int.


----------



## hanshans (19. Mai 2010)

Hab den letzten Post zu spät gelesen. Hier meine aktueller Ansatz, der aber leider, warum auch immer, nicht funktioniert. 

Sub Create_Copy()
ActiveWorkbook.Worksheets(1).Cells.Copy    'kopiert aus Tabelle1 die Werte
   With ThisWorkbook.ActiveSheet.Cells        'fügt in leeres Hauptblatt die Werte ein
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlFormats
   End With

   Application.CutCopyMode = False

   ActiveWorkbook.Close                       'schließt die Quelldatei

   ThisWorkbook.ActiveSheet.Name = Asheet     'benennt Tabelle1 wie die Quelltabelle

   ThisWorkbook.ActiveSheet.Range("E:E").Select
   Selection.NumberFormat = "0.#"

   Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs Filename:=NewDataName & ".xls"  'speichert das Workbook als "bereinigt"
   Application.DisplayAlerts = True
End Sub


----------



## tombe (19. Mai 2010)

Also ich habe es eben so probiert und es klappt:


```
'For i = 1 To Anzahl_Zeilen Step 1
'Range(i, 5).Value = Format(Range(i, 5).Value, "0.#")

'Next i
'obige Zeilen auskommentiert das sie nicht funktionieren und dafür diesen Code eingegeben
Range("A:A").Select
Selection.NumberFormat = "000000"
```

Ok, falsche Spalte und falsche Formatierung aber von Prinzip her klappt es damit bei mir!!

Was geht bei dir nicht ?


----------



## hanshans (19. Mai 2010)

Die so erzeugte CSV zeigt in der Spalte E flogende ID 902803576601 in dieser Form an 9,028E+11 usw. Die ID soll aber ausgeschrieben werden. Wenn ich nachträglich die CSV öffne und die Formatierung in der zeile anschaue, hat sich nichts geändert..
Ich verzweifel langsam. Kann mir jemand einen guten Link schicken, der mich zu einem Tutorial führt, bei dem ich das mit den Workbooks besser verstehen lerne? Ich kann mir eigentlich nur vorstellen, dass es noch daran liegt..


----------



## tombe (19. Mai 2010)

Excel hat damit ein generelles Problem. Es wird erkannt das der Inhalt der Zelle eine Zahl ist. Da sie für Excel aber zu groß ist um sie in der von dir gewünschten Form darzustellen wird sie umgewandelt.

Wenn du die Zellen manuell formatierst (Benutzderdefiniert) "###############" oder per Makro Selection.NumberFormat = "###############" werden sie richtig dargestellt.
Hier sind es jetzt 15 Zeichen, du kannst aber auch 20 nehmen wenn es noch längere IDs geben sollte.


----------



## hanshans (19. Mai 2010)

Ich danke dir wirklich für deine Hilfe, aber ich bekomm das Problem einfach nicht in den Griff. Folgende Sachlage. Wenn ich an der formatierung garnichts mache, dann gehen in der veräünderten csv die letzten ziffern der betroffenen ids komplett verloren. ganz schlecht.

wenn ich die zellen manuell formatier in zahlen ohne nachkomma stelle und dann schliesse und wieder öffne, ist die zelle wieder wie vorher´. Wenn ich sie als text in der original csv formatiere ändert sich erst mal nichts. kopier ich dann aber den inhalt und füge ihn manuell in die zelle ein, klappt es auf einmal.  Warum das denn bitte? NUR, obwohl ich, wie iben beschrieben, die formatierung ja mit kopiere, ändert sich das auf einmal wieder in der abgeänderten csv.. egal wie ich es mache, es geht schief.. Zu schade, dass das vertrauliche daten sind, sonst würde ich hier einfach mal alles posten 

das ist wie verhext....


----------



## tombe (20. Mai 2010)

Ok, auch wenn du (noch) nicht auf meine Antwort via PN reagiert hast, hier nochmal eine Lösung für dein Problem.


```
Sub csv_open()
'Aufbau der CSV-Datei: ID;Text;Zahl
'zum Beispiel so
'902803576601;Beliebiger Text 1 als Artikelbeschreibung oder so;99,99
'902803576602;Beliebiger Text 2 als Artikelbeschreibung oder so;111,11
'902803576603;Beliebiger Text 3 als Artikelbeschreibung oder so;222,22

'Zeilenzähler für die DO/LOOP-Schleife
zeile = 1
'Array zum Einlesen der CSV-Datei
Dim inhalt() As String
'freie Dateinummer zum Öffnen
datei = FreeFile
'Öffnen Dialog um die CSV auszuwählen
dateiname = Application.GetOpenFilename("CSV-Dateien (*.csv), *.csv,Text-Dateien (*.txt), *.txt")
'wenn keine Datei ausgewählt wurde, wird Sub verlassen
If dateiname = False Then
    Close
    Exit Sub
Else
'ansonsten wird die Datei geöffnet
    Open dateiname For Input As #datei
End If
'In Spalte A werden die IDs eingelesen, deshalb bereits hier die Spalte (mit 20 Stellen) formatieren
Range("A:A").Select
Selection.NumberFormat = "####################"
'Datei zeilenweise einlesen und Angaben eintragen
Do
    Line Input #datei, ReadLine
    
    If ReadLine <> "" Then
        inhalt() = Split(ReadLine, ";")
        Cells(zeile, 1) = inhalt(0) 'Spalte A bekommt die bis zu 20 Zeichen langen IDs
        Cells(zeile, 2) = inhalt(1) 'Spalte B bekommt hier einen beliebigen Text
        Cells(zeile, 3) = inhalt(2) 'Spalte C bekommt hier eine Zahl/den Preis
        zeile = zeile + 1
    End If
Loop Until EOF(datei)

Close
End Sub
```

Wenn der obige Code ausgeführt wird, wirst du zuerst aufgefordert eine CSV- bzw. TXT-Datei auszuwählen um sie zu öffnen. Anschließend wird im Beispiel die Spalte A so formatiert das die eingelesenen IDs im gewünschten Format erhalten bleiben und dann die Datei eingelesen.

Ich habe es bei mir mit verschiedenen IDs probiert. Sie werden korrekt eingelesen, dargestellt und man kann sie auch in Excel bearbeiten/ändern ohne das die Formatierung verlorengeht.

Versuche es damit nochmal ansonsten um nochmal auf deine Frage zu antworten: Ja du kannst mir die Originaldatei mal zuschicken und ich teste es damit auch. Meine eMail Adresse hast du ja.

Gruß Thomas


----------



## TheRamones (6. März 2012)

Hallo Thomas,

habe ein ähnliches Problem und habe deinen o.g. vb code genutzt. Das hat soweit auch alles ganz gut funktioniert.
Allerdings wird in deinem Beispiel nur eine Zeile aufgelistet. Da in meiner csv. datei mehrere Zeilen vorhanden sind und diese auch benötigt werden, müsste ich nur wissen, welche Veränderung ich in deinem code vornehmen müsste, damit diese auch übernommen werden.
Ich hoffe du kannst mir helfen.

Viele Grüße Chris


----------



## HonniCilest (6. März 2012)

Ich würde vor die Zahl ein Hochkomma setzen, dieses wird in der Zelle selbst nicht dargestellt und zwingt Excel dazu die Zahl als Text zu behandeln.


----------



## Zvoni (7. März 2012)

TheRamones hat gesagt.:


> Hallo Thomas,
> 
> habe ein ähnliches Problem und habe deinen o.g. vb code genutzt. Das hat soweit auch alles ganz gut funktioniert.
> Allerdings wird in deinem Beispiel nur eine Zeile aufgelistet. Da in meiner csv. datei mehrere Zeilen vorhanden sind und diese auch benötigt werden, müsste ich nur wissen, welche Veränderung ich in deinem code vornehmen müsste, damit diese auch übernommen werden.
> ...



Ich versteh dein Problem nicht, dass nur eine Zeile eingelesen wird. tombe hat in seinem Code oben eindeutig eine Schleife drin, die alle Zeilen aus der CSV ausliest. Dass bei dir nur eine Zeile kommt, liegt also eher daran,  dass mit deiner CSV etwas nicht stimmt.

EDIT: Ich würde dir mal empfehlen, da per Einzelschritt durchzugehen. Ich tippe mal drauf, dass  du irgendwo ne Fehlerbehandlungsroutine hast, die dann nach der ersten Zeile dich aus der Schleife wirft.


----------

