Opgelost tekst wijzigt niet in getallen met macro

[gGerretje]

Beste helpers,

Mijn zonnepanelen-app levert elke dag een csv-bestand.
Die gegevens wil ik kopiëren naar het bestand waarin ik ook de gegevens van de andere dagen heb staan.

Omdat ik dat elke dag doe gebruik ik een macro:
Daarmee zet ik de tekst om naar kolommen (dat werkt).
Vervolgens vervang ik de decimale punten door decimale komma's (ook dat lukt).

Maar als ik nu gewoon de tekst kopieer en plak in het andere bestand (waar alle gegevens staan) dan blijken die gegevens ineens tekst te zijn.
Ik probeer ze dus om te zetten naar getallen (Speciaal Plakken, vermenigvuldigen) maar dat lukt niet. Het blijft tekst.

Kan iemand mij helpen bij die laatste stap?

Alvast dan voor jullie hulp.
Ger


Dit is mijn vba code (staat in mijn persnlk.xls)

Sub ZonnePanelenCSV()
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True
    Range("A2:G97").Select
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-75
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
End Sub

Ik hoop dat het gelukt is om de code als code weer te geven.
Ik ben een tijdje niet op dit forum geweest (het is er niet handiger op geworden).
Ik heb de code geselecteerd en geklikt op BBCode aan/uit

 

[edmoor]

Wijzig in het .csv bestand m.b.v. Kladblok alle komma's in puntkomma's.
Dat kan met 1 zoek en vervang opdracht.
Sla het bestand op en dubbebklik er dan op om deze te openen in Excel.

 

[peter59]

Of d.m.v. power query.

 

[gGerretje]

Hallo Edmoor,

Dat het zo kan begrijp ik. Maar dat betekent dat ik die handeling handmatig moet uitvoeren om daarna alsnog dat csv-bestand te moeten openen. Dan kan ik het net zo goed meteen in Excel met de hand aanpassen.

Overigens converteert mijn macro de tekst al naar kolommen en wijzigt al alle punten in komma's.
Maar het vreemde is juist dat het dan tekst blijft. Ik kan de waarden niet optellen.

Als ik exact hetzelfde doe als de macro (maar dan handmatig) worden het wel getallen en kan ik ze wel optellen.

@peter59: Ik weet niet hoe je dit precies gedaan hebt. Ik ben ook niet bekend met Power Query, maar dit is dus het resultaat dat ik moet hebben.
Alleen moet ik dan toch weer handmatig (of met een macro) het bereik selecteren en kopiëren naar mijn verzamelbestand.

Vraag is dus: hoe krijg ik dat resultaat van mijn macro alsnog geconverteerd naar getallen?

Wel bedankt voor jullie reacties.

Kan iemand me ook nog vertellen hoe ik mijn code hier tussen codehaken zet? Dan kan ik het volgende keer netjes doen.

Groeten,
Ger

 

[jkpieterse]

Als je nou die CSV bestanden gewoon in de map laat staan, dan kan je met PQ al die bestanden in 1 keer laten inlezen in je doelbestand. Geen macro meer nodig. Gegevens, Gegevens ophalen, uit bestand, uit map. Dan de bestanden filteren op de extensie .csv en die laten "uitpakken". Wel eerst de query instellingen op Nederland zetten. ALs alles goed is ingesteld hoef je voortaan alleen maar op alles vernieuwen te klikken en klaar.

 

[peter59]

Hallo,

Dit is zo'n beetje wat JKPieterse tracht uit te leggen.
In het begin een klein beetje werk (hobby) maar hierna is het dus eigenlijk klikken op alles verversen en klaar is Ger.

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

 

[gGerretje]

Hallo Jan Karel,

Dank je wel voor je reactie.
We komen al mooi in de buurt van wat ik wilde.

