Verwijzing excel automatisch aanpassen

[88Gamer88]

Hallo allemaal,

Ik zit met het volgende probleem.

Ik wil met excel wat boekhouding doen en uitgaven / inkomsten monitoren.
(Heb een boekhouder, maar wil globaal ook inzicht hebben hoe het er voor staat)

Heb sinds kort Office 2016 aangeschaft en wil me hier wat meer in gaan verdiepen.
Hopelijk kan ik duidelijk maken wat de bedoeling is.

Wil een excel bestand maken die automatisch de gegevens haalt uit de gemaakte facturen.

De facturen staan in een map, elk kwartaal gaat een andere map in
Zoals:

Werk map\Verkoop Facturen\2019\1e Kwartaal
Werk map\Verkoop Facturen\2019\2e Kwartaal

Hier van was de bedoeling dat wanneer het excel bestand geopend wordt hij automatisch de nieuwe (gegevens) van de facturen inlaadt.

In deze facturen staat bijvoorbeeld op (J39) het totaal bedrag wat ik factureer.
Op (G6) het de klant naam enzo.

Nu wil ik als volgt een excel bestand maken (in andere map) als "kosten overzicht / totaaloverzicht"

Nu moet ik zelf iedere keer handmatig de factuur nummer + bedrag invullen in het overzicht (Fout gevoelig)
Of per factuur handmatig een hyperlink kopiëren vanuit cel (J39) naar mijn "kosten overzicht / totaaloverzicht"

Ik vroeg me af of er een manier is om dit te vergemakkelijken / te automatiseren ?
Ben zelf al bezig geweest met een soort layout en een VBA script proberen toevoegen maar zonder succes (ook geen verstand van)

EDIT: De VBA code die ik tegen kwam op internet en heb aangepast

Attribute VB_Name = "Uploadfactuur"
Sub Uploadfactuur()
Dim sv, hs, j As Long, y As Long
sv = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\[COLOR="#0000FF"]Naam[/COLOR]\Desktop\Werk map\Verkoop Facturen\2019\1e Kwartaal\*.xls*"" /b /s").StdOut.ReadAll, vbCrLf)
hs = Sheets("Blad1").Cells(1).CurrentRegion


ReDim arr(UBound(sv) - 1, 2)
 If Not IsEmpty(hs) Then
    y = UBound(hs) - 1
 Else
   y = 0
End If
  For j = y To UBound(sv) - 1


    With Workbooks.Open(sv(j))
       arr(j - y, 1) = Split(sv(j), "\")(UBound(Split(sv(j), "\")))
       arr(j - y, 2) = .Sheets(1).[E6]
       arr(j - y, 0) = .Sheets(1).[G16]
     .Close 0
    End With
  Next j
Sheets("totaaloverzicht").Cells(Rows.Count, 4).End(xlUp).Offset(6).Resize(UBound(arr) + 1, 3) = arr
End Sub

Hopelijk wilt er iemand mij helpen
Alvast bedankt.

 

[VenA]

Waarom schijf je niet gelijk de gegevens weg bij het opslaan van de factuur? Waarom gebruik je per kwartaal een aparte map en een aparte tab? De code aanpassen zal het probleem niet zijn maar jij zit dan nog steeds met een slecht gestructureerde opzet.

 

[JanBG]

Waarom schijf je niet gelijk de gegevens weg bij het opslaan van de factuur?

Ben het helemaal eens met VenA. Heb je bestand her en der wat aangepast; misschien heb je er wat aan.

 

[88Gamer88]

Waarom schijf je niet gelijk de gegevens weg bij het opslaan van de factuur? Waarom gebruik je per kwartaal een aparte map en een aparte tab? De code aanpassen zal het probleem niet zijn maar jij zit dan nog steeds met een slecht gestructureerde opzet.

Omdat ik als het een grote opdracht is ik de factuur aanmaak en deze continue aanvult tot dat het klaar.

Dat een map heb per kwartaal vond ik zelf overzichtelijk en duidelijk.

Ben het helemaal eens met VenA. Heb je bestand her en der wat aangepast; misschien heb je er wat aan.

Dit vergelijkbaars had in het begin ook, alleen vond zelf niet fijn werken.
Zo doend ben naar losse bestanden gegaan.

 

[jkpieterse]

Als je gebruik maakt van Gegevens, Nieuwe Query, Uit Bestand, UIt map EN je houdt de indeling van je facturen hetzelfde, dan kan je via die route alle data laten ophalen. Je moet dan wel de nodige acties doen voordat je er ene nette tabel uit krijgt, maar eenmaal gedaan hoef je daarna slechts "Alles vernieuwen" te klikken als er nieuwe facturen zijn. Simpeler te doen dan VBA denk ik.

 

[88Gamer88]

Dat snap ik, is ook heel mooi in elkaar gezet alleen niet voor mij op de manier hoe ik werk en gewend ben te werken.


EDIT:

='C:\Users\Naam\Desktop\Werk map\Verkoop Facturen\2019\1e Kwartaal\[FA.0001.xlsx]Blad1'!$E$6

Als er een manier is om de externe verwijzingen automatisch te laten ophalen ben ik denk ook al geholpen.

Dus als het boven in het overzicht invul en soort van "naar beneden kan trekken" en het automatisch door telt.

EDIT:

Ben tot het volgende gekomen:

=ALS.FOUT(INDIRECT("'C:\Users\Naam\Desktop\Werk map\Verkoop Facturen\2019\1e Kwartaal\["&A6&".xlsx]Blad1'!$E$6);"")

Alleen werkt dit niet.

Invak A6 staat het bestandsnaam.
Vanaf dat bestand moet er info gehaald worden vanaf E6.
Alleen de cel blijft leeg, hopelijk iemand een idee ?

 

[jkpieterse]

Hoe je "gewend bent te werken" zou geen excuus moeten zijn om een betere manier van werken niet te omarmen lijkt mij. Ik kan je warm aanbevelen eens goed naar dat "Ophalen en transformeren" te kijken, het is buitengewoon krachtig en helemaal niet zo lastig te gebruiken.

 

[88Gamer88]

Geeft geen antwoord op de vraag.

 

[jkpieterse]

Ik probeer altijd net even iets verder te kijken dan de letterlijke vraag. Soms is nou eenmaal ander gereedschap (veel) beter. Misschien niet in dit geval, maar ik ben het in ieder geval al eens met VenA, een andere struktuur zou hier al aardig kunnen helpen.

 

[E v R]

Als antwoord op de vraag uit post#6: Indirect werkt niet met gesloten bestanden
Gebruik Als.fout niet (levert meer schade op, nu dus eigenlijk ook weer)
Neem post#9 echt ter harte

 

[88Gamer88]

@ E v R,

Ook als het bestand geopend is wilt het niet werken.

Heb de eerdere manier geprobeerd.
Alleen werkt dit niet met de rest.

Staat meer programma's gekoppeld met Excel.
Zo als offerte systeem en autocad.

Om dit om te gooien heb niet echt trek ik.

 

[Hans123]

Kijk eens naar de functie adres.

formule wordt dan iets van =indirect(adres(x;x;x;x;x)) waarbij bij de x'en de parameters ingevuld moeten worden. Excel geeft wel aan wat er verwacht wordt.
Als het bestand open is waar naar verwezen wordt zal dit werken.