Diverse gegevens exporteren naar ander bestand VBA

Status
Niet open voor verdere reacties.
R

royb73

Gebruiker
Lid geworden
19 sep 2012
Berichten
228
Beste mensen,

Ik gebruik standaard een template voor verschillende rapporten.
Van de rapporten die ik maak, wil ik een aantal gegevens "exporteren" naar een standaard "overzichten" bestand (D:\test\Archieflijst\Overzichten.xlsx). Dit bestand is standaard niet open, maar tijdens exporteren zou deze geopend moeten worden.

In Overzichten.xlsx staan de sheets op jaartallen.
In het rapportbestand (Mc14027a.xlsm) moet eerst gekeken worden uit welk jaar dit rapport is (Cel C26) en vervolgens de gegevens wegschrijven naar de desbetreffende sheet (jaar).

Vervolgens moeten de volgende cellen geëxporteerd worden naar de desbetreffende kolom: C3, C4, C12, C14, C15, C24, C25, C26, C27, H4 en H5. Bij de volgende zou dit op een nieuwe regel moeten.

Wie o wie kan mij helpen?

Bekijk bijlage Overzichten.xlsx
Bekijk bijlage Mc14027a.xlsm

Mvg

Roy
 
Beste MDN111,

Dit werkt perfect!

Hoe kan ik ervoor zorgen dat de McNummers (rapportnummers) in kolom A van overzicht.xlsx gesorteerd wordt op alfabetisch volgorde nadat deze geëxporteerd zijn? Deze moet hij doen voordat het bestand wordt afgesloten.


Met vriendelijke groet,

Roy.
 
Dag Roy !

Deze zou bijkomend de bestemmingssheet moeten sorteren.

Grtz,
MDN111.
 

Bijlagen

Beste MDN111,

Ik krijg steeds een foutmelding bij het sorteren bij "With oSh.Range(Cells(5, 1), Cells(r, num_cols))" (Fout 1004 tijdens uitvoereing: Methode Range van object_Worksheet is mislukt).

Groeten,
Roy.
 
Het probleem is dat het bij mij wel werkt. Ik slaag er niet in om een fout te laten plaatsvinden.

Het heeft blijkbaar iets te maken met het bestemmingsbestand "Overzichten.xlsx". Is dat nog steeds hetzelfde als de bijlage bij #1 ?

Wil je verder eens het Immediate Window openen in de debugger, op het moment dat de foutboodschap verschenen is en de waarde opvragen van enkele variabelen? Zie bijlage.

? oSh.Name
? oSh.Range(Cells(5, 1), Cells(r, num_cols)).cells.count
? r
? num_cols

en een screenshot nemen van de antwoorden, a.u.b.?

Grtz,
MDN111.
 

Bijlagen

  • Debugger.png
    Debugger.png
    78,5 KB · Weergaven: 72
Beste MDN111,

? oSh.Name
2014

? oSh.Range(Cells(5, 1), Cells(r, num_cols)).cells.count

? r
5

? num_cols
16

Dit zijn de waardes wat ik gekregen heb.

Bij ? oSh.Range(Cells(5, 1), Cells(r, num_cols)).cells.count krijg ik de fout 1004 melding. Door de toepassing of door object gedefinieerde fout.

Mvg

Roy.
 
Dag Roy,

Ik vermoed dat het ligt aan de specificatie van de Sort Key.
Blijkbaar werkt het niet goed als we de Key specifiëren beginnend met een "." binnen een With-End With-blok.
Specifiëren we de key met de volledige "route" vanaf het Worksheet-object, dan lijkt het wel te werken.
Voor een verklaring is mijn kennis te beperkt.

Wil je de regels
Code: 
With oSh.Range(Cells(5, 1), Cells(r, num_cols))
    .Sort Key1:=.Range("A3"), Header:=xlNo
End With
vervangen door deze
Code: 
With oSh.Range(Cells(5, 1), Cells(r, num_cols))
    .Sort [COLOR="#FF0000"]Key1:=oSh.Range(Cells(5, 1), Cells(r, num_cols)).Columns(1)[/COLOR], Header:=xlNo
End With

Grtz,
MDN111.
 
Beste MDN111,

Indien ik de datum aanpast in cel C26 of het rappoprtnummer in cel C25, dan krijg ik steeds een melding: "Een door de gebruiker gedefinieerd gegevenstype is niet gedefinieerd"....

Ik heb de code aangepast, echter stopt hij nog steeds bij "With oSh.Range(Cells(5, 1), Cells(r, num_cols))". Wat wel opvalt, is indien ik een ander jaartal invul, dan krijg ik dit probleem. Indien ik 10 keer dezelfde jaartal gebruik, dan lijkt het wel te werken.

Mvg

Roy
 
Dag royb73,

Ik heb nog wat opzoekwerk verricht en blijkbaar was het refereren naar de range met de .cells eigenschap toch niet echt OK.
Met de hiernavolgende code heb ik redelijk wat verschillende rapportgegevens toegevoegd aan het overzicht, en geen enkele foutmelding gekregen.

Wil je de regels
Code: 
With oSh.Range(Cells(5, 1), Cells(r, num_cols))
    .Sort Key1:=oSh.Range(Cells(5, 1), Cells(r, num_cols)).Columns(1), Header:=xlNo
End With
vervangen door deze, a.u.b.
Code: 
With oSh
    Set myRange = .Range(.Cells(5, 1), .Cells(r, num_cols))
End With
With myRange
.Sort Key1:=.Columns(1), Header:=xlNo
End With

En ook bovenaan de sub bijvoegen:
Code: 
Dim myRange As Range

Hopelijk werkt het bij jou nu ook zonder problemen. Fingers crossed.

Grtz,
MDN111.
 
Beste MDN111,

Dit werkt prima nu!

Is het ook mogelijk, indien de code het rapportnummer ziet (Mc nummer), dan die dan vraagt of de waardes overgeschreven moeten worden of doorgaan zonder over te schrijven? nu krijg ik alleen een melding dat het rapport nummer bestaat en kan dan alleen op OK drukken zonder een actie uit te voeren.

Mvg

Roy.
 
Dag royb73,

Omdat de wijziging iets te omvangrijk was, zie nieuwe bijlage.

Grtz,
MDN111.
 

Bijlagen

Beste MDN111,

Dit werkt perfect!

Ik heb nog 2 laatste dingen.
1. Ik heb een tabel aangemaakt met in rij 3 kopteksten. De waarden worden vanaf rij 5 toegevoegd. Indien de code sorteert op volgorde, dan sorteert hij ook de kopteksten van de tabel ook mee. Hoe kan dit voorkomen worden? Het sorteren moet v.a. rij 5 gebeuren.

2. Ik zou graag alles in tekst Arial en 10 (tekstgrootte) willen hebben. Ook moet alles links uitgelijnd worden per cel.

Met vriendelijke groet,

Roy.
 
Dag Roy !

  2. Als je zegt "Ik heb een tabel aangemaakt...", dan vermoed ik dat je het hebt over het bestand "Overzichten.xlsx". De macro in bestand als bijlage aan #12 blijkt dat toch wel goed te sorteren. Vanaf de 5de rij, zonder kopteksten. Bij mij werkt dat naar behoren. In het bestand "Overzichten.xlsx" van de nieuwe bijlage zijn diverse rapporten weergegeven die gecreëerd zijn in een willekeurige volgorde vanuit het bestand "Mc14027a.xls".
  3. De nieuwe bijlage houdt nu ook rekening met het formatteren van de tekst (Arial, 10, links uitgelijnd).


Grtz,
MDN111
 

Bijlagen

Beste MDN111,

Hartstikke bedankt voor jouw hulp.

De codes werken nu prima. Ik stuur je nog mijn overzicht.xlsx zodat je kunt zien wat ik bedoel. Zie jaartal 2011 bijvoorbeeld. Daar is regel 3 en 4 verwisseld tijdens het sorteren.

Bekijk bijlage Overzichten.xlsx
 
Dag Roy !

Ja, nu begrijp ik het. Het bestand "Overzichten.xlsx" bij #1 is niet hetzelfde als bij #15.

Je hebt ondertussen dus die fameuze tabellen in het leven geroepen. Twee van de zes tabbladen bevatten een tabel, de andere tabbladen niet. Omdat ik geen ervaring heb met tabellen en het sorteren ervan, ben ik wat informatie gaan opzoeken. Zo ben ik er achter gekomen dat de VBA-code om een Range te sorteren anders is dan om een Tabel te sorteren.

Vooraleer verder te gaan had ik graag vernomen of je in "Overzichten.xlsx" in alle tabbladen een Tabel gaat gebruiken of met "gewone" sheets zonder Tabellen gaat verder werken. Zo kan ik de code voor het sorteren aanpassen naargelang de keuze.

Grtz,
MDN111.
 
Beste MDN111,

Het is uiteindelijk wel de bedoeling dat ik die tabellen ga gebruiken in alle tabladen
Mvg.
Roy
 
De code voor het sorteren van een Table lijkt zowat te werken, maar er is nog een vraag te beantwoorden:

De oorspronkelijke opzet was dat we gegevens toevoegen vanaf de 5de rij, de kolom headers van de Table staan op de 3de rij terwijl de 4de rij leeg blijft. Door het sorteren van de Table verdwijnt echter die 4de lege rij en komen de gegevens vanaf de 4de rij te staan. Dat is normaal.

Er zijn nu twee mogelijkheden:

  • De lege 4de rij vergeten en de gegevens toevoegen vanaf de 4de rij i.p.v. vanaf de 5de rij (in principe de meest logische Excel-oplossing).
  • Manueel een rapportnummer invullen met als waarde een spatie. In dat geval aanziet Excel de cel niet als leeg en wordt de betreffende rij bovenaan gesorteerd (dit lijkt mij een niet elegante oplossing).


Aan jou de keuze...

Grtz,
MDN111.
 
Beste MDN111,

Ik wil het ook niet te ingewikkeld maken. Mij lijkt het beste om vanaf rij 4 toe te voegen.

Mvg

Roy.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan