Zoeken op de map waar het bestand staat vervolgens verticaal zoeken

Robert Smidt · 23 jan 2016

Beste Helpmij-ers,

Ik heb een vraag over een formule in Excel.

Ik heb een bestand dat zijn informatie zoekt in een andere map, maar omdat ik met meerdere pc's en met Dropbox werk bevindt zich de map "Dropbox" per pc in verschillende mappen;

Pc1
C:\Users\Gebruiker\Dropbox\Cashflow Control\Boekhouding klanten

pc 2
C:\Users\It's Amazing\Dropbox\Cashflow Control\Boekhouding klanten

Nu heb ik via Helpmij ooit eens onderstaande formule gekregen om te beoordelen in welke mappenstructuur een bestand zich bevindt.
=LINKS(CEL("bestandsnaam");VIND.SPEC("[";CEL("bestandsnaam"))-1)

Vraag 1
Mijn wens is een formule die alleen de eerste twee mappen aangeeft Dus: voor pc1C:\Users\Gebruiker\ en voor pc2 C:\Users\It's Amazing\

Vraag twee
Bovenstaande heb ik nodig om (verticaal) te zoeken in een ander bestand.

De huidige formule is: =VERT.ZOEKEN(A2;'C:\Users\Gebruiker\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]Invulblad'!$A$3:$B$10000;2;ONWAAR)

Hier moet: "C:\Users\Gebruiker" vervangen worden door het antwoord vanuit vraag 1.

Ik hoop dat de vraag helder is, mocht dat niet zo zijn dan hoor ik het graag om deze te verduidelijken.

Alvast heel erg bedankt.

Met vriendelijke groet,
Robert

edmoor · 23 jan 2016

Zet deze functie (UDF) in een module:

Code:

Function Profielmap() As String
    Profielmap = Environ("userprofile")
End Function

Vervolgens:

Code:

=VERT.ZOEKEN(A2;Profielmap()&'\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]Invulblad'!$A$3:$B$10000;2;ONWAAR)

Maar je hebt een gebruiker met "It's Amazing" als naam?

Robert Smidt · 23 jan 2016

Dank je voor de formule

Deze geeft nu: #NAAM?

Ik heb de eerste code (UDF) geplaatst via Alt F11 in Module 1
Het betreffende werkblad (Blad1 (factuur)) heb ik de code ook in geplaatst

Wellicht dat ik iets niet juist doe.

edmoor · 23 jan 2016

Die melding geeft aan dat de functie niet gevonden werd.
Die functie mag ALLEEN in de module staan.

Plaats anders je document eens.

Robert Smidt · 23 jan 2016

Bekijk bijlage Verkoopfactuur.xlsm
Helaas nog steeds niet gelukt, ik stuur je het bestand bij deze

edmoor · 23 jan 2016

Je hebt 'm in de Algemeen sectie van de module gezet en daar hoort 'ie niet. Dat geeft de foutmelding in VBA ook aan.
Zet 'm zo in die module:

Code:

Public FactuurNummer1 As Double
Public ReageerOpTweedeKlik As Integer
Public Pad As String
Public AcceptGiro As Integer
Public Eur As Double
Dim big(10, 1) As Integer

[COLOR="#FF0000"]Function Profielmap() As String
    Profielmap = Environ("userprofile")
End Function[/COLOR]

'Sub ConversieMacroVersie4NaarVersie5()
'    frmConversie.Show
'End Sub

'N I E U W  F A C T U U R  A A N M A K E N!
..
.

Cobbe · 23 jan 2016

Test deze functie eens:

Code:

