[EXCEL] Formule waarde = cel waarde

[Verwijderd]

Ik ben een formule aan het maken die de inhoudt van een ander excel bestand ophaalt en hier invoegt.
Dit werkt:
='C:\\[ab001.xls]Blad1'!$B$4

Maar nu wil ik dat ab001.xls variabel is en deze waarde dus uit bijv. cel A1 haalt.
Ik heb onderstaande allemaal al geprobeerd, maar zonder suc6
='C:\[A1]Blad1'!$B$4
='C:\\[(A1)]Blad1'!$B$4
='C:\\["A1"]Blad1'!$B$4
='C:\\['A1']Blad1'!$B$4

[ Voor 12% gewijzigd door Verwijderd op 14-11-2005 19:26 ]

[Verwijderd]

Wel dat is toch een ongeldige verwijzing? Je wijst naar een cel, maar omdat je het na c:\ zet gaat hij volgens mij de cel A1 niet in de werkmap zoeken waar je de formule in plaatst.

[Daos]

Je moet INDIRECT gebruiken.

=INDIRECT("'C:\\[" & A1 & "]Blad1'!$B$4")

[ Voor 46% gewijzigd door Daos op 14-11-2005 19:42 ]

[Verwijderd]

Daos schreef op maandag 14 november 2005 @ 19:40:
Je moet INDIRECT gebruiken.

=INDIRECT("'C:\\[" & A1 & "]Blad1'!$B$4")

Dan krijg ik de melding #VERW!

[ Voor 20% gewijzigd door Verwijderd op 14-11-2005 19:58 ]

[Daos]

En als je ab001.xls ook open hebt staan?

[Verwijderd]

Daos schreef op maandag 14 november 2005 @ 20:09:
En als je ab001.xls ook open hebt staan?

Dan krijg ik nog steeds dezelfde melding.

[onkl]

Probeer eerst eens, zonder gebruik van INdirect, precies dezelfde tekst "op te bouwen" als die achter de = in je originele, werkende verwijzing. Let ook op ' enkele aanhalingstekens, Excel is er verzot op.
ALs dat klopt, zet je er =INDIRECT( .... ) omheen en het zou kunnen werken.

[Verwijderd]

onkl schreef op maandag 14 november 2005 @ 21:39:
Probeer eerst eens, zonder gebruik van INdirect, precies dezelfde tekst "op te bouwen" als die achter de = in je originele, werkende verwijzing. Let ook op ' enkele aanhalingstekens, Excel is er verzot op.
ALs dat klopt, zet je er =INDIRECT( .... ) omheen en het zou kunnen werken.

Als ik bij een werkende formule, dus zelfs nog zonder de verwijzing van het bestand naar A1, =INDIRECT( .... ) omheen zet, krijg ik al #VERW!

[Verwijderd]

Daos schreef op maandag 14 november 2005 @ 20:09:
En als je ab001.xls ook open hebt staan?

Ik heb het nogmaals geprobeerd. Eerst ab001.xls geopend. Daarna het andere bestand, formule erin gezet en nu doet hij het.
We zijn al iets verder, maar ab001 gaat tot ab100. Ik hoop toch niet dat ik nu elke keer die 100 bestanden moet openen....

INDIRECT heeft ook als probleem dat als je het bron bestand sluit, er na een tijdje als nog #VERW! komt te staan, dit hebt je niet met een koppeling...

AUB help

[ Voor 18% gewijzigd door Verwijderd op 14-11-2005 22:56 ]

[Daos]

Je kan ook in VBA een eigen functie maken die een workbook opent, waarden er uitleest en vervolgens weer sluit.

[Verwijderd]

Daos schreef op dinsdag 15 november 2005 @ 10:57:
Je kan ook in VBA een eigen functie maken die een workbook opent, waarden er uitleest en vervolgens weer sluit.

Sorry maar ik weet niets van VBA. Lijkt me toch dat dit ook gewoon standaard met excel moet kunnen. Ik neem aan dat ik niet de enige ben die het op deze wijze wil gaan doen. Toch.

[onkl]

