naam bestand = waarde cel in een ander bestand

[Humadgen]

Hoi,

Ik ben een Vakantie rooster aan het maken.
De bedoeling is als volgt:

1.er is een hoofdrooster waarin de manager de namen van de medewerkers noteert (= kolom A)
2.in de kolommen daarachter komen het aantal nieuwe vakantie rechten, wat is overgenomen van 2008, eventuele bij ge- of verkochte uren en het begin totaal.
3. vervolgens zijn de kolommen daarachter (tot nu toe) ieder jaar de dagen van het 1e half jaar
en bovenstaande herhaalt zich voor het 2e half jaar in er onder (met cel verwijzingen etc.)
Het hoofdrooster is password protected door de manager om te voorkomen dat medewerkers zelf uren invullen aangezien de manager controle moet houden op de bezetting per dag.

Bovenstaande heb ik al eeen aantal jaren geleden gemaakt en werkt goed.

Nu wil ik aan bovenstaande rooster individuele vakantie kaarten koppelen.
Dit betekend dat ieder manager een folder moet aanmaken waarin het bovengenoemde hoofdrooster + voor iedere medewerker een individuele vakantie kaart (=excel bestandje)komt te staan.
Ieder medewerker mag deze vakantie kaart ook zelf password protecten zodat hij zelf zijn uren ook goed kan vooruit kan plannen.

Ook dat gedeelte is geen probleem

De truc speciaal die ik nu aan het maken ben, is dat tussen de verschillende bestanden over en weer gegevens worden uitgewisseld zodat de manager kan zien of iedere maand hij en de medewerker hetzelfde aantal uren hebben afgeschreven.

Die communicatie is ook geen probleem.

Wat ik nu eigenlijk nog zou willen zien te bereiken is dat:
Als de manager het hoofdrooster en de vakantiekaarten gaat voorbereiden en hij de namen van de medewerkers invult in cellen A3, A4, A5 etc dat waarde van deze cellen gebruikt kunnen worden om de individuele vakantie kaarten mee op te slaan.

Voorbeeld: de waarde in het hoofdrooster in cell A3 is nu 1. De vakantiekaart die ermee correspondeert is 1.xls
De manager heeft beide bestanden open.
Als de manager in het hoofdrooster cel A3 verandert in Jan Jansen dan zou ik graag zien dat de vakantie kaart 1.xls ook opgeslagen gaat worden als Jan Jansen.xls zodat er geen typefouten gemaakt kunnen worden en de informatie van de individuele kaarten goed wordt ingelezen.

Misschien gaat het wel helemaal niet wat ik wil en moet ik gewoon tegen de managers zeggen dat het grote "o e t l u l l e n" zijn als ze niet eens een bestandje kunnen opslaan als Jan Jansen.xls zonder type fout te maken.

------------------------

Mocht bovenstande onmogelijk zijn dan heb ik nog zitten denken aan een ander soort oplossing, waarbij de manager en medewerker individueel "eigenaar" blijven van hun deel (Manager = jaarrooster, medewerker is vakantie kaart)
maar ook daarvan weet ik niet of het mogelijk is.

Het alternatief zou zijn dat het jaarrooster en de vakantie kaarten bij elkaar in 1 EXCEL bestand komen te staan maar dat de individuele sheets password protected zijn, waarbij iedere mederker zijn eigen password kan toekennen aan zijn eigen sheet.

Mocht iemand zich hier in vast willen bijten en een oplossing weten, dan zou ik dat echt super vinden.
Vriendelijke groet

Humadgen

 

[Roncancio]

In bijgevoegd bestand zit een macro die tekst van de A-kolom bewaard.
Zodra de naam verandert controleert de macro of er een bestand open is met de "oude" naam.
Is dat het geval (en alleen dan) dan wordt krijgt dat bestand de nieuwe naam.