Public Function locatie()
Dim WrdString As String
 Dim text_string As String
  text_string = Replace(ThisWorkbook.FullName, "\", " ")
   WrdString1 = Split(text_string)(1)
   WrdString2 = Split(text_string)(2)
   WrdString3 = Split(text_string)(3)
   Schijf = CreateObject("scripting.filesystemobject").GetDriveName(ThisWorkbook.FullName)
locatie = Schijf & "\" & WrdString1 & "\" & WrdString2 & "\"
End Function

Robert Smidt · 23 jan 2016

Sorry beste mensen,

Ik heb beide opties geprobeerd, de laatste heb ik op deze wijze weggezet wellicht dat dat niet juist is.

Code:

Public FactuurNummer1 As Double
Public ReageerOpTweedeKlik As Integer
Public Pad As String
Public AcceptGiro As Integer
Public Eur As Double
Dim big(10, 1) As Integer

Public Function locatie()
Dim WrdString As String
 Dim text_string As String
  text_string = Replace(ThisWorkbook.FullName, "\", " ")
   WrdString1 = Split(text_string)(1)
   WrdString2 = Split(text_string)(2)
   WrdString3 = Split(text_string)(3)
   Schijf = CreateObject("scripting.filesystemobject").GetDriveName(ThisWorkbook.FullName)
locatie = Schijf & "\" & WrdString1 & "\" & WrdString2 & "\"
End Function


'Sub ConversieMacroVersie4NaarVersie5()
'    frmConversie.Show
'End Sub

edmoor · 23 jan 2016

Wat ik schreef in #6 is gewoon gewoon goed. Wat Cobbe voor je maakte is een andere interpretatie van het ophalen van je gegevens en ook die is goed.
Je zegt wel iedere keer dat het niet goed gaat maar je zegt er niet bij wat er dan mis gaat.

Robert Smidt · 23 jan 2016

sorry mijn fout, hij geeft nog steeds dezelfde melding #WAARDE!

Ik heb inmiddels jouw code er weer in gezet op de plaats die jij aangaf

Cobbe · 23 jan 2016

De functie van @cobbe dien je ten alle tijden in een Module te plakken, anders krijg je steeds als resultaat #NAAM

Robert Smidt · 23 jan 2016

Dit is toch een hardnekkige denk ik.

Hij geeft nu ook de melding:

Complieerfout
Er mogen alleen opmerkingen staan na End Sub, End function of En Prorty

Ik heb inmiddels jullie beide codes helemaal onderaan gezet om voorgaande te voorkomen, ook dat bood geen soelaas

Cobbe · 23 jan 2016

Voeg een nieuwe Module in ---> Module2
Dubbelklik op Module2 en plak nu mijn code in het rechtse vak.

Roep nu in A2 deze functie aan : =locatie()

Dan gaat het goed komen.

edmoor · 23 jan 2016

Robert Smidt zei:
Dit is toch een hardnekkige denk ik.

Hij geeft nu ook de melding:

Complieerfout
Er mogen alleen opmerkingen staan na End Sub, End function of En Prorty

Ik heb inmiddels jullie beide codes helemaal onderaan gezet om voorgaande te voorkomen, ook dat bood geen soelaas

Dan heb je hem weer in de Algemeen sectie gezet of de code fout overgenomen.

Robert Smidt · 23 jan 2016

Ik heb het advies van Cobbe opgevolgd en denk dat ik nu een stuk dichterbij ben. Vraag 1 is opgelost. Het systeem zet in A2 C:\Users\gebruiker.

Helaas werkt vraag twee nog niet; staat nog steeds #NAAM?

Timshel · 23 jan 2016

Je kunt voor links naar andere bestanden eigenlijk geen variabelen gebruiken. De suggestie van EdMoor uit #2:

Code:

=VERT.ZOEKEN(A2;Profielmap()&'\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]Invulblad'!$A$3:$B$10000;2;ONWAAR)

gaat helaas niet werken.
Excel heeft de functie INDIRECT om verwijzingen als tekenreeks op te bouwen. Dit werkt voor wat betreft links naar andere bestanden echter alleen zolang die andere bestanden geopend zijn. Dat is dus ook niet echt een oplossing.
Ik zou het met een changelink doen vanuit het workbook_open-event (in de thisworkbook-module):

Code:

Private Sub Workbook_Open()
    Dim sLink
    
    For Each sLink In ThisWorkbook.LinkSources
        If InStr(sLink, "[Klantbeheer.xlsm]") > 0 Then
            ThisWorkbook.ChangeLink sLink, Environ("userprofile") & "\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]", 1
            Exit For
        End If
    Next
End Sub

...waarbij je de huidige formule intact laat:

Code:

=VERT.ZOEKEN(A2;'C:\Users\Gebruiker\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]Invulblad'!$A$3:$B$10000;2;ONWAAR)

Robert Smidt · 23 jan 2016

De laatste code begrijp ik niet helemaal in combinatie met het verticaal zoeken zoals ik aanvankelijk had bedoeld.

In tussentijd ben ik behoorlijk aan het experimenteren geweest en dacht de oplossing te hebben. Om tot een pad te komen zijn er diverse mogelijkheden bijv.

=locatie()&"Boekhouding klanten\"&A3&"" dit resulteert in "C:\Users\Gebruiker\Dropbox\Cashflow Control\Boekhouding klanten\00. Testmap"

Zoals je weet kun je vrij eenvoudig een pad samenstellen, echter wanneer je refereert naar een cel uit een bestand vanuit een andere directory, dan lukt dat niet door vb

='C:\Users\Gebruiker\Dropbox\Cashflow Control\Boekhouding klanten\00. Testmap\[Kas- bankboek.xlsm]Persoonlijke instelling'!$E3

Mijn bedoeling is om het hele pad over te nemen en alleen (00. Testmap) te vervangen door de waarde in A3 dus:
='C:\Users\Gebruiker\Dropbox\Cashflow Control\Boekhouding klanten\&A3"&\[Kas- bankboek.xlsm]Persoonlijke instelling'!$E3

Als iemand hier iemand een oplossing voor heeft, dan ben ik volgens mij helemaal geholpen. Dan heb ik ook gelijk de oplossing "Gebruiker" te vervangen door "It's Amazing.

Timshel · 23 jan 2016

Vervelend dat je het niet snapt. De code uit #16 is namelijk wel waar de oplossing ligt.
Wat je nu probeert kan niet werken. Het is in Excel niet mogelijk om met formules een dynamische verwijzing te maken naar een ander excelbestand.
Ik raad aan toch nog eens naar post #16 te kijken.

Robert Smidt · 23 jan 2016

Omdat ik niet goed snap wat de bedoeling is volg jouw instructies nauwlettend op. Moet ik nog iets met de codes die ik ontvangen heb?

Er stond overigens ook al iets in ThisWorkbook, is dat bezwaarlijk?

Code:

Private Sub Workbook_Open()
    Call LeesFactuurnummer
    Call Bewaarfactuurnummer
    Call NoteerFactuurnummer
End Sub

Private Sub Workbook_Open()
    Dim sLink
    
    For Each sLink In ThisWorkbook.LinkSources
        If InStr(sLink, "[Klantbeheer.xlsm]") > 0 Then
            ThisWorkbook.ChangeLink sLink, Environ("userprofile") & "\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]", 1
            Exit For
        End If
    Next
End Sub

edmoor · 23 jan 2016

Dan moet je ze samenvoegen. Een routinenaam moet uniek zijn:

Code:

Private Sub Workbook_Open()
    Dim sLink

    Call LeesFactuurnummer
    Call Bewaarfactuurnummer
    Call NoteerFactuurnummer
    
    For Each sLink In ThisWorkbook.LinkSources
        If InStr(sLink, "[Klantbeheer.xlsm]") > 0 Then
            ThisWorkbook.ChangeLink sLink, Environ("userprofile") & "\Dropbox\Cashflow Control\Klantbeheer\[Klantbeheer.xlsm]", 1
            Exit For
        End If
    Next
End Sub

Zoeken op de map waar het bestand staat vervolgens verticaal zoeken

Gebruiker

Inventaris

Gebruiker

Inventaris

Gebruiker

Inventaris

Giga Honourable Senior Member †

Gebruiker

Inventaris

Gebruiker

Giga Honourable Senior Member †

Gebruiker

Giga Honourable Senior Member †

Inventaris

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Inventaris

Wij waarderen jouw privacy