Ik ben bang dat je toch naar VBA moet kijken. Als het Excel niet lukt de bestanden te openen zal er weinig anders opzitten.
Moeilijk is het niet.
Hier een opzetje, waarmee je wat kan prutsen.

Visual Basic:

Sub ophalen()
Dim anderwb As Workbook
Dim cel As Range
Set cel = ActiveCell
Dim fs As FileSearch
With fs
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "C:\" 'Deze aanpassen dus.
    .SearchSubFolders = False
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            Set anderwb = Workbooks.Open(.FoundFiles(i))
            cel.Value = anderwb.Worksheets(1).Range("A1").Value
            'ofwel: maak waarde van cel (hierboven gedefinieerd) gelijk aan
            'waarde van cel A1 in tabblad 1 van het gevonden bestand.
            cel.Offset(0, 1).Value = anderwb.Name
            'plak de naam van het bestand ernaast.
            Set cel = cel.Offset(1, 0)
            'verplaats "cel" 1 naar beneden.
        Next i
    Else
        MsgBox "There were no files found."
    End If

End With
End Sub

Type in excel alt-F11, maak in de het VBA project met de naam van je bestand (linksboven) een nieuwe module aan (rechtermuisknop) en plak deze code erin.
Zet vervolgens de directory goed en eventueel verander je het tabbladnummer en de "A1".
In Excel kan je nu (alt-F8) een macro "ophalen" uitvoeren.
Dan zal er, vanaf je actieve cel, een rijtje waardes en bestandsnamen gecreerd worden.
(of er crashed iets, maar dat horen we dan wel )

[ Voor 7% gewijzigd door onkl op 15-11-2005 11:44 . Reden: subfolders = false is beter als je in C root gaat zoeken :) ]

[edeboeck]

Verwijderd schreef op dinsdag 15 november 2005 @ 11:04:
[...]


Sorry maar ik weet niets van VBA. Lijkt me toch dat dit ook gewoon standaard met excel moet kunnen. Ik neem aan dat ik niet de enige ben die het op deze wijze wil gaan doen. Toch.

Omdat je zegt dat je niets afkent van VBA, heb ik hier misschien een compleet andere oplossing voor jou:

