macro, gegevens importen vanuit extern bestand en met index? plakken in de tabel

[fonszzz]

Hoi!

Probleemomschrijving: ik zou graag data uit een exportbestand (een excel bestand genaamd: "jeugd bezit scholen") automatisch willen verwerken in het overzicht dat staat als een tabel in een ander bestand genaamd: "Collectie vervanging". Volgens mij kan ik een hoop stappen opnemen met een macro, maar helaas niet het stuk dat de juiste cel geselecteerd wordt aan de hand van het schoolnummer. Vanuit die selectie kan ik namelijk de gegevens als waarden in de tabel plakken.

Ik ben zelf al een beetje aan het puzzelen geweest maar kom er helaas niet helemaal uit.

Volgens mij moet ik nog de volgende stappen uitvoeren, liefst met behulp van een macro:

1. Automatisch selecteren van de data (cel F2:F10 in het werkblad "Invoer Macro Status") en deze data plakken als waarde in cel G2:G10 in werkblad "Invoer Macro Status").

2. Zoeken en automatisch selecteren van de correcte cel van de correcte school in tabel1 (werkblad "Status na Saneren") met behulp van het schoolnummer in cel F1 uit het werkblad "Invoer Macro Status") . (mbv vert.zoeken?)

3. Plakken van de cellen G2:G10 (werkblad "Invoer Macro Status") in de juiste rij én kolom van tabel1 uit het werkblad "Status na Saneren".

In het voorbeeld zou dus cel G2:G10 (werkblad "Invoer Macro Status") geplakt moeten worden in de cellen E69:M69 (=school nummer 044) van het werkblad "Status na Saneren".

Hopelijk kunnen jullie mij helpen!

Met vriendelijke groet,
Fons

 

[snb]

Je kunt beter het bestand 'jeugdbezit scholen' plaatsen, want met een simpele macro zijn dan alle tussenstappen in dit bestand overbodig.

 

[VenA]

Je zal ervoor moeten zorgen dat de te zoeken waarden met elkaar overeen komen. School 044 is iets anders dan school 44

Sub VenA()
With Sheets(" Status na saneren")
    Set s = .Columns(1).Find(Sheets("Invoer Macro Status").[F1].Value, , xlValues, xlWhole)
    If Not s Is Nothing Then Cells(s.Row, 5).Resize(, 9) = Application.Transpose(Sheets("Invoer Macro Status").Range("F2:F10").Value)
End With
End Sub

 

[fonszzz]

Bekijk bijlage jeugd bezit scholen.xls

Hoi @snb: De bijlage is toegevoegd. Ik ben benieuwd!

@VenA: ik zal zorgen dat dit gelijk wordt. Het enige wat ik hiervoor hoef te doen is kolom A in het tweede werkblad aan te passen.

 

[snb]

Het is ook handig als

- de kolomnamen in sheet2 (status na saneren) exact dezelfde namen hebben als de rijnamen in het bestand 'jeugdbezit scholen.xls'. (zie prentenboeken)
- de werkbladnaam ' status na saneren' niet met een spatie begint
- als geen enkele werkbladnaam een spatie bezit, maar bij voorkeur 'underscores'

Met deze macro moet het dan lukken:

Sub M_snb()
   c00 = "G:\OF\"
   c01 = "jeugd bezit scholen.xls"
   sn = Sheet2.Cells(1).CurrentRegion
   
   With GetObject(c00 & c01)
      sp = .Sheets(1).Cells(1).CurrentRegion
      .Close 0
    End With
    
    For j = 1 To UBound(sn)
       If sn(j, 1) = sp(1, 1) Or sn(j, 1) = Val(sp(1, 1)) Then
          sn(j, 14) = sp(1, 3)
          For jj = 2 To 9
             sn(j, Application.Match(sp(jj, 1), Application.Index(sn, 1), 0)) = sp(jj, 2)
          Next
          Exit For
        End If
    Next
    
    Sheet2.Cells(1).CurrentRegion = sn
End Sub

NB. de naam van de directory ("G:\OF\" in het voorbeeld) waarin zich het bestand bevindt moet je nog wel wijzigen.
Deze macro geeft nog een fout omdat de prentenboeken in beide bestanden een andere naam hebben.
Pas dat dus eerst aan in het bestand 'jeugd bezit scholen.xls'

 

[fonszzz]

Hoi snb

Ik ben een beetje aan het puzzelen merk ik. De stappen heb ik gezet zoals aangegeven.
Nu krijg bij de uitvoering de volgende foutmelding:



Weet jij wat ik fout heb gedaan?

 

[VenA]

Je hebt geen Sheet2. Dit moet je veranderen in Blad2

 

[fonszzz]

Dankjewel voor je snelle reactie VenA!
Ik heb alle woorden "sheet" veranderd in "blad"

Nu kom ik een tweede foutmelding tegen...

 

[VenA]

C00 moet eindigen met een "\" Dus NIEUW\"

 

[fonszzz]

Ik heb het aangepast naar aanleiding van je tip.
Helaas blijft dezelfde foutmelding actief.

Hieronder de 'nieuwe' code:


Voor de zekerheid stuur ik ook een screenshot mee van de maplocatie. Dit klopt dan toch zo?

Weet jij waarom de foutmelding blijft?

 

[VenA]

Nee dat klopt niet dat kan je toch zelf ook zien. Kopieer vanuit de verkenner de bestandslocatie. Dan weet je zeker dat je geen type fouten maakt.

 

[fonszzz]

Sorry was inderdaad niet erg goed van mij. De foutmelding is weg, maar ik krijg nu weer een foutmelding in de volgende regel van de macro...
Het maakt niet uit of "Blad" of "Sheet" gebruik, want in beide gevallen geeft die een foutmelding.

 

[VenA]

En hoe ziet dat bestand eruit? Iig niet zoals het bestand in #4. Waarschijnlijk beginnen jouw gegevens niet in A1

 

[snb]

Een beetje VBA studeren mag ook hoor (les 1 is dit):
Blad1. in plaats van Blad(1)

of

Sheets(1)

 

[fonszzz]

Klopt, ik heb wat punten veranderd naar aanleiding van jullie tips.

Ik zou het bestand graag naar je willen sturen, maar ik mag maar 100kb uploaden in dit forum.
Daarom heb ik beide bestanden naar Onedrive gezet. Misschien wil je vanuit daar eens kijken?

https://onedrive.live.com/redir?resid=1A193FBE1A953739!5953&authkey=!ALdYeaYLt-7dKgA&ithint=folder%2cxlsm

 

[fonszzz]

Hoi snb en VenA,

Fijn dat jullie mij willen helpen. Inderdaad heb ik nog veel te leren over macro's. Vandaar dat ik ook aan het bijleren ben (met jullie begeleiding)

Ik ben alweer een stuk verder gekomen

Nu loop ik tegen het volgende aan: