Gegevens automatisch updaten van een andere Excel bestand

shalhevet · 2 sep 2013

Hallo,

In mijn administratie bestand heb ik een werkblad "Debiteuren". Daar zet ik alle naw gegevens van de debiteur bij.

In mijn factuur bestand heb ik dezelfde "Debiteuren" werkblad.

Nu zou ik willen dat wanneer ik het bestand "Factuur" open, zal de werkblad "Debiteuren" automatisch geupadate worden met de gegevens van de "Debiteuren" werkblad in het bestand "Administratie".

Het liefst zonder dat het bestand "Administratie" open gaat. Als dat niet mogelijk is, dan zou ik willen dat het bestand "Administratie" automatisch gesloten zal worden na het updaten.

Op deze manier hoef ik maar 1 keer de naw gegevens in te vullen.

Ik heb op het net de volgende code gevonden:

Code:

Private Sub Workbook_Open() 
    Range("Used,Restocked").ClearContents 
    Range("StartingCount") = Range("EndingCount") 
End Sub

Ik begrijp dat deze in het bestand "Factuur" moet komen te staan. Tijdens het openen van het bestand zullen alle gegevens van de "Debiteuren" werkblad gewist worden en vervolgens ingevuld worden met de gegevens uit het andere bestand.

Maar ik kan niet vinden hoe moet ik naar het andere bestand verwijzen en hoe kan ik ervoor zorgen dat het bestand of niet open gaat voor het updaten of automatisch afgesloten wordt na het updaten.

Wellicht iemand hier met meer kennis dan ik het antwoord weet....

Alvast bedankt

shalhevet · 2 sep 2013

Ik heb een macro opgenomen met alle stappen die nodig zijn om mijn doel te bereiken en vervolgens deze onder de open workbook event gezet.

Het werkt prima maar nu heb ik een tweede probleem.

In mijn Administratie bestand maak ik gebruik van een Hide_Unhide code die alle regels verbergt indien deze leeg zijn.

Bij het overnemen van de gegevens vanuit de factuurbestand wordt de code van de Hide_Unhide niet in gang gezet en worden de verborgen regels (die wel ingevuld worden door gegevens van de bron bestand (nl. Administratie) opgenomen.

Iemand een idee hoe kan ik dat oplossen?

Bekijk bijlage Administratie.xls

Bekijk bijlage Factuur.xlsm

spaarie · 3 sep 2013

Shalhevet,

Waarom breng je het niet onder in 1 bestand?
- Voorblad met eventueel menu'tje ofzo
- Blad met relaties, daarop specificeer je debiteur of crediteur of 2 aparte bladen hiervoor
- Factuur
- Debiteurenbewaking (deze kan ook nog samen met crediteurenbewaking)
- Crediteurenbewaking
- en eventueel nog meerdere

Zo heb je 1 bestand met NAW gegevens van je relaties, je hoeft niet onnodig bestanden te openen en te sluiten en wanneer je layout veranderd hoef je dit niet in alle bestanden aan te passen.

Ik zou er echt over nadenken om het over een andere boeg te gooien, want al deze handelingen zijn niet nodig...

shalhevet · 3 sep 2013

Spaarie,

Ik durf het niet. Mij is geleerd dat Excel bestand die te groot is loopt veel risico's om vast te lopen of beschadigd te raken.

In mijn administratie bestand heb ik al meerdere sheets:

Debiteuren - NAW
Crediteuren - NAW
Inkoopboek
Verkoopboek
Bankboek
Urenregistratie - totaal overzicht
Debiteurenbewaking
Crediteurenbewaking

In ieder sheet zijn tussen de 500 tot 1000 regels.

Ik vrees dat hoe meer sheets ik aan zou toevoegen hoe groter het bestand zal worden met alle risico's.

spaarie · 3 sep 2013

Excel bestand die te groot is

Een ieder heeft zijn eigen definitie van een groot bestand. Ik vind een bestand van >5mB redelijk groot, waar andere dit té groot vinden en andere juist klein.
Neem Excel2013, de 64 bit versie heeft geen eens een maximum aan bestandsgrootte. Dat wil zeggen dat een bestand net zo groot kan zijn als dat je computer aan geheugen heeft...

Mij is geleerd dat Excel bestand die te groot is loopt veel risico's om vast te lopen of beschadigd te raken

Weet niet wie jou dit geleerd heeft, maar ik denk dat een bestand van 100kB net zoveel kans heeft om vast te lopen als een bestand van 10mB. Het ligt geheel aan de bewerkingen die je doet en 500 tot 1000 regels is niet echt een uitdaging voor Excel.

Ik vrees dat hoe meer sheets ik aan zou toevoegen hoe groter het bestand zal worden met alle risico's

Dit is 1 van de redenen waarom bedrijven elke dag back-ups maken. Jouw vrees is onder te vangen door dagelijks een back-up te maken. Dit is ook te automatiseren.

shalhevet · 3 sep 2013

Spaarie,

Er zit wel een kern van waarheid in wat je zegt... misschien denk ik idd ook te moeilijk (aard van het beest

)

Ik zal de factuur sheet toevoegen aan mijn administratie bestand.... nog 1 sheet erbij al het echt niet meer of minder doen (hoop ik)...

nu maar hopen dat ik met alle macro's uit kom die in de factuurbestand staan :shocked:

spaarie · 3 sep 2013

Zal wel goed komen denk ik, je hebt altijd nog HelpMij in je favorieten staan

Tevens nog een tip: Als je Excel2007 of hoger gebruikt, sla de bestanden dan ook op in deze format (.xlsx of .xlsm) deze zijn op XML basis. Excel2003 of lager bestand (.xls) worden anders opgeslagen...

shalhevet · 3 sep 2013

je hebt altijd nog HelpMij in je favorieten staan

Favorieten??? HelpMij is inmiddels mijn start pagina geworden

Ja, ik zat net te lezen over de verschillen van de XLS en XLSM bestanden.

Bedankt tot zover..... heb trouwens nog steeds een probleem met de inkoopboek.... als je even tijd hebt een dezer dagen om naar te kijken zal het erg fijn zijn

http://www.helpmij.nl/forum/showthr...-crediteurenbeaking-werkt-niet-zoals-het-moet

spaarie · 3 sep 2013

Wil je het gehele bestand eens plaatsen? Natuurlijk ontdaan van gevoelige info.
Mocht dit niet passen qua grootte kan je hem eventueel uploaden naar www.mijnbestand.nl en de link hier plaatsen.

Misschien dat ik nog wat voor je kan betekenen met een opzetje...

shalhevet · 3 sep 2013

Wat ben je toch een lieverd :love:

Het volledig bestand is geupload naar mijnbestand.nl

http://www.mijnbestand.nl/Bestand-73ZW4MBADZPK.rar

Heb de factuur nog niet verplaatst vandaar dat ook factuurbestand bij zit.

Super bedankt!!!!

spaarie · 4 sep 2013

Ik heb hier en daar wat ingevuld.
Alleen je urenregistratie ontbreekt. Deze moet je zelf even toevoegen.

Ik hoor het wel over enige toevoegingen en/of vragen...

shalhevet · 4 sep 2013

Alle eerst wil ik je bedanken voor alle moeite!! Dat is niet vanzelfsprekend en wordt hier enorm gewaardeerd.

Bestand ziet er erg mooi uit!!!

Ben er super blij mee

Ik moet nog in het bestand verdiepen en even mee werken om mogelijke problemen te signaleren.

Wat ik zo snel kon zien is het volgende:

Je hebt crediteuren en debiteuren vervangen door "RELATIES". hoe maak je dan de scheiding welke is een klant en welke is een leverancier?

In mijn inkoopboek waren formules ingezet in diverse kolommen maar deze zijn nu weg... is het bewust gedaan?

In mijn factuur bestand had ik een keuze lijst waaruit ik de klanten kon kiezen - adresgegevens en klantnummer werden dan automatisch ingevuld.

Nu heb je het omgedraaid, klantnummer moet ik handmatig invullen en klantgegevens worden ingevuld. Vind ik niet handig want dan moet ik onthouden welke klant welke nummer heeft of moet ik alsnog een extra handeling doen en zoeken in de relaties blad.

Zou je dit terug kunnen zetten? als ik het nou zelf moet doen ben ik bang dat ik alle macro's weer in de war gooi en kom ik niet meer uit

En ik krijg een foutmelding bij het printen van een factuur in de regel:

Code:

    Workbooks.Open ThisWorkbook.Path & "\Urenregistratie" & kw & " e kwartaal 2014.xlsm"

Heb deze aangepast naar de naam van de urenregistratie bestanden, heb de bestanden in dezelfde map gezet maar toch doet ie het niet. ik kan niet zo snel vinden wat het probleem is.

spaarie · 4 sep 2013

Origineel had ik 2 kolommen erin gezet met een D en C, maar voor het bestand zelf maakt het niet uit of het een debiteur of crediteur is. Is dit een persoonlijke voorkeur dan kan je deze optie toepassen natuurlijk.

De formules heb ik niet bewust weggelaten. Meerdere zijn verloren gegaan door de bladen te kopieren en te plakken als waarde, maar door al die verschillende bestanden die je op HelpMij hebt gepost kon ik de originele niet terug vinden.

De gegevensvalidatie zal ik terug zetten.

De foutmelding komt denk ik door een '\' die er niet meer staat: "\Urenregistratie\"

shalhevet · 4 sep 2013

Origineel had ik 2 kolommen erin gezet met een D en C, maar voor het bestand zelf maakt het niet uit of het een debiteur of crediteur is. Is dit een persoonlijke voorkeur dan kan je deze optie toepassen natuurlijk.

Is idd persoonlijke voorkeur. Heb ik iets meer overzicht over de aantal klanten en werkt makkelijker met het doornummeren. Belastingdienst wilt tenslotte klantnummers die aaneen aangesloten zijn. Dat zal ik dus nog aanpassen.

De formules heb ik niet bewust weggelaten. Meerdere zijn verloren gegaan door de bladen te kopieren en te plakken als waarde, maar door al die verschillende bestanden die je op HelpMij hebt gepost kon ik de originele niet terug vinden.[/

]

Begrijp ik, is zelfs voor mij soms even zoeken welke bestand heb ik nu nodig omdat ik elke keer weer een andere voorbeeld bestand had geupload. Is niet erg, de origineel heb ik nog en kan ik de formules zelf weer terug zetten.

De gegevensvalidatie zal ik terug zetten.

Klik om te vergroten...

Erg fijn, dank je wel.

De foutmelding komt denk ik door een '\' die er niet meer staat: "\Urenregistratie\"

Klik om te vergroten...

Zal straks als ik iets meer tijd heb goed naar kijken.

Tot zover hartelijk dank voor alle tijd en moeite

Klik om te vergroten...

spaarie · 4 sep 2013

Factuur aangepast met combobox, deze zie je niet wanneer je afdrukt...

shalhevet · 5 sep 2013

Spaarie,

Ik heb een paar wijzigingen gedaan om het bestand aan te passen.

Omdat in de echte factuur ruimte moet blijven voor logo en bedrijfsgegevens heb ik de "range" of de cellen veranderd. Tevens vond ik het fijner om toch met een tabblad debiteuren en een tabblad crediteuren te werken.

Zie hieronder de origineel code (van jouw) en de aanpassingen die ik gedaan heb.

Origineel:

Code:

    wk = [COLOR="#FF0000"]Range("H5")[/COLOR].Value2
    facnr = [COLOR="#FF0000"]Range("H6")[/COLOR].Value
    debnr = [COLOR="#FF0000"]Range("H7")[/COLOR].Value
    pad = ThisWorkbook.Path & "\Facturen\_" & facnr & ".pdf"

    Select Case nr
        Case 1 To 13: kw = 1
        Case 14 To 26: kw = 2
        Case 27 To 39: kw = 3
        Case 40 To 53: kw = 4
    End Select
    Workbooks.Open ThisWorkbook.Path & "\[COLOR="#FF0000"]Uren registratie[/COLOR] " & kw & "[COLOR="#FF0000"]e kwartaal.xlsm[/COLOR]"
    With ActiveWorkbook.Sheets("Week" & wk)
        .Range("B19") = facnr
        .Range("B19").Hyperlinks.Add .Range("B19"), ThisWorkbook.Path & "\Factuur\_" & facnr & ".pdf"
    End With
    ActiveWorkbook.Close True
    ThisWorkbook.ActiveSheet.Copy
    ActiveSheet.ExportAsFixedFormat xlTypePDF, pad, , True, , , , True
    ActiveWorkbook.Close False
    ThisWorkbook.Save
    
    With Sheets("VERKOOPBOEK")
        rij1 = .Range("A" & Rows.Count).End(xlUp).Offset(1)
        rij2 = Sheets("[COLOR="#FF0000"]RELATIES[/COLOR]").Columns(1).Find(debnr).Row
        .Cells(rij, 1) = facnr
        .Cells(rij, 2) = debnr
        .Cells(rij, 3) = Sheets("[COLOR="#FF0000"]RELATIES[/COLOR]").Cells(rij2, 3)
        .Cells(rij, 4) = Range("D20").Value
        'etc
    End With
    
    facnr = facnr + 1
End Sub

Aangepaste code:

Code:

Private Sub CommandButton1_Click()
    wk = [COLOR="#FF0000"]Range("F13")[/COLOR].Value2
    facnr = [COLOR="#FF0000"]Range("F14")[/COLOR].Value
    debnr = [COLOR="#FF0000"]Range("F15")[/COLOR].Value
    
    pad = ThisWorkbook.Path & "\Verkoop facturen\_" & ActiveSheet.Range("F14").Value & ".pdf"
    nr = Sheets("factuur").Range("F13").Value2
    Select Case nr
        Case 1 To 13: kw = 1
        Case 14 To 26: kw = 2
        Case 27 To 39: kw = 3
        Case 40 To 53: kw = 4
    End Select
    Workbooks.Open ThisWorkbook.Path & "\[COLOR="#FF0000"]Urenregistratie[/COLOR] " & kw & "[COLOR="#FF0000"]e kwartaal 2013.xlsm[/COLOR]"
    With ActiveWorkbook.Sheets("Week" & nr)
        .Range("B19") = ThisWorkbook.Sheets("Factuur").Range("F14")
        .Range("B19").Hyperlinks.Add .Range("B19"), ThisWorkbook.Path & "\Factuur\_" & .Range("B19") & ".pdf"
        .Range("B19").Copy ActiveWorkbook.Sheets("Index").Cells(nr + 2, 3)
    End With
    ActiveWorkbook.Close True
    ThisWorkbook.ActiveSheet.Copy
    ActiveSheet.ExportAsFixedFormat xlTypePDF, pad, , True, , , , True
    ActiveWorkbook.Close False
    ThisWorkbook.Save
    
    With Sheets("VERKOOPBOEK")
        rij1 = .Range("A" & Rows.Count).End(xlUp).Offset(1)
        rij2 = Sheets("Debiteuren").Columns(1).Find(debnr).Row
        .Cells(rij, 1) = facnr
        .Cells(rij, 2) = debnr
        .Cells(rij, 3) = Sheets("Debiteuren").Cells(rij2, 3)
        .Cells(rij, 4) = Range("D20").Value
        'etc
    End With
    
    facnr = facnr + 1
End Sub

Nu krijg ik bij de laatste stukje code een foutmelding:

Objectvariabele of blokvariabele With is niet ingesteld

Code:

    With Sheets("VERKOOPBOEK")
        rij1 = .Range("A" & Rows.Count).End(xlUp).Offset(1)
        [COLOR="#FF0000"]rij2 = Sheets("Debiteuren").Columns(1).Find(debnr).Row[/COLOR]
        .Cells(rij, 1) = facnr
        .Cells(rij, 2) = debnr
        .Cells(rij, 3) = Sheets("Debiteuren").Cells(rij2, 3)
        .Cells(rij, 4) = Range("D20").Value
        'etc
    End With
    
    facnr = facnr + 1
End Sub

Waar gaat het mis?

spaarie · 6 sep 2013

Misschien:

Code:

rij2 = Sheets("Debiteuren").range("A2:A" & sheets("Debiteuren").range("A" & rows.count).end(xlup).row).Find(debnr).Row

snb · 6 sep 2013

rij2 = Sheets("Debiteuren").Columns(1).Find(debnr).Row

'debnr' is niet gevonden.

shalhevet · 6 sep 2013

Spaarie,

De nw. code doet het ook niet. Ik heb even getest met de origineel bestand die ik van jou had gekregen, zonder niks te veranderen krijg ik daar ook een fout melding bij de volgende regel:

Code:

.Cells(rij, 1) = facnr

Is er misschien een typo in de code die ik niet kan vinden?

Dus in jouw bestand en in de mijne blijft het bij de laatste stuk code hangen. Ik kan het probleem gewoon niet vinden, heb al zoveel geprobeerd te veranderen maar kom in zo'n cirkel terecht - krijg continu weer andere fout meldingen wegens mijn aanpassingen, ga ik weer naar het begin en probeer ik opnieuw... zonder succes tot nu toe

---------**********-----------

SNB,

Idd, debnr is niet gevonden maar waar ligt het aan? want er is wel debnr ingevoerd in kolom B van de debiteurenbestand

snb · 6 sep 2013

Vergelijk de inhoud van de variabele eens met de inhoud van de cel: beide getallen of beide tekst ? ergens een spatie ? of een verborgen teken in de cel ? of een apostrof ? Kijk naar de inhoud van de cel, niet naar de weergave.

Gegevens automatisch updaten van een andere Excel bestand

Gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Bijlagen

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Bijlagen

Gebruiker

Terugkerende gebruiker

Verenigingslid

Gebruiker

Verenigingslid

Wij waarderen jouw privacy