1/ je maakt een werkblad(bv "Feed") met daarin 2 kolommen (bv A en . In kolom A komen de namen van de bronbestanden (ab001.xls ... ab100.xls), terwijl in kolom B komen de waarden uit de cel A1 van de respectievelijke bestanden (dus formules zijn "=[ab001.xls]Blad1!$A$1" ... "=[ab100.xls]Blad1!$A$1"). Deze formules kan je samenstellen in Excel en dan in de nodige cellen plakken.
Geef dit cellenbereik (A1-B100) bv de naam "Zoeklijst"

2/ In een ander werkblad (bij voorkeur zelfde bestand, dat vereenvoudigt een en ander) komen nu de eigenlijke opzoekingen. We gaan ervan uit dat dit werkblad "Opzoeken" heet.
We gaan ervan uit dat in "Opzoeken" in A1 de naam van het bestand staat en in A2 de opgezochte waarde moet komen.
In dat geval wordt de formule in A2:
=VERT.ZOEKEN(A1;Zoeklijst;2;ONWAAR)

Nadeel van deze oplossing: je zoeklijst moet je up-to-date zien te houden.
Voordeel: de oplossing doet wat ze moet doen

[Verwijderd]

edeboeck schreef op dinsdag 15 november 2005 @ 21:05:
[...]


Omdat je zegt dat je niets afkent van VBA, heb ik hier misschien een compleet andere oplossing voor jou:

1/ je maakt een werkblad(bv "Feed") met daarin 2 kolommen (bv A en . In kolom A komen de namen van de bronbestanden (ab001.xls ... ab100.xls), terwijl in kolom B komen de waarden uit de cel A1 van de respectievelijke bestanden (dus formules zijn "=[ab001.xls]Blad1!$A$1" ... "=[ab100.xls]Blad1!$A$1"). Deze formules kan je samenstellen in Excel en dan in de nodige cellen plakken.
Geef dit cellenbereik (A1-B100) bv de naam "Zoeklijst"

2/ In een ander werkblad (bij voorkeur zelfde bestand, dat vereenvoudigt een en ander) komen nu de eigenlijke opzoekingen. We gaan ervan uit dat dit werkblad "Opzoeken" heet.
We gaan ervan uit dat in "Opzoeken" in A1 de naam van het bestand staat en in A2 de opgezochte waarde moet komen.
In dat geval wordt de formule in A2:
=VERT.ZOEKEN(A1;Zoeklijst;2;ONWAAR)

Nadeel van deze oplossing: je zoeklijst moet je up-to-date zien te houden.
Voordeel: de oplossing doet wat ze moet doen

Ziet er goed uit..Echter moeten er meer dan 1 waardes worden opgehaald uit de bestanden ab001.xls - ab100.xls
Wat dus betekend dat er nog teveel vaste waardes in de zoeklijst staan.
Ik begrijp gewoon niet waarom het niet mogelijk is om in een formule een variabele waarde te zetten.

[Verwijderd]

onkl schreef op dinsdag 15 november 2005 @ 11:33:
Ik ben bang dat je toch naar VBA moet kijken. Als het Excel niet lukt de bestanden te openen zal er weinig anders opzitten.
Moeilijk is het niet.
Hier een opzetje, waarmee je wat kan prutsen.

Visual Basic:

Sub ophalen()
Dim anderwb As Workbook
Dim cel As Range
Set cel = ActiveCell
Dim fs As FileSearch
With fs
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "C:\" 'Deze aanpassen dus.
    .SearchSubFolders = False
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            Set anderwb = Workbooks.Open(.FoundFiles(i))
            cel.Value = anderwb.Worksheets(1).Range("A1").Value
            'ofwel: maak waarde van cel (hierboven gedefinieerd) gelijk aan
            'waarde van cel A1 in tabblad 1 van het gevonden bestand.
            cel.Offset(0, 1).Value = anderwb.Name
            'plak de naam van het bestand ernaast.
            Set cel = cel.Offset(1, 0)
            'verplaats "cel" 1 naar beneden.
        Next i
    Else
        MsgBox "There were no files found."
    End If

End With
End Sub

Type in excel alt-F11, maak in de het VBA project met de naam van je bestand (linksboven) een nieuwe module aan (rechtermuisknop) en plak deze code erin.
Zet vervolgens de directory goed en eventueel verander je het tabbladnummer en de "A1".
In Excel kan je nu (alt-F8) een macro "ophalen" uitvoeren.
Dan zal er, vanaf je actieve cel, een rijtje waardes en bestandsnamen gecreerd worden.
(of er crashed iets, maar dat horen we dan wel )

Hij crashte :-)
Hij geeft de melding "Objectvariabele of blokvariabele With is niet ingesteld"
En maakt dan met foutopsporing de volgende regel geel: .FileType = msoFileTypeExcelWorkbooks

[onkl]

Sorry, denkfoutje mijnerzijds.
Probeer deze eens?

Visual Basic:

Sub ophalen()
Dim anderwb As Workbook
Dim cel As Range
Set cel = ActiveCell
With Application.FileSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "C:\" 'Deze aanpassen dus.
    .SearchSubFolders = False
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            Set anderwb = Workbooks.Open(.FoundFiles(i))
            cel.Value = anderwb.Worksheets(1).Range("A1").Value
            'ofwel: maak waarde van cel (hierboven gedefinieerd) gelijk aan
            'waarde van cel A1 in tabblad 1 van het gevonden bestand.
            cel.Offset(0, 1).Value = anderwb.Name
            'plak de naam van het bestand ernaast.
            Set cel = cel.Offset(1, 0)
            'verplaats "cel" 1 naar beneden.
        Next i
    Else
        MsgBox "There were no files found."
    End If

End With
End Sub

[Verwijderd]

onkl schreef op woensdag 16 november 2005 @ 09:20:
Sorry, denkfoutje mijnerzijds.
Probeer deze eens?

Visual Basic:

Sub ophalen()
Dim anderwb As Workbook
Dim cel As Range
Set cel = ActiveCell
With Application.FileSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "C:\" 'Deze aanpassen dus.
    .SearchSubFolders = False
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            Set anderwb = Workbooks.Open(.FoundFiles(i))
            cel.Value = anderwb.Worksheets(1).Range("A1").Value
            'ofwel: maak waarde van cel (hierboven gedefinieerd) gelijk aan
            'waarde van cel A1 in tabblad 1 van het gevonden bestand.
            cel.Offset(0, 1).Value = anderwb.Name
            'plak de naam van het bestand ernaast.
            Set cel = cel.Offset(1, 0)
            'verplaats "cel" 1 naar beneden.
        Next i
    Else
        MsgBox "There were no files found."
    End If

End With
End Sub

Deze werkt wel, maar ik denk zelf niet dat dit de oplossing is.
Het zit namelijk zo. Ik heb 100 bestanden, ab001.xls t/m ab100.xls.
Ze hebben allemaal 150 regels in de A en B kolom.
Dan is er nog het bestand totaal. In dit bestand wil ik kunnen aangeven van welke abxxx bestanden hij de waardes moet ophalen.

[onkl]

Ik bben met je eens dat dit -vanuit het aspect van uitvoeringsgemak, je weet, zei je, weinig van VBA- niet optimaal is.
Echter, ik gok dat je er niet aan ontkomt VBA te gebruiken, niet specifiek dit brokje code, om dit probleem op te lossen, wanneer blijkt dat de INDIRECT formules van hierboven alleen werken als je werkboeken open zijn.
Wat ik me kan voorstellen, als oplossingsrichting is een wijziging in het stukje code van hierboven.
-Alleen bepaalde bestanden:
Je maakt een lijst van de bestanden ergens in je "totaal" bestand. MEt de hele bestandsnamen, dus "C:\Een map\ab1001.xls", niet "ab1001.xls".
in de For Next lus van het macro bouw je een if...-then statement in.
Daarbij gebruik je de functie VLOOKUP, die je ook in VBA kan gebruiken, zodat je iets krijgt als

Visual Basic:

IF NOT(application.worksheetfunctions.iserror(application.worksheetfunction.vlookup(.foundfiles(i).name,Verwijzing naar lijstje, 1, FALSE))) then
'hier de code die de data kopieert.
end if

De code

Visual Basic:

cel.Value = anderwb.Worksheets(1).Range("A1").Value

kan je heel makkelijk aanpassen.
Bijvoorbeeld:
Als je ervan maakt (het hele stukje tussen de for-next)

Visual Basic:

            Set anderwb = Workbooks.Open(.FoundFiles(i))
            anderwb.worksheets(1) .Range("A1:B150").Copy
            cel.Offset(1,0)PasteSpecial Operation:=xlPasteValues
            'plak kolom a en b in de kolom waar cel in staat, vanaf rij 2, en de kolom ernaast.
            cel.Value = anderwb.Name 
            'plak de naam van het bestand 
            Set cel = cel.Offset(0,2) 
            'verplaats "cel" 2 naar rechts.

En zo heb je de info uit alle werkbladen naast elkaar in je totaalblad.
Probeer alle methodes die ik aangaf eens uit te vogelen in de VBA help (da's niet de Excelhelp, gewoon in VBA of F1 drukken) en probeer een idee te hebben hoe je je data wilt binnenhalen (waar moet wat staan etc.), want het is allemaal goed te programmeren. (En de leercurve is er wel, maar die is voor VBA niet heel lang. Je zit even te prutsen, maar in no-time heb je iig. de basis te pakken.)

[Verwijderd]

onkl schreef op donderdag 17 november 2005 @ 09:41:
Ik bben met je eens dat dit -vanuit het aspect van uitvoeringsgemak, je weet, zei je, weinig van VBA- niet optimaal is.
Echter, ik gok dat je er niet aan ontkomt VBA te gebruiken, niet specifiek dit brokje code, om dit probleem op te lossen, wanneer blijkt dat de INDIRECT formules van hierboven alleen werken als je werkboeken open zijn.
Wat ik me kan voorstellen, als oplossingsrichting is een wijziging in het stukje code van hierboven.
-Alleen bepaalde bestanden:
Je maakt een lijst van de bestanden ergens in je "totaal" bestand. MEt de hele bestandsnamen, dus "C:\Een map\ab1001.xls", niet "ab1001.xls".
in de For Next lus van het macro bouw je een if...-then statement in.
Daarbij gebruik je de functie VLOOKUP, die je ook in VBA kan gebruiken, zodat je iets krijgt als

Visual Basic:

IF NOT(application.worksheetfunctions.iserror(application.worksheetfunction.vlookup(.foundfiles(i).name,Verwijzing naar lijstje, 1, FALSE))) then
'hier de code die de data kopieert.
end if

De code

Visual Basic:

cel.Value = anderwb.Worksheets(1).Range("A1").Value

kan je heel makkelijk aanpassen.
Bijvoorbeeld:
Als je ervan maakt (het hele stukje tussen de for-next)

Visual Basic:

            Set anderwb = Workbooks.Open(.FoundFiles(i))
            anderwb.worksheets(1) .Range("A1:B150").Copy
            cel.Offset(1,0)PasteSpecial Operation:=xlPasteValues
            'plak kolom a en b in de kolom waar cel in staat, vanaf rij 2, en de kolom ernaast.
            cel.Value = anderwb.Name 
            'plak de naam van het bestand 
            Set cel = cel.Offset(0,2) 
            'verplaats "cel" 2 naar rechts.

En zo heb je de info uit alle werkbladen naast elkaar in je totaalblad.
Probeer alle methodes die ik aangaf eens uit te vogelen in de VBA help (da's niet de Excelhelp, gewoon in VBA of F1 drukken) en probeer een idee te hebben hoe je je data wilt binnenhalen (waar moet wat staan etc.), want het is allemaal goed te programmeren. (En de leercurve is er wel, maar die is voor VBA niet heel lang. Je zit even te prutsen, maar in no-time heb je iig. de basis te pakken.)

Sorry maar ik kom er echt niet uit. Kun je de gehele code nogmaals plaatsen.
Nu ik bovenstaande stukken plaats in de oude code, geeft hij alleen nog maar fouten.

[onkl]

Visual Basic:

Sub ophalen()
Dim anderwb As Workbook
Dim ditwb As Workbook
Set ditwb = ActiveWorkbook
Dim cel As Range
Set cel = ActiveCell
Application.DisplayAlerts = False
With Application.FileSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "C:\" 'Deze aanpassen dus.
    .SearchSubFolders = False
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            If Not (ditwb.Worksheets("Blad2").Range("A:A").Find(.FoundFiles(i), LookIn:=xlValues) Is Nothing) Then
                Set anderwb = Workbooks.Open(.FoundFiles(i))
                anderwb.Worksheets(1).Range("A1:B150").Copy
                cel.Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                'plak kolom a en b in de kolom waar cel in staat, vanaf rij 2, en de kolom ernaast.
                cel.Value = anderwb.Name
                'plak de naam van het bestand
                Set cel = cel.Offset(0, 2)
                'verplaats "cel" 2 naar rechts.
                anderwb.Saved = True
                anderwb.Close
                Application.CutCopyMode = 0
            End If
        Next i
    Else
        MsgBox "There were no files found."
    End If

End With
Application.DisplayAlerts = True
End Sub

Zet in kolom A van tabblad "Blad2" (of hernoem in regel 16) een lijstje "te importeren" bestanden. Met pad (C:\hoi.xls of zo)
Klik op cel A1 van het werkblad waar je je resultaten wilt hebben. (Niet blad2 )
Run ophalen. Blad 1, cel A1-B150 van alle gevonden bestanden die in de lijst staan worden gekopieerd naar de actieve cel, die steeds verder naar rechts gaat.
Bij mij issie blij, maar ik hoor het wel.

(Er zaten in mijn eerdere aanvullingen idd wat slordigheden, was een beetje uit het blote hoofd getypet.)

[ Voor 8% gewijzigd door onkl op 21-11-2005 15:27 ]