[Excel 2003] 2 sheets extern koppelen

[Pazzi]

Op mijn werk maak ik gebruik van een Excelsheet om op gezette tijden de voortgang van het werk te controleren. Ik vul wat gegevens in en Excel vergelijkt deze met de planning en geeft vervolgens het (negatieve of positieve) verschil aan.

Nu wil ik echter deze resultaten ook weergeven aan mijn teamleden, het liefst realtime. Dus op het moment dat ik de gegevens heb ingevoerd, moet op een ander scherm het resultaat worden weergegeven. Dit scherm zal ergens op de werkvloer komen te hangen, buiten mijn zicht.

Voorlopig heb ik het grotendeels opgelost, echter loop ik tegen 1 probleem aan:

Ik heb een 2e Excelsheet gemaakt, genaamd "Output". Deze bevat koppelingen naar mijn eigen sheet. Wanneer ik beide sheets in dezelfde Excelsessie open, worden de cellen in "Output" keurig geüpdate zodra ik in mijn sheet iets invul. So far, so good.

Om de "Output"sheet zichtbaar te maken voor het team, heb ik een 2e beeldscherm aangesloten. Ik heb het bureaublad over beide beeldschermen gespreid en sleep, vanaf mijn bureau, de "Output"sheet naar het 2e scherm. Hierbij loop ik echter tegen 2 problemen aan:

1 ) Excel zelf is ook verspreid over beide schermen. Wanneer ik een 3e sheet open (wat regelmatig gebeurd) kan dit deels op het 2e beeldscherm verschijnen. Dit wil ik voorkomen.
2 ) De layout wordt niet onthouden. Ik moet iedere dag beide sheets opnieuw "positioneren", dus meerdere malen tussen beide schermen heen en weer lopen. Dit is alles behalve praktisch.

Ik heb geprobeerd dit op te lossen door 2 sessies van Excel te openen. De ene gemaximaliseerd op het beeldscherm op de vloer, de ander gemaximaliseerd op het beeldscherm op mijn bureau. Dit lost bovenstaande problemen op, maar helaas worden de gegevens in de "Output" sheet niet meer automatisch geüpdate.

Wat mijn ideale oplossing zou zijn is dat ik vanuit mijn Excelsheet een opdracht kan geven dat de gegevens in de "Output"sheet moeten worden geüpdate. Ik heb al zitten kijken naar het koppelen van sheets (wat ik in principe al gedaan heb) en naar celverbindingen, maar ik kom er niet uit. Ook de functie "Gegevens vernieuwen" doet niets.

Ik maak gebruik van Office 2003 op een Windows 7 machine. Hier valt niets aan te veranderen. Ik vrees dat ik met VBA aan de gang moet, maar daar heb ik 0,0 kaas van gegeten.

Hopelijk heb ik het verhaal duidelijk genoeg uitgelegd. Iemand met een briljante oplossing / ingeving?

[Icephase]

Macro'tje opnemen om de gegevens op de 2e sheet bij te werken en die macro koppelen aan een object (zo'n mooie Windows-knop of iets dergelijks) op je 1e sheet?
Hoe je een macro opneemt staat uitgelegd in de Office-help functie, daar staat het duidelijker dan dat ik hier kan vertellen.

[Pazzi]

Macro's had ik nog niet aan gedacht. Ik hoop dat ik binnenkort tijd kan maken om me daar eens in te verdiepen. Bedankt voor het idee.

[Martijnvc]

Lost Ctrl + Alt + F9 (Force recalculate) terwijl je tweede sheet actief is dit probleem niet op?

[Pazzi]

@Martijnvc: helaas. Zowel de toetsencombinatie als het selecteren van de menukeuze helpen niet. Er gebeurd simpelweg niets.

@Woelders: Ook met een macro lukt het niet. Zover ik kan zien is er geen mogelijkheid om de externe gegevens handmatig te updaten. Ik heb geprobeerd een macro te maken die de huidige sheet sluit en weer opent (want tijdens het openen worden de gegevens wel geüpdate), maar dat werkt ook niet.

[Martijnvc]

Pazzi,

het moet kunnen. Met een beetje moeite heb ik jouw situatie kunnen reproduceren. Enige verschil is dat ik nu niet de file dwing te updaten (dat werkt inderdaad niet met twee instances) maar de gegevens (input dan wel output) kopieer van de ene instance naar de andere instance file.

Voor code zie hieronder. Aangezien ik beperkte kennis heb, is de code als volgt opgebouwd: eerst output file openen vanuit de input file (zodat je nl. kunt refereren aan de Excel instance). Vervolgens een tweede macro runnen voor het updaten.

Ik hoop dat je er verder mee komt...succes!

Visual Basic:

Global ObjApp1 As Excel.Application
Global ObjApp2 As Excel.Application

Sub OpenFiles()
'Define ObjApp1 as your input file
Set ObjApp1 = GetObject(, "Excel.Application")
'Create a new Excel instance, to show the output file on your secondary screen
Set ObjApp2 = CreateObject("Excel.Application")
'Make the instance visible and open the output file
ObjApp2.Visible = True
ObjApp2.Workbooks.Open Filename:="C:\Users\...\Book2.xlsx"
End Sub

Sub UpdateOutputFile()
Dim iws As Worksheet
Dim ows As Worksheet
Set iws = ObjApp2.ActiveWorkbook.Worksheets("Sheet1")
Set ows = ObjApp1.ActiveWorkbook.Worksheets("Sheet1")
iws.Range("C8:D10").Value = _
ows.Range("C3:D5").Value
End Sub

[Pazzi]

Goed, we zijn een tijdje verder maar het is me vandaag eindelijk gelukt.

Het blijkt dat cellen niet via "Update links" geüpdate kunnen worden wanneer de sheet voorzien is van een beveiliging. Vreemd, gezien het feit dat de cellen wel worden geüpdate wanneer de sheet voor het eerst opgestart wordt.

Dit loste dus één deel van het probleem op. Het andere probleem heb ik kunnen oplossen met macro's. Ik heb in mijn sheet een macro opgenomen die automatisch iedere 5 minuten alles opslaat. Dit is gelukt door de opgenomen macro in VBA te editen en te voorzien van de Application.OnTime functie.

De "output" sheet heeft een macro die ook iedere 5 minuten de cellen update via de optie "Update links". Door de macro's Auto_Open als naam te geven, worden ze automatisch gestart. Ik hoef slechts de snelkoppeling te openen om de sheet te openen. In het slechtste geval zit er dus 10 minuten tussen het updaten, maar voorlopig is dat genoeg. Ik kan altijd de timer aanpassen.

@Martijnvc: bedankt voor je reply. Je hebt er werk in gestoken en dat waardeer ik Ik begin me een beetje te verdiepen in VBA (er is een snowball-effect aan ideeën ontstaan), wellicht dat ik jou code nog eens aan het werk ga zetten als ik er wat meer in thuis ben. Thanks!