Bijv.:
Het bestand met de macro en het bestand test zijn open.
Je beweegt door het bestand met de macro en als de A-kolom wordt geselecteerd, wordt de huidige celwaarde onthouden. Als de celwaarde test is en deze wordt veranderd in help, dan zoekt de macro naar het bestand "test". Dat bestand wordt dan hernoemd in help. Kan de macro het bestand niet vinden, dan gebeurt er ook niets.

Met vriendelijke groet,


Roncancio

 

[Humadgen]

Hoi Roncancio,

Wat je schreef klinkt als DE oplossing voor mijn vraag. Maar ik snap iets niet of ik doe iets fout.

Ik heb jou Excel bestandje opslaan[1].xls geopend en daarbij een tweede excel bestandje geopend die ik test.xls heb genoemd.
In opslaan[1].xls heb ik vervolgens ergens in cel A1 het woordje test getypt waarmee ik de begin situatie wegzet.
Als ik dan in jou instructie goed begrijp, dan zou ik nu cel A1 kunnen veranderen in help en dan zou het andere bestandje hernoemd worden.

Wat ik niet begrijp zijn:

1.) Normaal gesproken als ik code in VBA heb staan, dan heb ik dat staan bij - module1 of module2. De code die jij me doorstuurt staat bij : blad 1 (blad1)

2.) Ik neem aan dat er een actie moet plaatsvinden (een Macro activeren) om jou code zijn werk te laten doen. Maar ik kan de code die onder blad 1 (blad1) staat niet aan een Macro button hangen.

Dus mij vraag is hoe ik jou code geactiveerd krijg.
Ik lees me helemaal gek in boeken over EXCEL en programmeren in VBA, maar er zijn een aantal dingen die ik gewoon net niet "pak".

Dus als je hier ook nog een vervolg antwoord op kunt geven......
thanks
Humadgen

 

[Humadgen]

Roncancio

Ik ben het nog een keer (van begin af aan) gaan proberen en als ik nu het woord test heb ingevuld in cel A1 (en ook het bestandje test.xls open heb staan) heb ik (zoals je eigenlijk al had geschreven) kolom A geselecteerd.

Nu gebeurd er wel iets met de code, maar ik krijg een BUG op regel:

sName = Target.Value

verder gebeurd er nog niets wat erop lijkt dat het vestandje test.xls een andere naam gaat krijgen.

Grtnx
Humadgen

 

[Roncancio]

Hoi Roncancio,

Wat je schreef klinkt als DE oplossing voor mijn vraag. Maar ik snap iets niet of ik doe iets fout.

Ik heb jou Excel bestandje opslaan[1].xls geopend en daarbij een tweede excel bestandje geopend die ik test.xls heb genoemd.
In opslaan[1].xls heb ik vervolgens ergens in cel A1 het woordje test getypt waarmee ik de begin situatie wegzet.
Als ik dan in jou instructie goed begrijp, dan zou ik nu cel A1 kunnen veranderen in help en dan zou het andere bestandje hernoemd worden.

Wat ik niet begrijp zijn:

1.) Normaal gesproken als ik code in VBA heb staan, dan heb ik dat staan bij - module1 of module2. De code die jij me doorstuurt staat bij : blad 1 (blad1)

2.) Ik neem aan dat er een actie moet plaatsvinden (een Macro activeren) om jou code zijn werk te laten doen. Maar ik kan de code die onder blad 1 (blad1) staat niet aan een Macro button hangen.

Dus mij vraag is hoe ik jou code geactiveerd krijg.
Ik lees me helemaal gek in boeken over EXCEL en programmeren in VBA, maar er zijn een aantal dingen die ik gewoon net niet "pak".

Dus als je hier ook nog een vervolg antwoord op kunt geven......
thanks
Humadgen

De antwoorden op je vragen.
1.
Het klopt wat je zegt dat macro's in modules staan. Echter, de code is een "Event".
Dat houdt in dat het uitvoert zodra er een wijziging in het werkblad plaatsvindt.
Het hoeft dus niet in een module te staan.