Ik wist niet wat je bedoelde met die Power Query waar Peter het ook al over had.
Maar je extra toevoeging was voldoende (denk ik)(al bedoelde jij het waarschijnlijk net iets anders, ik heb nog excel 2010).
Die gegevens ophalen is gelukt met Gegevens - Van andere bronnen - Van XML gegevens-import - map kiezen - allebestanden kiezen - csv-bestand selecteren en dan toch dezelfde actie als bij tekst naar kolommen. Ook die decimale komma erin krijgen is gelukt (ik moet dan bij Geavanceerd voor decimale punt kiezen (en dan krijg ik mijn komma ). Dat begrijp ik.

Ik heb er een macro van gemaakt en die werkt (zie beneden)
Ik houd alleen nog 2 vraagjes over:

1:
In mijn macro staat destination nu op de cel waar ik vandaag moet vullen.
Maar ik moet natuurlijk elke dag een aantal rijen lager invoegen. Ik wil dus de betreffende cel selecteren en als destination die geselecteerde cel kiezen.
Ik dacht al slim te zijn met zoiets als
Dim Bestemming As Range
bestemming = ActiveSheet.Selection
en dan verderop Destination:=bestemming
maar dat werkt niet. Heeft iemand dar een oplossing voor?

2:
Een opmaakprobleem. Na import zijn de kolommen plotseling heel smal geworden (conform de breedte van de ingevoerde gegevens)
Hoe behoud ik de breedte van de kolommen?

Groeten,
Ger

De macro:

Sub ZonnePanelenImport()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\gGerretje\Downloads\Dec 20.csv", Destination:=Range("$D$10"))
        .Name = "stackedChart_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileDecimalSeparator = "."
        .TextFileThousandsSeparator = ","
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[gGerretje]

Hallo Peter,

Ik zag jouw reactie nu pas (was bezig met mijn reactie zo goed mogelijk te formuleren).
Dank je wel voor je toevoeging.
Ik was er al achter gekomen met de korte uitleg van Jan Karel en wat uitproberen.

Ik hoop dat je nog even wil meedenken over mijn laatste vragen?

Groeten,
Ger

 

[Haije]

Wijzig
.AdjustColumnWidth = True eens in
.AdjustColumnWidth = False

 

[JEC.]

De bedoeling is om telkens alle bestanden op te halen met Power Query. Je probeert ze nu nog steeds één voor één te importeren met ieder een eigen tabel.

Als je een folder kiest met daarin al je CSV bestanden, kan je alles gemakkelijk samenvoegen tot 1 tabel. Zo hoef je in het vervolg de query alleen maar te verversen zodra er een nieuw CSV bestand bij is gekomen.

 

[JEC.]

1. Zorg dat al je CSV bestanden in 1 folder staan zonder andere bestanden.
2. Open Power Query met een blank query (zie afbeelding)
3. Ga in de Power Query editor naar Advanced Editor.
4. Plak onderstaande code met daarin jouw correcte folder pad (zie source regel)

let
    Source = Folder.Files("C:\Users\xxx\Documenten\xxx"),
    fltr = Table.SelectRows(Source, each ([Extension] = ".csv")),
    fltr_hidden_files = Table.SelectRows(fltr, each [Attributes]?[Hidden]? <> true),
    get_content = Table.Combine(List.Transform(fltr_hidden_files[Content], each Table.PromoteHeaders(Csv.Document(_)))),
    colNames = Table.ColumnNames(get_content),
    rep = Table.ReplaceValue(get_content,".",",",Replacer.ReplaceText,colNames),
    catType = Table.TransformColumnTypes(rep,{{"category", type time}}),
    cTypes = Table.TransformColumnTypes(catType, List.Zip({List.Skip(colNames,1),List.Repeat({type number}, List.Count(colNames)-1)}))
in
    cTypes

 

[gGerretje]

Hallo Haije,

Dank je wel.
Daarmee is de tweede vraag beantwoord.


@JEC. Dank je wel voor je aanvulling.
Ik lijk nu misschien heel ondankbaar maar in principe download ik elke dag het laatste csv-bestand (dus met de gegevens van de vorige dag).
Meer komt er niet in de downloadmap te staan.
Bovendien loop ik met jouw oplossing meteen al tegen een aantal andere problemen aan.
Om te beginnen heb ik een andere menu-indeling vanwege mijn Excel 2010. Die Blank Query heb ik waarschijnlijk niet eens.
Verder komt mijn csv-bestand binnen met de naam stackedChart.csv
Als ik ze steeds erbij zet in die map, moet ik ze dus gaan hernoemen en dan moet ik dan ook nog gaan uitzoeken hoe ik de inhoud van elk csv-bestand op de juiste plek in mijn bestand krijg.

Dit gaat dan ook niet de oplossing worden.
Maar ik waardeer wel je bereidheid om te helpen.

Rest nog vraag 1 hoe ik kan importeren naar de geselecteerde cel.
En als iemand me kan vertellen hoe ik op dit forum de ingevoegde code correct kan weergeven ... graag.

Groeten,
Ger

​

 

[JEC.]

Ja precies, je downloadt iedere dag een nieuw bestand. Als je deze gewoon allemaal apart opslaat in een folder heb je ook niets te hernoemen. In de query verwijs je naar de bovenliggende folder en niet naar het bestand.

In Excel 2010 kun je Power Query als add-in downloaden.

 

[gGerretje]

Hallo JEC

Dat laatste ga ik zeker eens bekijken. En dan probeer ik die code toch eens uit. Gewoon om er iets van te leren.
Nogmaals bedankt voor je reacties.

Groeten,
Ger

 

[gGerretje]

Ik heb het gevonden.
Gegevens invoeren vanaf de geselecteerde cel.
Met ActiveCell. Daar had ik eerder op kunnen komen.

Dank aan alle helpers.
Alvast een Fijne jaarwisseling en de beste wensen voor 2024

Groeten,
Ger

 

[jkpieterse]

Jouw code voegt telkens een nieuwe query toe aan je bestand. Eigenlijk hoef je na de eerste keer dat je die macro opnam alleen nog maar alles vernieuwen te doen en dan die kopieeropdracht.

 

[gGerretje]

Dat laatste begrijp ik niet Jan Karel.
Maar dat komt omdat ik nooit op deze manier gewerkt heb met het importeren van csv-bestanden.

Ik importeer de gegevens (van bijvoorbeeld 27 december) vanaf cel D20 (ik zeg maar wat).
Het csv bestand kan nu verwijderd worden. Toch? Mijn gegevens blijven bewaard.
De dag erna importeer ik die van 28 december vanaf cel D120 (want het 96 rijen en ik houd wat ruimte ertussen)
Dan kan dat csv-bestand ook weg.
Dan 29 december importeren naar D220, etc.

En wat had ik nu anders kunnen doen volgens jou?

Groeten,
Ger

 

[jkpieterse]

Het probleem is dat jouw code telkens als je die uitvoert een nieuwe gegevensverbinding toevoegt aan je bestand. Na twee jaar zijn dat er dus al meer dan 700.

Wat je eigenlijk moet doen zijn deze stappen:
- maak op een apart tabblad die verbinding
- je macro doet vanaf nu slechts deze twee dingen:
1. De verbinding vernieuwen
2. De gegevens kopieren naar het tabblad waar alle oudere gegevens staan

 

[gGerretje]

Hallo Jan Karel,

Aha, ik was in de veronderstelling dat ik alleen gegevens geïmporteerd had, maar het was een link. Ik vond het al raar dat die gegevensbereiken steeds een nieuwe naam kregen.

Wat ik dus nu moet doen (corrigeer me als ik het verkeerd zeg):

Ik importeer het csv-bestand naar A1 op een leeg blad (noem het "Invoer") in mijn Gegevensbestand.
Vervolgens kopieer ik (met een macro) de range A1:G96 en Plak Speciaal - Waarden in de ActiveCell van het blad waar de gegevens moeten komen.
Morgen komt er in mijn downloadmap een nieuw csv-bestand met dezelfde naam.
Dan vernieuw ik de gegevens op het blad "Invoer"en met de macro kopieer ik de nieuwe gegevens naar de ActiveCell van morgen.
En voor overmorgen kan ik het dan ook wel bedenken.

Heb ik nu alles correct?
Dan heb je me prima geholpen.

(zo niet dan hoop ik dat we nog even contact houden )

Groeten,
Ger

 

[jkpieterse]

Dat is inderdaad het "plan". Je zou het nog kunnen uitbreiden met een check of je de gegevens van vandaag al gekopieerd had, door de datum in de import te vergelijken met de nieuwste datum in je lijst met meerdere dagen aan gegevens.