Validatie naar extern bestand

[JJ de zeeuw]

Ik heb een bestand gemaakt voor het invullen van projectgegevens, in de kolom van het personeel worden nu automatisch personeelsleden ingevuld van de bijbehorende machines in 90% van de gevallen is dat goed.

Maar ik wil er achter ook nog een lijst hangen van alle personeelsleden voor het geval dat er een keer een andere medewerker op die machine draait, zodat je die persoon dan uit een lijst kunt selecteren.

Echter het liefst zet ik die lijst ook weer in het externe bronbestand (waar nu alle brongegevens tot nu toe staan), maar volgens mij kan je geen gegevens valideren in extern bestand. Klopt dat?

Of hoe zouden jullie dit aanpakken?

 

[Roncancio]

Echter het liefst zet ik die lijst ook weer in het externe bronbestand (waar nu alle brongegevens tot nu toe staan), maar volgens mij kan je geen gegevens valideren in extern bestand. Klopt dat?

Nee, dat klopt niet.
Je kunt een datavalidatie toepassen naar een ander bestand mits dat bestand wel open is.

Hier wordt het uitgelegd voor Excel 2003. (Engelstalig).

Hier wordt het uitgelegd voor Excel 2007. (Engelstalig).

Met vriendelijke groet,


Roncancio

 

[JJ de zeeuw]

Mijn engels is toch onvoldoende om hieruit te komen. Iemand dit zelfde ook in nederlands of de juiste forumule om in te vullen voor het scherm van Valideren. Betreft office 2003.

Gr. Jan

 

[snb]

Werkwijze

- maak een apart werkblad aan.
- voeg daarin een databasequery (menubalk/data/externe gegevens/databasequery)
- zet in het werkblad waar de te valideren cel staat een dynamische tekstverwijzing naar het werkblad met de databasequery.
- definieer de validatie van de cel als lijst met verwijzing naar de cel, die verwijst naar de databasequery.

Voorbeeld:
- In Blad4 staat in cel A1 een Databasequery naar gegevensbestand E:\adressen.xls. Daarvan willen we de 4e kolom (kolom D) als validatielijst gebruiken
- De te valideren cel C4 staat in Blad1
- In Blad1 zetten we in de hulpcel AA1 de volgende formule
="Blad4!D1" & aantalarg(Blad4!D)
- Het gebied voor de validatie van Cel Blad1!C4 definiëren we met de formule:
= Indirect(AA1)

Het externe bestand wordt niet geopend.
De gegevens uit het externe bestand worden bij opening geaktualiseerd.
In feite kun je zo een apart extern valdatielijstenbestand maken, dat je op deze manier koppelt aan het bestand waarin die valdatielijsten gebruikt moeten worden.
Het wordt helemaal handig als je die validatielijsten in allerlei verschillende bestanden wil gebruiken.

 

[JJ de zeeuw]

Werkwijze

- maak een apart werkblad aan.
- voeg daarin een databasequery (menubalk/data/externe gegevens/databasequery)
- zet in het werkblad waar de te valideren cel staat een dynamische tekstverwijzing naar het werkblad met de databasequery.
- definieer de validatie van de cel als lijst met verwijzing naar de cel, die verwijst naar de databasequery.

Voorbeeld:
- In Blad4 staat in cel A1 een Databasequery naar gegevensbestand E:\adressen.xls. Daarvan willen we de 4e kolom (kolom D) als validatielijst gebruiken
- De te valideren cel C4 staat in Blad1
- In Blad1 zetten we in de hulpcel AA1 de volgende formule
="Blad4!D1" & aantalarg(Blad4!D)
- Het gebied voor de validatie van Cel Blad1!C4 definiëren we met de formule:
= Indirect(AA1)

Het externe bestand wordt niet geopend.
De gegevens uit het externe bestand worden bij opening geaktualiseerd.
In feite kun je zo een apart extern valdatielijstenbestand maken, dat je op deze manier koppelt aan het bestand waarin die valdatielijsten gebruikt moeten worden.
Het wordt helemaal handig als je die validatielijsten in allerlei verschillende bestanden wil gebruiken.

Klinkt goed, maar ik kom er niet aan uit hoe ik dit opzet, ben even met een voorbeeld bezig geweest om je stappen te volgen. Maar daar kom ik niet uit.

Kun je de stappen wat gedetailleerder opzetten?

Ik vraag alles maar want jullie komen iedere keer toch weer met een oplossing, ik sta nog steeds versteld van jullie kennis en bereidwilligheid.

Jan

 

[snb]

Gebruik deze macro in een leeg Excelbestand met 1 werkblad (sheet).
Vervang de blauw gemarkeerde tekst door de volledige naam (inc. pad) van het Excelbestand met de validatielijsten.
Zorg ervoor dat het bestand met de validatielijsten 1 werkblad (sheet) heeft, dat de naam Blad1 heeft, met in ieder geval gegevens in kolom A, B, C en D.

Sub tst()
  c0 = "[COLOR="Blue"]C:\validatielijstenbestand.xls[/COLOR]"
  ActiveSheet.Name = "werkblad1"
  With Sheets.Add
    .Name = "werkblad2"
    With .QueryTables.Add("ODBC;DSN=Excel-bestanden;DBQ=" & c0 & ";DriverId=790", .[A1])
      .CommandText = "SELECT `Blad1$`.*" & Chr(13) & "FROM `" & c0 & "`.`Blad1$` "
      .Refresh False
    End With
  End With
  Sheets("werkblad1").Range("AA1").Formula = "=" & Chr(34) & "werkblad2!D1:D" & Chr(34) & Chr(38) & "CountA(werkblad2!D:D)"
    Sheets("werkblad1").Cells(3, 4).Validation.Add xlValidateList, , , "=Indirect(AA1)"
End Sub

In de bijlage een bestand met deze voorzieningen.

Voer in cel A2 van werkblad1 de volledige naam in van het koppelen bestand met validatielijsten.
Klik op de knop 'koppel' en de koppeling wordt voor je gelegd via werkblad 2.
Daarna is de validatielijst voor cel D3 van werkblad1 automatisch aangepast.

 

[JJ de zeeuw]

Zucht, of het komt door dat het vrijdag middag is of omdat dit een stapje te ver voor mij is weet ik niet, maar ik kom er niet uit.

Ik heb je voorbeeld bekeken en geprobeerd mee te werken maar kom er toch nog niet uit.

Ik kan mijn eigen bestanden hier bij zetten, mogelijk wil je daar in kijken, anders moet ik het maar beschouwen als niet haalbaar voor mij.

 

[snb]

Ik heb aan mijn vorige post nog een bijlage toegevoegd. Bekijk die eerst eens.
En je feedback over wat je gedaan hebt met welk resultaat zou 'iets' specifieker mogen zijn. Dat kan dan voor meer bezoekers van dit forum informatief zijn.

 

[JJ de zeeuw]

Ik heb nogmaals het voorbeeld getest.

Ik heb zelf een test bronbestand gemaakt waarvan ik de link heb ingevuld. Daarna de koppeling gemaakt met de "knop koppeling" waarna de gegevens automatisch in werkblad2 gezet werden.

Daarna zou ik in het gevel vak een automatisch bijgewerkt validatie moeten hebben maar dat is niet het geval.

Verder zie ik met dit bestand even niet voor me hoe ik dit straks om kan vormen naar mijn eigen bestanden, maar dat terzijde.

Tot zo ver weer bedankt

 

[snb]

Blijkbaar worden de gegevens correect in werkblad 2 gezet.

Kijk nu wat er in werkblad1 in cel AA1 staat: kun je dat doorgeven ?

Kijk vervolgens wat de validatieformule van de gele cel (D3) in werkblad 1 is: kun je ook dat doorgeven ? (D3 selecteren/menubalk/data/valideren/ Lijst/ Bron )

 

[JJ de zeeuw]

Blijkbaar worden de gegevens correect in werkblad 2 gezet.

Kijk nu wat er in werkblad1 in cel AA1 staat: kun je dat doorgeven ?