2.
Eigenlijk hetzelfde als antwoord 1. De actie is de wijziging in de A-kolom.
Als je de code gaat verplaatsen naar een module dan kan er geen Target gevonden worden omdat de Target is gerelateerd aan de gewijzigde cel van dat moment.

Je kunt 2 dingen doen:
- Of je zet de gegevens van je bestand in mijn bestand.
- Of je zet de code van mijn bestand in jouw bestand maar dan wel exact op de plek waar het ook in mijn bestand stond.

Het was maar een voorbeeld met test.
Als je mijn bestand opent samen met een willekeurig bestand en je selecteert de naam van dat bestand in de A-kolom om het vervolgens in de A-kolom te veranderen, dan behoort de naam van het bestand ook te veranderen.

Als er nog vragen zijn, dan hoor ik het graag.

Met vriendelijke groet,


Roncancio

 

[Humadgen]

Hoi Roncancio

Bedankt voor je vervolg uitleg, dat is weer iets waar ik wat van leer. Maar ik blijf tegen hetzelfde probleem aanlopen wat ik in mijn vervolgvraag heb gezet.

Ik heb nu in jou bestandje kolom A van waardes voorzien.
aangezien de bijbehorende bestandjes 1.xls, 2.xls etc zijn, staat er in kolom A nu een getallen reeks van 1 t/m 30

Ik heb 1.xls samen met jou bestandje geopend staan, en zodra ik nu kolom A selecteer dan krijg ik
Run-time error '13':
Type mismatch

Als ik dan Debug kies, dan is de code gestopt op regel

sName = Target.Value

Dus weet je wat er nu nog fout gaat???
Thanks
Humadgen

 

[Roncancio]

Bovenin het script heb ik sName gedefinieerd als zijnde tekst. Dim sName As String
Ook voor getallen zou het geen problemen mogen geven.

Je kunt het oplossen door sName anders te definieren.

Je zou het kunnen definiëren als een getal:
- Dim sName As Integer
of
- Dim sName As Long
of
- Dim sName As Double


Of Alles.
- Dim sName As Variant

Variant accepteert alle types maar wordt bvk niet vaak gebruikt omdat het relatief veel geheugenruimte inneemt.

Met vriendelijke groet,


Roncancio

 

[Humadgen]

hoi Roncancio,
Daar ben ik toch weer, en ik zou willen dat ik kon zeggen "hoera het werkt", maar niets is minder.
Ik heb alle sName definities geprobeert en bij allen kreeg ik direct dezelfde foutmelding als bij de As String.

Enkel bij Dim sName As Variant kwam de foutmelding niet.
Dat gaf me hoop, maar ik zie niet wat er daarna moet gebeuren.

Kun je voor mij de onderstaande stappen aanvullen / verbeteren waar het fout gaat.
Uit je verhaal begrijp ik dat het zo ongeveer zou moeten werken, maar ik zie niets gebeuren.

Stap 1. Ik open het bestand opslaan.xls waarin de door jou geschreven code staat. (Hierin staat in cel A1 het getal 1)
Stap 2. Ik open daarbij het bestandje 1.xls
Stap 3. Ik selecteer cel A1 in opslaan.xls en verander in cel A1 de waarde 1 in de naam Piet en hit <enter>
Stap 4. Ik save en sluit opslaan.xls
Stap 5. Ik save? en sluit 1.xls (wat dan zou moeten veranderen in Piet.xls)

Misschien moet ik ergens save as gebruiken ,of moet eerst 1.xls gesloten worden, maar welke volgorde ik ook gebruik, er gebeurd niets.
Dus vandaar mijn vraag of bij jou de wijziging van de bestandsnaam wel lukt en wat dan de volgorde van handelen bij jou is.

Wederom bij voorbaat bedankt voor je hulp.
Grtnx
Humadgen

 

[Roncancio]

hoi Roncancio,
Daar ben ik toch weer, en ik zou willen dat ik kon zeggen "hoera het werkt", maar niets is minder.
Ik heb alle sName definities geprobeert en bij allen kreeg ik direct dezelfde foutmelding als bij de As String.

