Aanpassen extensies van csv naar xls met macro

[Marky76]

Beste,
Ik heb volgende code opgenomen waarbij ik de csv bestandjes wil opslaan als xls bestandjes.

Mijn probleem is dat als ik het xls bestand open doe alle gegevens zicht naast elkaar bevinden met enkel een ; ertussen zoals in bijlage.

Wat moet ik in mijn code aanpassen?

Waarschijnlijk dit stukje. Ik werk in exel 2003, welke xlExcel..... moet ik dan gebruiken??

FileFormat:=xlExcel9795

Sub ExtensiesAanpassen()
'
' ExtensiesAanpassen Macro
' De macro is opgenomen op 4/12/2007 door Mark.
'

'
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.csv"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.xls" _
        , FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_MIS.csv"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_MIS.xls" _
        , FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_PAT.csv"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_PAT.xls" _
        , FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_SCC.csv"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_SCC.xls" _
        , FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

Groeten,
Mark

 

[Ginger]

Marky76, Als ik zo je opgenomen macro bekijk, ben je 4x hetzelfde spelletje aan het spelen. Ik zou deze macro dus niet te vaak laten lopen als ik jou was.... (trouwens WEL goed dat je 't zelf zo aan het proberen bent :thumb
Waarom moet je trouwens deze conversieslag aan gaan? Een .csv laat zich toch prima openen in Excel?

Groet, Leo

 

[Marky76]

Neen het werkt niet met csv, dan krijg je zo'n resultaat zoals ik in bijlage heb gestuurd, want als ik de macro laat lopen die ik nu hieronder zal weergeven met de extensie xls, dan werkt hij perfect en daarom wou ik een macro die eerst de extensies kon omwisselen van csv naar xls.

De bedoeling van mijn marco is vier bestanden die ik elke dag moet sorteren en schikken en dan nog eens doorsturen en daarom wou ik een macro die de extensies aanpast en dan met onderstaand macro sorteert schikt en dan opslaagt onder een andere map, dan mijn bestand Klantenbalans Schikken terug wist en dan zou ik het zelfde doen voor de andere 3.

Sub KlantBalAanMaken()
'
' KlantBalAanMaken Macro
' De macro is opgenomen op 4/12/2007 door Mark.
'

'
    ChDir _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV"
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.xls"
    Cells.Select
    Selection.Copy
    Windows("Klantenbalans schikken.xls").Activate
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
    Windows("BalAgéeCli_StatPat_CYN.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Close
    Range("A1").Select
    
    Verplaatsen
    Sorteren
    
    Workbooks.Add
    Windows("Klantenbalans schikken.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("Map2").Activate
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    ChDir _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\GroepPatigny"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\GroepPatigny\Cynap.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
    Range("A1").Select
    
    Wissen
    Wissen_1
     
    
End Sub

Kan ik de extensie aanpassen opdat het naar xls gaat, want met xlExcel9795 heb ik dat slechte formaat zoals in bijlage van mijn eerder bericht.

Groeten,

Mark

 

[koosl]

Mark,

je hernoemt alleen je bestand. Dat is niet voldoende.
Je moet er nog een stap in tussenvoegen.

Selekteer de hele kolom A en ga naar Data - Tekst naar kolommen.
Klik gescheiden aan. (tekst is gescheiden door punt komma's.)
Volgende
Vink alleen punt komma aan.
Voltooien.
Wilt U niet lege cellen in het doelbereik overschrijven? Ok

Nu moet het er beter uitzien. Dit sla je dan op als .xls bestand

Koosl

 

[Marky76]

Koos,

Ik heb je opdracht uitgevoerd, maar nu heb ik enkel mijn bedragen in kolom K,
de bedragen in de kolommen erachter zijn ineens verdwenen.

Dus al ik het CSV bestand manueel gewoon open, dan is er geen probleem, maar nadat ik mijn macro heb laten lopen en dan met die puntkomma's zit en dan tekst naar kolommen doe zoals je hebt aangegeven dan heb ik geen bedragen meer achter kolom K.

Op het moment dat de macro er puntkomma's van maakt, vanaf dat moment zijn de bedragen achter de kolom K weg. Hoe kan dit of hoe kan ik dat vermijden?

Moet ik toch eerst zelf manueel de 4 bestanden 1 voor 1 openen en bewaren onder xls want dan gaat het wel. Maar dan vind ik het toch raar dat dit niet met een macro kan gefixt worden.

Ik zal het origineel CSV bestand in bijlage zetten. En dan moet je eens met een macro opnemen zoals ik en het bestand openen en opslaan als xls en dan zie je dat de bedragen achter kolom K verdwenen zijn.

Groeten,
Mark

 

[koosl]

Mark,

in de bijlage zit niet het .csv bestand, maar een bestand wat al is omgezet van .csv naar .xls
Maar volgens mij is het wel goed hoor!
Als je naar het bovenste .csv bestand (eerste post) kijkt zie je dat er achter sommige getallen of cijfers bv: 3 puntkomma's staan. Dit wil dus zeggen dat er dan 2 lege cellen achter komen te staan!

De getallen of tekst in jou .csv bestand zijn gescheiden door puntkomma's. (alles in 1 cel)

De functie "tekst naar kolommen" van Excel, moet dan naar het door jou opgegeven scheidingsteken zoeken (in jou geval puntkomma's) om deze getallen of tekst uit elkaar te halen en in aparte cellen te zetten.

Kijk maar eens in de bijlage van je eerste post. Dit is een .csv bestand.
En kijk dan eens naar de 3e regel naar het eerste getal 19763
Hier achter staan 4 puntkomma's. Dus dan komen er 3 lege cellen achter te staan.

Koos.

 

[Marky76]

Koos,

In bijlage de CSV die net met tekst naar kolommen heb aangepast.

De vakken die ik geel heb gemarkeerd, daar moeten normaal dus de bedragen komen en bij mij staat er niets in.

Groeten,
Mark

 

[Marky76]

In bijlage zie je als je de groote van de kolommen aanpast alvorens "tekst naar kolommen te doen dat net regel 3 de regel die je aanhaald verschilt van al de anderen.

 

[koosl]

Mark,


in je laatste bijlage kun je zien dat deze output is verdeelt over 3 kolommen ipv over 1 kolom!

Dus als je kolom A bewerkt met tekst naar kolommen dan worden de 2 anderre
kolommen daardoor overschreven door wat er in de eerste stond!

Is in je originele csv bestand dit dan ook zo verdeelt?
Dus staat niet alles in 1 kolom?

Koos.

 

[Marky76]

Dit had ik gisteren ook opgemerkt en dan nog eens getest, maar het is dus zo.
Ja, ik heb de code tot hier laten lopen en dan onderbroken

 ChDir _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV"
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.csv"
    Cells.Select
    Selection.Copy
    Windows("Klantenbalans schikken.xls").Activate
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
    Windows("BalAgéeCli_StatPat_CYN.csv").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Close
    Range("A1").Select

en voor sommige bestandjes heb ik soms 4 kolomen en dit gebeurt enkel door dit stukje code.
Dus de bedragen na de komma staan in een andere kolom.
Heb met ASAP utilities, join data gedaan en dan "tekst naar kolommen", dat lukt dan staan de bedragen in de juiste kolommen, maar dan zijn de komma's weg en heb ik uiteraard absurde bedragen.

Groeten,
Mark

 

[koosl]

Mark,

kun je hier je tekstbestand niet neerzetten?
Dus het tekstbestand nog voordat je er vanalles mee gedaan hebt.

Je kunt ook eerst dit nog proberen.
Open met Excel het tekstbestand
File - Open en zoek daar de map met je tekstbestand op. (tekstbestand heeft geen xls extensie dus deze zal je niet zien.
Dan zet je onder in het Open venster de Files of type: op text Files.
Dan klik je het tekstbestanje aan en klikt op Open.
Dan verschijnt de Text Import Wizzard.

Vink Delimited aan.
Next
Zet alleen een vinkje bij Semicolon.
Finish.

Als het goed is zou nu ook alles naar wens moeten zijn.
Nu alleen je bestand nog opslaan.
Save as:
en kies bij Save as type: voor Microsoft Excel Workbook

Lukt dit allemaal?

Koosl

 

[Marky76]

In bijlage het bestand voordat ik er iets aan heb gedaan.
Je moet dan eens mijn stukje code daarop loslaten, dan zal je de rare structuur waarnemen.
Ga straks je advies proberen na het werk.

Groeten,

 

[Marky76]

De Text Import Wizard verschijnt bij mij niet.
Heb dus het bestand dat ik hier net heb gezet via excel als tekstbestand geopend, maar bij mij komt er geen Wizard tevoorschijn.

 

[koosl]

Mark,

Ik heb het csv bestandje in kladblok zo ingedeeld zoals het er in Excel uit zou moeten zien.

En ik heb het csv bestand gedubbelklikt zodat het in Excel werd ingelezen.
Dit csv bestand wordt door Excel herkend en automatisch omgezet in kolommen.

Open het csv bestand maar eens met kladblok en kijk vergelijk dan de verdeling maar eens met Excel.
Deze zijn precies hetzelfde.

Als het Excel bestand niet goed is ligt dat aan je csv bestand.

Ik heb het nu nog niet over VBA. Eerst boven water krijgen wat er nu niet goed is.

Koos.

 

[Marky76]

Als ik de csv open doe en deze dan, daarna onmiddelijk bewaar met xls dan heb ik de correcte indeling. Het enige wat ik wil doen is die handeling via VBA laten. Het is als het met VBA gaat dat de indeling niet meer klopt, want als je in mijn code gaat kijken zie je dat VBA als fileformat xlExcel9795 gebruikt. En volgens mij komt het daarvan voort, want ik zit in excel 2003.
Als ik manueel de csv open doe in excel en bewaar als een xls bestand en als ik dan mijn marcro laat lopen werkt alles. Het is alleen als ik via VBA het csv bestand wil bewaren als een xls bestand dat de indeling niet meer klopt. Dus manuele handeling wou ik vervangen met vba, dus ik neem mijn manuele handeling gewoon simpelweg op en dan klopt alles. Maar als ik dan de opgenomen macro laat runnen dan is de indeling niet meer correct. Dus nu moet ik elke keer het csv bestand stomweg openen en bewaren als xls. En dan de macro (die ik hier dankzij helpmij heb bekomen) laten runnen.

Dus het ligt aan de FileFormat die in VBA word gebruikt.

Groeten,

 

[koosl]

Mark,

FileFormat:=xlNormal werkt bij mij gewoon.
Dan is de csv file gesaved als Microsoft Excel Workbook zoals ik eerder al aangaf.

Sub Marky()
    ChDir "H:\H--Documents\1 Temp (Lotus Notes Mail)\mark"
    ActiveWorkbook.SaveAs Filename:= _
        "H:\H--Documents\1 Temp (Lotus Notes Mail)\mark\BalAg‚eCli_StatPat_CYN.xls", _
        [B]FileFormat:=xlNormal[/B], Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Koos.

 

[Marky76]

De xlNormal heb ik er gezet. Als je dus gewoon opneemt en de csv opent en opslaat onder xls. dan staat er xlExcel9597, en ik heb er zowat alle andere mogelijkheden achter gezet maar zonder resultaat.

Dus de conclusie is dat je een csv bestand met vba niet kan omzetten naar een xls, met behoud van een correcte indeling.
Dus zal het manueel moeten blijven doen en dan pas de macro laten runnen.

Maar ik kan niet snappen dat als ik de csv open doe en hem gewoon save als xls dat dit wel lukt en als je die handeling opneemt, dat hij dan dat formaat met de komma punten geeft, dus volgens mijn ligt het aan de fileformat.

 

[Marky76]

Ik heb het nog eens getest met een collega. We hebben het openen van de csv apart opgenomen en dan apart het bewaren van die file als xls.
Als je dit dan samenvoegt en je laat de macro runnen dan hebben we terug dat vreemd formaat.

Workbooks.Open Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.csv"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.xls" _
        , FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

 

[Marky76]

Ik heb het gevonden, je moet eerst Data doen en dan Externe gegevens importeren, dan het csv bestand selecteren, de bewerkingen van de macro erop loslaten en nu werkt het.

Sub Sorteren()

' CSVImporteren Macro
' De macro is opgenomen op 7/12/2007 door Mark.
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\CSV\BalAgéeCli_StatPat_CYN.csv" _
        , Destination:=Range("A1"))
        .Name = "BalAgéeCli_StatPat_CYN"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    Dim x As Long, n As Long, Var1 As Variant, Var2 As Variant, myRange As Range
    Dim rwIndex As Long, colIndex As Long
    'Sheets("sorteren").Select
    Sheets(1).Select
    'If Sheets("sorteren").Range("Q1") = "" Then MsgBox "Je hebt al gesorteerd.": Exit Sub
    If Sheets(1).Range("Q1") = "" Then MsgBox "Je hebt al gesorteerd.": Exit Sub
    x = Cells(Rows.Count, "A").End(xlUp).Row
    'sorteren
    'Sheets("sorteren").Range("A1:Q" & x).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
     '                      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    'kolommen optellen
    
    Sheets(1).Range("A1:Q" & x).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
                           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Sheets(1).Range("A1:P1").Font.Bold = True
    
    For n = 12 To 16
        Var1 = Cells(2, n).Address(ColumnAbsolute:=False)
        Var2 = Cells(x, n).Address(ColumnAbsolute:=False)
        Set myRange = Range(Var1, Var2)
        Cells(x + 1, n).Formula = Application.WorksheetFunction.Sum(myRange)
        Cells(x + 1, n).Font.Bold = True
    Next n
  
        For rwIndex = 2 To x + 1
        For colIndex = 12 To 16
            'With Worksheets("sorteren").Cells(rwIndex, colIndex)
            With Worksheets(1).Cells(rwIndex, colIndex)
            .NumberFormat = "#,##0.00"
            End With
        Next colIndex
        Next rwIndex
    
    Columns("D:E").Delete
    Set myRange = Nothing
    Columns("A:P").EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    Range("A2").Select
    
     ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Mark\Mijn documenten\Allerlei\Vervaldagen PAT SCC CYN MISS\GroepPatigny\Cynap.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

Groeten,
Mark

 

[koosl]

Mark,

Data - Externe gegevens importeren - Gegevens Importeren
Dan krijg je dus de "Wizzard Tekst Importeren" waar ik het in post 11 over had!

Blij voor je dat het is opgelost.


Koos.