Kijk vervolgens wat de validatieformule van de gele cel (D3) in werkblad 1 is: kun je ook dat doorgeven ? (D3 selecteren/menubalk/data/valideren/ Lijst/ Bron )

Cel AA1 staat werkblad2!D10 en de formule is ="werkblad2!D1"&AANTALARG(werkblad2!I:I)

D3 is =INDIRECT(AA1)

In werkblad2 staan gegevens in A4-F11

 

[snb]

Cel AA1 staat werkblad2!D10 en de formule is
="werkblad2!D1"&AANTALARG(werkblad2!I:I)

Daar zit de boosdoener. Dat moet zijn :

="werkblad2!$D$1:$D"&AANTALARG(werkblad2!$D:$D)

en maak van =Indirect(AA1) =Indirect($AA$1)

En het is ook wel handig als je ervoor zorgt, dat er ook gegevens in de eerste 3 rijen staan (A1:F11).

 

[JJ de zeeuw]

Daar zit de boosdoener. Dat moet zijn :

="werkblad2!$D$1:$D"&AANTALARG(werkblad2!$D:$D)

en maak van =Indirect(AA1) =Indirect($AA$1)

En het is ook wel handig als je ervoor zorgt, dat er ook gegevens in de eerste 3 rijen staan (A1:F11).

Dit werkt in ieder geval, de vertaling naar mijn eigen bestand raakt met nu nog even niet helemaal helder.
Zou even niet weten welke stappen ik moet gaan nemen, maar goed het raakt weekend en we zitten nu even biertje te doen dus ik ga er maandag weer mee verder.
Prettig weekend en bedankt weer tot zo ver!

 

[JJ de zeeuw]

Ik heb de stappen nogmaals langs gelopen, maar ik zou niet weten hoe ik het geheel over zou kunnen zetten naar mijn eigen document.

Het werken met de macro's is iets wat ik nog niet veel gedaan heb dus dat word moeilijk zeker?

En met het voorbeeld blijf je dus een werkblad houden binnen je document? Je kunt dus niet rechtstreeks valideren naar een extern document?

Gr. Jan

 

[snb]

Lees dit topic nog eens van voor naar achter door.

Het antwoord op je laatste vraag staat hier.

 

[JJ de zeeuw]

Ik ben er al weer even aan bezig om het voor elkaar te krijgen maar het wil nog niet echt lukken.
Ik ben begonnen om in een tabblad een databasesequery te maken, dit ging opzich redelijk en hij importeerde de gegevens uit mijn brongegevens bestand.
Toen de link in AJ5 gezet [="Personeeltotaal!$A$1:$A"&AANTALARG('[Personeeltotaal]Personeel totaal'!$A:$A)]
Daarna gevalideerd naar de cel AJ5, met de formule =INDIRECT($AJ$5) dan geeft hij een foutmelding dat dit resulteerd in een fout.

Daarnaast heb ik in mijn bronbestand 1 personeelslid als test toegevoegd, het bestand opnieuw opgestart om te kijken of hij nu automatisch bijwerkte. Dat was niet het geval.

Toen wilde ik het opnieuw proberen van af het begin maar kan nu Personeel totaal niet meer selecteren in de sequery. Dus toen bij mijn gegevensbronnen het bestand verwijderd om zo met schone lei te starten. Dit hielp niet.

Ik ben er dus mee bezig maar het resulteerd nog in niet kunnen jullie een fout ontdekken?

 

[snb]

Toen de link in AJ5 gezet [="Personeeltotaal!$A$1:$A"&AANTALARG('[Personeeltotaal]Personeel totaal'!$A:$A)]

Er moet geen link in AJ5 gelegd worden (waar heb j dat gevonden ?)
Er moet tekst in AJ5 komen met de formule

="Personeeltotaal!$A$1:$A"&AANTALARG(Personeel totaal!$A:$A)

 

[JJ de zeeuw]

Dat is puur een verkeerde uitdrukking van mijn zijde, maar die formule heb ik er ingeplaatst.