Enkel bij Dim sName As Variant kwam de foutmelding niet.
Dat gaf me hoop, maar ik zie niet wat er daarna moet gebeuren.

Kun je voor mij de onderstaande stappen aanvullen / verbeteren waar het fout gaat.
Uit je verhaal begrijp ik dat het zo ongeveer zou moeten werken, maar ik zie niets gebeuren.

Stap 1. Ik open het bestand opslaan.xls waarin de door jou geschreven code staat. (Hierin staat in cel A1 het getal 1)
Stap 2. Ik open daarbij het bestandje 1.xls
Stap 3. Ik selecteer cel A1 in opslaan.xls en verander in cel A1 de waarde 1 in de naam Piet en hit <enter>
Stap 4. Ik save en sluit opslaan.xls
Stap 5. Ik save? en sluit 1.xls (wat dan zou moeten veranderen in Piet.xls)

Misschien moet ik ergens save as gebruiken ,of moet eerst 1.xls gesloten worden, maar welke volgorde ik ook gebruik, er gebeurd niets.
Dus vandaar mijn vraag of bij jou de wijziging van de bestandsnaam wel lukt en wat dan de volgorde van handelen bij jou is.

Wederom bij voorbaat bedankt voor je hulp.
Grtnx
Humadgen

Hmm, opmerkelijk.
Stap 5 zou niet nodig moeten zijn.
Wat je zou kunnen doen is een onderbrekingspunt zetten bij Worksheets_Change.
Dat is een rode cirkel in de kantlijn van de code.
Als er een wijziging plaatsvindt in het werkblad (bijvoorbeeld dus 1 in A1 veranderen in Piet) dan stopt het programma op dat punt. Je kunt dan met F8 de stappen van de code doorlopen om te zien wat het programma daarna doet.
Het zou bijvoorbeeld goed kunnen dat de code het bestand 1 niet kan vinden.

Met vriendelijke groet,


Roncancio

 

[Warme bakkertje]

Roncancio & Humadgen, zo doet hij het bij mij wel

Dim sName As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ChDir "D:\Mijn documenten\Helpmij\"  'naar wens aanpassen
If Target.Column = 1 And sName <> "" Then
    Dim WB As Workbook
    Set WB = Workbooks(sName)
        If Not WB Is Nothing Then
            Workbooks(sName).SaveAs Filename:=Target.Value & ".xls"
        End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    sName = Target.Value & ".xls"
End If
End Sub

Mvg

Rudi

 

[snb]

Het lijkt mij slimmer een aparte cel te gebruiken voor de oude naam (bijv.A1) en de nieuwe naam (bijv A2.)
Dan lijkt mij dit al genoeg:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  if target.address="$A$2" then Workbooks([A1].value).SaveAs Workbooks([A1].value).Path & "\" & Target.Value
End Sub

Toelichting:
Als target meer dan 1 cel bevat gaat het feest niet door
Als er geen werkboek open staat dat overeenkomt met de naam in A1, gebeurt er niets vanwege de fout
Als in A2 geen geldige bestandsnaam staat gebeurt er evenmin wat.
In alle andere gevallen heeft een van de geopende werkboeken de naam die overeenkomt met A1, bevat A2 kennelijk een geldige bestandsnaam, waarmee het bestand wordt hernoemd.

 

[Humadgen]

Beste Roncancio en Warme Bakkertje.

Het is gelukt. Super hoor. Heel erg bedankt.:thumb:
Het verschil zat hem in de & ".xls" die Warme Bakkertje had toegevoegd.
De andere (extra) regel die Warme Bakkertje erbij had gedaan en die begint met ChDir ..etc was niet eens nodig.

