Hulp met macro

Status
Niet open voor verdere reacties.
R

rmk75

Gebruiker
Lid geworden
30 mrt 2013
Berichten
374
Beste forumleden,

Wie kan mij helpen met het volgende…
Op m’n werk moeten collega’s meerdere keren per dag een excel overzicht genereren vanaf een website. In dit overzicht staan containers welke arrived, expected of preannounced zijn.
Dit overzicht omvat iets van 48 kolommen en soms een paar honderd regels met diverse informatie. Van deze 48 kolommen hebben zij er maar 7 daadwerkelijk nodig.
Nu kan ik met een macro de onnodige rijen en kolommen verwijderen, eventuele dubbele containers eruit halen, barcodesverwijderen en een filter op de eerste regel zetten en deze regel gelijk vastzetten, maar graag zou ik nog iets erbij willen.
In de kolom ‘ Carrier Name In’ staat normaal gesproken een scheepsnaam. Echter gebeurt het dagelijks dat er, of een streepje staat of de cel is leeg.
Nu moeten alle regels waar géén scheepsnaam (Carrier Name In) vermeld staan verwijderd worden. Echter zijn deze cellen soms leeg en soms staat er een streepje… Maar in beide gevallen moet de regel worden verwijderd.
Dat laatste stukje krijg ik op de een of andere manier niet voor elkaar.

Ik zal een willekeurig overzicht zoals deze wordt gemaakt bijvoegen.

Van dit overzicht moeten altijd de eerste 4 regels verwijderd worden. Daarna moeten alle kolommen verwijderd worden behalve B-C-E-F-G-AE-AN. De objecten (barcodes) in kolom AW kunnen ook verwijdert worden.
Daarna de dubbele containernummers verwijderen, een filter op de eerste regel en tevens deze regel vastzetten.
Wie o wie wil me hiermee helpen..

alvast bedankt!

Bekijk bijlage Test scanlijst.xlsx
 
Laatst bewerkt:
Fluitje van een cent met Power Query (PQ). Als de website geschikt is kun je ook direct de gegevens inladen in PQ zonder eerst een Excelbestand te maken. Dan is het nog maar zo simpel als op "verversen" drukken en je krijgt je gewenste tabel rechtstreeks getransformeerd vanaf de website.
 

Bijlagen

  • Test scanlijst (AC).xlsx
    127,7 KB · Weergaven: 38
Laatst bewerkt:
Als je deze macro runt, wordt alles vervangen door hetgeen wat je wilt. Het bevriezen van rij 1 zit hier nog niet in.
De power query methode is zeker beter als het direct van de pagina te halen is!

Code: 
Sub jec()
 ar = Sheets(1).Cells(5, 1).CurrentRegion
 Set dict = CreateObject("scripting.dictionary")
 
 For i = 1 To UBound(ar)
    If ar(i, 31) <> "" And ar(i, 31) <> "-" Then dict(ar(i, 2)) = Array(ar(i, 2), ar(i, 3), ar(i, 5), ar(i, 6), ar(i, 31), ar(i, 40))
 Next
   
 With Sheets(1)
   .UsedRange.Clear
   .Cells(1, 1).Resize(dict.Count, 6) = Application.Index(dict.items, 0, 0)
   .Cells(1).CurrentRegion.AutoFilter
 End With
End Sub
 
Laatst bewerkt:
Hoe weet ik of een pagina geschikt is??
Wat is/doet een power query? Nog nooit van gehoord.. :confused:
 
Probeer eens via menu gegevens > gegevens ophalen > van het web > URL invullen > Ok > Verbinden.
Kun je nu de juiste tabel selecteren? Zo ja druk dan is de website "geschikt". Je kunt nu de gegeven rechtstreeks inladen en transformeren in Power Query.

Power Query is een onderdeel van Excel voor gegevensbewerking. Je kan Power Query ook starten via menu gegevens > gegevens ophalen > Power Query-editor starten. De stappen die je moet volgen om van de bron (website of excel) naar het gewenste eindresultaat te komen staan in het voorbeeld in #2. Die zie je rechts in beeld in de PQ-editor. Probeer maar eens elke stap aan te klikken, dan zie je wat er gebeurt.
 
Laatst bewerkt:
Ah, ok! Top, ik ga even kijken.

Bedankt voor de info!!
 
Super geschikt voor wat je wilt dus zeker de moeite waarde om je wat erin te verdiepen. Kan veel werk schelen.
Je benodigde transformaties is ook niet de meest moeilijke dus zou moeten lukken hoop ik... anders kom je maar terug hier.

Succes.
 
Laatst bewerkt:
Macro ook getest?
Mocht power query geen directe verbinding kunnen leggen, kan de data misschien met een macro opgehaald worden. Doe je dat nu handmatig?
 
Laatst bewerkt:
Ja, die macro heb ik ook getest. Moet alleen nog even kolom aan toegevoegd worden..

De site die men gebruikt daar moet men op inloggen (na 10 minuten wordt men automatisch uitgelogd), dan eerst wat opties aanvinken en daarna op 'search' klikken.
Dan verschijnt er een overzicht welke naar Excel geexport kan worden.
Kan dit ook allemaal via een macro???
 
Zo goed kunnen, heb dan wel een email en ww nodig. Met power query gaat dit niet.
 
Ik heb gisteren geprobeerd via power query de gegevens op te halen maar dat lukte helaas niet.
Ik kan hier geen inlog en ww delen want het betreft een site waarin onze containers en douane 'zaken' staan..
 
Jammer, maar dan kan het met PQ nog steeds via de gedownloade excel-file. Of met een macro.
 
Laatst bewerkt:
Bedankt AlexCEL, ik ga me eens verdiepen in de power query optie, maar voor nu ga ik voor een macro.
 
