Macro om gegevens uit een grafiek in Excel te extraheren

Samenvatting

In Microsoft Excel kunt u gegevens uit een grafiek ophalen, zelfs wanneer de gegevens zich in een extern werkblad of een externe werkmap bevinden. Dit is handig in situaties waarin de grafiek is gemaakt van of gekoppeld is aan een ander bestand dat niet beschikbaar is of op een of andere manier is beschadigd. Wanneer de brongegevens van een grafiek verloren gaan, kunnen de gegevens nog steeds worden opgehaald uit de grafiek zelf, met behulp van een Microsoft Visual Basic for Applications-macro.

Meer informatie

Microsoft verstrekt deze code zonder enige expliciete of impliciete garantie, daaronder mede begrepen, maar niet beperkt tot impliciete garanties met betrekking tot de verkoopbaarheid en/of geschiktheid voor een bepaald doel. In dit artikel wordt ervan uitgegaan dat u bekend bent met de programmeertaal VBScript, alsmede met de hulpprogramma's waarmee procedures worden gemaakt en waarmee fouten in procedures worden opgespoord. U kunt desgewenst contact opnemen met Microsoft Product Support Services voor uitleg over de functie van een bepaalde procedure. Microsoft Product Support Services is echter niet bereid de voorbeelden aan te passen om extra functies toe te voegen of om procedures te maken die aan uw specifieke eisen voldoen.

Met de volgende voorbeeldmacro worden de brongegevens van de grafiek in een werkblad met de naam ChartData in de actieve werkmap geplaatst, te beginnen in de eerste kolom en eerste rij.

1. Voer de volgende macrocode in een moduleblad in:

   Sub GetChartValues()
      Dim NumberOfRows As Integer
      Dim X As Object
      Counter = 2
   
      ' Calculate the number of rows of data.
      NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)
   
      Worksheets("ChartData").Cells(1, 1) = "X Values"
   
      ' Write x-axis values to worksheet.
      With Worksheets("ChartData")
         .Range(.Cells(2, 1), _
         .Cells(NumberOfRows + 1, 1)) = _
         Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
      End With
   
      ' Loop through all series in the chart and write their values to
      ' the worksheet.
      For Each X In ActiveChart.SeriesCollection
         Worksheets("ChartData").Cells(1, Counter) = X.Name
   
         With Worksheets("ChartData")
            .Range(.Cells(2, Counter), _
            .Cells(NumberOfRows + 1, Counter)) = _
            Application.Transpose(X.Values)
         End With
   
         Counter = Counter + 1
      Next
   
   End Sub
   

2. Voeg een nieuw werkblad in uw werkmap in en wijzig de naam ervan in 'ChartData' (zonder de aanhalingstekens).

3. Selecteer de grafiek waaruit u de onderliggende gegevenswaarden wilt extraheren.

   Opmerking

   De grafiek kan worden ingesloten op een werkblad of op een afzonderlijk grafiekblad.

4. Voer de macro GetChartValues uit.

   De gegevens uit de grafiek worden in het werkblad Grafiekgegevens geplaatst.

Stappen voor het koppelen van de grafiek aan de herstelde gegevens

Als u wilt dat de grafiek interactief is met de herstelde gegevens, moet u de grafiek koppelen aan het nieuwe gegevensblad in plaats van de koppelingen naar de ontbrekende of beschadigde werkmap te behouden.

1. Selecteer de grafiek en klik op een reeks om de naam van het blad te vinden waaraan de grafiek is gekoppeld in de beschadigde of ontbrekende werkmap. De bladnaam wordt weergegeven in de reeksformule op de formulebalk.

   Opmerking

   De naam van het blad kan de naam van de werkmap volgen, die tussen vierkante haken staat, zoals '[Boek1]', en voorafgaat aan het uitroepteken '!' (of apostrof en uitroepteken "'!) waarmee het begin van een celverwijzing wordt aangegeven. De naam van het blad bevat alleen de tekens tussen het vierkante haakje en het uitroepteken (of apostrof en uitroepteken). Laat apostrof weg als deze direct vóór het uitroepteken staat, omdat een apostrof niet het laatste teken in een bladnaam kan zijn.

2. Dubbelklik op het tabblad van het nieuwe blad met de naam ChartData.

3. Typ de oorspronkelijke bladnaam uit stap 1 op de gemarkeerde Grafiekgegevens en druk op Enter. Deze naam moet gelijk zijn aan de bladnaam van de beschadigde of ontbrekende werkmap.

4. Als u dit bestand niet hebt opgeslagen met de grafiek en het gegevensblad, slaat u het bestand op.

5. Klik in Excel 2003 of Excel 2002 op Koppelingen in het menu Bewerken en klik vervolgens op Bron wijzigen.

   Klik in Excel 2007 op het tabblad Gegevens , klik op Koppelingen bewerken in de groep Wijzigingen en klik vervolgens op Bron wijzigen.

6. Selecteer in het vak Bronbestand de koppeling die u wilt wijzigen en klik vervolgens op Bron wijzigen.

7. Selecteer in het dialoogvenster Koppelingen wijzigen het nieuwe bestand met de herstelde gegevens en grafiek en klik vervolgens op OK.

8. Als u het volgende foutbericht ontvangt

   De formule bevat een ongeldige externe verwijzing naar een werkblad.

   het is waarschijnlijk dat de bladnaam die u in stap 3 hebt getypt, niet hetzelfde is als de oorspronkelijke naam. Terug stap 1.

9. Het vak Bronbestand is nu mogelijk leeg. Dit geeft aan dat alle koppelingen verwijzen naar het actieve bestand in plaats van het ontbrekende of beschadigde bestand. Klik op Sluiten.

De grafiek verwijst nu naar en communiceert met de herstelde gegevens op het blad met de naam van het werkblad in de actieve werkmap.