Ik loop nog wel tegen iets anders aan.
Alles gaat nu dus goed, maar indien ik "per ongeluk" 2 of meer cellen in Kolom A selecteer komt de eerder genoemde foutmelding weer terug. Heeft een van jullie daar nog een idee voor om dat op te lossen? Want dat zal bij de verschillende managers ongetwijfeld regelmatig kunnen gebeuren en die wil ik niet met die foutmelding laten zitten (of continue mij laten bellen). Misschien in de trent van: Als je meer dan 1 cel selecteert in kolom A dan gebeurt er niets en kun je niets enteren of zo???? Ik bedenk maar iets?

Dan heb ik nog twee kleine aanvullende dingen (ik weet niet of ik die direct in deze vraag moet meenemen of apart moet stellen in nieuwe vragen?)

extratje 1. Is het mogelijk om, nadat het bestandje 1.xls is hernoemd naar Piet.xls ook automatisch het oude bestandje 1.xls (de oude bestandsnaam, te laten te laten verwijderen uit de folder?

extratje 2. Is het mogelijk om de bestanden (hoofdrooster met de Macro) + de individuele bestandjes 1.xls etc automatisch altijd te laten Updaten bij het openen, zodanig dat de vraag die automatisch opkomt wordt vermeden

Grtnx
Humadgen

Ps. SNB jij ook bedankt voor het meedenken. Ik had jou oplossing nog niet gezien aangezien ik met de andere oplossingen aan het testen was (tussen de voetbalwedstrijden op Eredivisie live door)

 

[snb]

In reaktie op 1 van je nieuwe vragen:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  if target.address="$A$2" then 
     c0=Workbooks([A1].value).Path 
     Workbooks([A1].value).close true
     Name c0 &  "\" & [A1].value as c0 &  "\" & Target.Value
  End if
End Sub

 

[Humadgen]

Hoi SNB

Als ik mijn best doe om je code te begrijpen, dan denk ik dat dit de oplossing moet zijn voor het verwijderen van het oudebestandje (toch). Maar het lijkt erop dat je code zich richt op de cellen A1 en A2 (of zie ik dat verkeerd)

Ik weet niet of je het eerste stuk van mijn vragen gezien hebt, maar het gaat om bestandsnamen die in cellen A7 t/m A37 staan en van te voren weet ik dus niet welke cel wordt aangepast en welke bestandsnaam wordt aangepast.

Maar misschien begrijp ik je code verkeerd en zet ik hem ook verkeerd weg.

Ik heb de code toegevoegd aan het werkblad, (onder de al bestaande code van Roncancio en warme Bakkertje), maar ik krijg zodra ik cellen selecteer de foutmelding:
Compile Error
"ambigious name detected: Worksheet_change"

Wat doe ik verkeerd met het wegzetten van de code, en klopt het wat ik dacht dat jou code alleen nar cellen A1 en A2 kijkt?

Grtnx en ook jij weer bedankt voor je snelle meedenken.
Humadgen

 

[Warme bakkertje]

Heb de code van snb moeten aanpassen om hem werkend te krijgen. De & ".xls" lijkt toch noodzakelijk te zijn. Zet onderstaande achter het blad. De code vervangt de oude bestandsnaam door de nieuwe, dus moet er niets verwijderd worden. Zoals je zelf al zei, komt in A1 de huidige naam en in A2 de nieuwe naam. Is het misschien mogelijk om met een validatielijst in A1 te werken zodat je enkel deze twee cellen nodig hebt ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    sName = Target.Value & ".xls"
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Address = "$A$2" Then
     c0 = Workbooks([A1].Value & ".xls").Path
     Workbooks([A1].Value & ".xls").Close True
     Name c0 & "\" & [A1].Value & ".xls" As c0 & "\" & Target.Value & ".xls"
  End If
End Sub

Mvg

Rudi

 

[snb]

Deze code mag weg:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    sName = Target.Value & ".xls"
End If
End Sub

Correctie

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Address = "$A$2" Then
     With Workbooks([A1].Value & ".xls")
       c0 = .Fullname
       .Close True
     End With
     Name c0  As Replace(c0 ,[A1].Value & ".xls",Target.Value & ".xls")
  End If
End Sub

De toevoeging van xls is natuurllijk afhankelijk van wat er in cel A1 staat.
Overigens begrijp ik niet waarom, als de medewerkerskaart een deelverzameling van de hoofdkaart is, dat deel van het hoofdbestand niet als medewerkerskaart wordt opgeslagen. De leidinggevende kan dan eenvoudig 'zijn/haar' hoofdkaart bewerken, terwijl automatisch de medewerkerskaarten (als selectie van de hoofdkaart) worden opgeslagen.

 

[Roncancio]

Met If Target.Count = 1 Then aan het begin van de code kan je bepalen dat er maar 1 cel geselecteerd mag zijn voor dat de rest van de code doorlopen wordt.
Voeg aan het eind van de code ook End If toe.

Verder weet ik niet of en welke vragen er nog open staan.

Met vriendelijke groet,


Roncancio

 

[Humadgen]

Hoi SNB, Roncancio en Warme Bakker.

Jullie hebben alledrie al geweldig geholpen. Maar ik loop hier en daar toch nog tegen dingen aan.
Eerst een antwoord op de vraag van SNB:
Het mag geen kopie van het hoofdprogramma zijn om een onafhankelijke "reconciliatie" mogelijk te maken tussen wat de manager heeft vast gelegd en wat de medewerker heeft vastgelegd. Doordat ieder in zijn eigen EXCEL bestand gegevens vastlegd worden fouten voorkomen. (dit is een praktijk gegeven en vandaar dat ik het op deze manier opzet.)

Je moet erbij weten dat ik dit programma over meerdere locaties in de hele wereld moet uitzetten dus vandaar dat er zo min mogelijk fouten mogen komen. ik moet het simpel proberen te houden.

---
Wat ik tot nu toe heb bereikt met jullie hulp is dat vanaf het hoofdbestand ik de namen van de andere bestandjes kan veranderen en met de laatste opmerking van Roncancio is het probleem ook verholpen waarbij ik een fout krijg als er meer dan 1 cel geselecteerd worden.

De vraag die Warme Bakkertje stelde om met een validatie lijst te werken in Cel A1 en A2 spreekt me helaas niet aan.

ik blijf daarom met 2 vragen zitten.
1. Kan het oude bestandje ( in het voorbeeld steeds 1.xls) dat een nieuwe naam heeft gekregen (Piet.xls) middels code worden vervwijderd, want nu komen er steeds bestanden bij als er iets in Kolom A wordt veranderd.
De code die SNB en Warme Bakkertje tot nu toe leverden wil ik daar niet voor gebruiken.

en een minder belangrijke vragen:
2. Is het mogelijk om de bestanden (hoofdrooster met de Macro) + de individuele bestandjes 1.xls etc automatisch altijd te laten Updaten bij het openen, zodanig dat de vraag die automatisch opkomt wordt vermeden

Dus mochten jullie daar nog iets voor hebben.
Thanks again
Humadgen

 

[Roncancio]

Ik weet niet precies met welke versie je werkt, dus geef ik je mijn code.

Dim sName As String
[B]Dim sOudBest As String[/B]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 And sName <> "" Then
    Dim WB As Workbook
    Set WB = Workbooks(sName)
        If Not WB Is Nothing Then
            Workbooks(sName).SaveAs Target.Value
[B]            Kill sOudBest[/B]
        End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 Then
    sName = Target.Value
[B]    If Target.Value <> "" Then
        sOudBest = Workbooks(sName).FullName
    End If[/B]
End If
End Sub

De belangrijkste wijzigingen zijn vet weergegeven.

Dus:
Er is een bestand geopend bijvoorbeeld 1.
Via de A-kolom wordt het hernoemd naar Jan Jansen en wordt 1 uit de directory verwijderd.

Vraag 2 kan ik niet helemaal plaatsen.

Met vriendelijke groet,


Roncancio

 

[snb]

Ben je bekend met de funktionaliteit Databasequery (menu/data/externe gegevens/databasequery) in Excel ?
Die doet wat jij nu zelf in VBA-wil maken.