Beste JVeer, zou je me wellicht nogmaals willen helpen met de door jou gemaakte macro?

In eerste instantie moesten alle regels waarin geen scheepsnaam (Carrier Name In) stond verwijderd worden maar ik begreep van m'n collega dat, indien de ' container Status' arrived is dat dan betreffende regels wel behouden moeten blijven.

Alvast bedankt voor de moeite.
 
Zo dan?

Code: 
Sub jec()
 ar = Sheets(1).Cells(5, 1).CurrentRegion
 Set dict = CreateObject("scripting.dictionary")
 
 For i = 1 To UBound(ar)
    If ar(i, 31) <> "" And ar(i, 31) <> "-" Or ar(i, 7) = "Arrived" Then dict(ar(i, 2)) = Array(ar(i, 2), ar(i, 3), ar(i, 5), ar(i, 6), ar(i, 7), ar(i, 31), ar(i, 40))
 Next
   
 With Sheets(1)
   .UsedRange.Clear
   .Cells(1, 1).Resize(dict.Count, 7) = Application.Index(dict.items, 0, 0)
   .Cells(1).CurrentRegion.AutoFilter
 End With
End Sub
 
@JVeer, helemaal geweldig!
Hier kan ik mee verder.

Zou je me misschien eens kunnen uitleggen hoe ik zo'n macro moet 'lezen'.
Zou dat ook graag willen leren.


Grt Maurice
 
Het is een kwestie van veel doen en veel lezen.
Hier op het forum valt bijvoorbeeld genoeg te leren. Alleen al door topics te volgen en soms zelf te proberen ga je het vanzelf al beter snappen.

Bij de basis beginnen en steeds een stapje verder. Er zijn ook websites met gratis basiscursussen voor VBA.

De macro die ik je heb gegeven zou ik vooralsnog niet geheel proberen te begrijpen. Let wel op, het is maatwerk dus als je kolommen of bereiken gaat aanpassen kan de macro verkeerd lopen.
 
Laatst bewerkt:
Code: 
Sub jec()
     ar = Sheets(1).Cells(5, 1).CurrentRegion                   'lees het bereik rond A5 van het eerste werkblad naar een array (=je geheugen)
     Set dict = CreateObject("scripting.dictionary")            'maak een dictionary aan (een veredeld notitieboekje met een indexsleutel)

     For i = 1 To UBound(ar)                                    'loop alle rijen in je array af
          If ar(i, 31) <> "" And ar(i, 31) <> "-" Or ar(i, 7) = "Arrived" Then dict(ar(i, 2)) = Array(ar(i, 2), ar(i, 3), ar(i, 5), ar(i, 6), ar(i, 7), ar(i, 31), ar(i, 40))     'goed kijken naar het 2e cijfer binnen de haakjes dat is het kolomnummer uit je werkblad
          '31=carrier name in is niet leeg of "-" en 7=contianerstatus niet "arrived", hou dan op je notitieboekje een aantal (7) gegevens bij met als sleutel het 2=containernummer
          'wat hou je bij, 2=containernummer, 3=iso-code,5container load status, 6=containervisit type, ...
     Next

     With Sheets(1)                                             'in je eerste werkblad
          .UsedRange.Clear                                      'maak die helemaal leeg met opmaak en alles
          .Cells(1, 1).Resize(dict.Count, 7) = Application.Index(dict.items, 0, 0)     ''schrijf de volledige inhoud van je notitieboekje (=zoveel rijen x 7 kolommen) neer vanaf A1
          .Cells(1).CurrentRegion.AutoFilter                    'activeer de autofilter
     End With
End Sub
 
Bedankt JVeer en cow18 voor de uitleg!!
Ik ga hier verder mee stoeien.

Grt Maurice
 
Goedemiddag,

Om de macro helemaal af te maken zou ik wederom nogmaals graag wat hulp willen.

De lijst welke wij, een aantal keer per dag, via een website genereren is een overzicht van containers welke wij verwachten op onze terminal of welke reeds aangeleverd zijn.
Echter is het zo dat bepaalde type containers niet op deze lijst mogen staan. Het betreft hier alleen wel zo'n 500 iso codes ( zie lijst met ISO codes) welke dan uitgezonderd moeten worden.
Ik weet alleen niet of deze ook in de macro opgenomen kunnen worden en of dat de macro dan niet heel erg langzaam wordt.


Nog even alles op een rijtje..

Men logt in op een bepaalde website, selecteert hier wat filters en genereert daar een overzicht ( zie sheet ExportResults_office etc) welke men exporteert naar Excel.
Daarna moet de sheet wat bewerkt worden.

- De eerste 4 regels moeten verwijderd worden
- Alleen kolom B - C - E - F - G - AE - AN moeten behouden blijven (ook de objecten uit kolom AW moeten verwijderd worden)
- Dubbele container nummers verwijderen
- Filter op 1e regel zetten en deze regel vastzetten
- Breedte van de kolommen aanpassen aan de tekst
- Alle regels waar geen scheepsnaam (Carrier Name In) staat verwijderen behalve als de container Arrived (Container Status) is.
- Alle Flat en Open Top containers verwijderen (zie lijst met ISO codes)


Omdat we verschillende collega's in verschillende diensten hebben die deze lijst moeten maken neem ik aan dat de macro op ieders pc moet staan, of kan ik deze bijv. mailen naar een ieder??
Ik ben nog niet zo thuis in macro's dus vandaar deze vraag..

Alvast ontzettend bedankt voor de hulp!


Grt Maurice

Bekijk bijlage ISO codes (Open top- en flat containers).xls
Bekijk bijlage ExportResults_office_MKR001_Sun Dec 19 00_05_24 CET 2021.xlsm
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan