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.

Alle externe gegevensbronnen voor een hele werkmap weergeven

Alle externe gegevensbereiken in een Microsoft Excel-werkmap weergeven

1. Start Excel en open vervolgens de werkmap waarin u naar externe gegevensbronnen wilt zoeken.
2. Wijs Macro aan in het menu Extra en klik op Visual Basic-editor.
3. Open het menu Invoegen van de Visual Basic-editor en klik op Module.
4. Typ in het modulevenster de volgende programmacode:
   Sub ListWebQueryPivotTableLinks() Dim wbA As Workbook, wsN As Worksheet, ws As Worksheet Dim pt As PivotTable, qt As QueryTable, R As Long, i As Long Dim vLnkSrc As Variant Const PROGCREATE As String = "Dit externe " & _ "gegevensbereik is met behulp van " & _ "programmacode gemaakt en kan niet worden bewerkt" On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsN = Workbooks.Add(xlWorksheet).Worksheets(1) wsN.Name = wbA.Name wsN.Range("A1:E1").Value = Array("Name", "Location", _ "Type", "Connection", "CommandText") wsN.Range("A1:E1").Font.Bold = True R = 1 For Each ws In wbA.Worksheets For Each pt In ws.PivotTables R = R + 1 With pt.PivotCache wsN.Cells(R, 1).Value = pt.Name wsN.Cells(R, 2).Value = ws.Name & "!" & _ pt.TableRange2.Address(False, False) Select Case .SourceType Case xlConsolidation R = R -1 For i = 1 To UBound(.SourceData) R = R + 1 wsN.Cells(R, 1).Value = pt.Name wsN.Cells(R, 2).Value = ws.Name & "!" & _ pt.TableRange2.Address(False, False) wsN.Cells(R, 3).Value = _ "Draaitabel - Samenvoegingsbereik" wsN.Cells(R, 4).Value = "'" & _ .SourceData(i, 1) wsN.Cells(R, 5).Value = "n.v.t." Next Case xlDatabase wsN.Cells(R, 3).Value = "Draaitabel - Excel-lijst" wsN.Cells(R, 4).Value = "'" & .SourceData wsN.Cells(R, 5).Value = "n.v.t." Case xlExternal If .OLAP Then wsN.Cells(R, 3).Value = "Draaitabel - OLAP" wsN.Cells(R, 4).Value = "'" & .Connection wsN.Cells(R, 5).Value = .CommandText ElseIf .QueryType = xlADORecordset Then wsN.Cells(R, 3).Value = _ "Draaitabel - ADO-recordset" wsN.Cells(R, 4).Value = PROGCREATE wsN.Cells(R, 5).Value = "'" & .Recordset.Source Else wsN.Cells(R, 3).Value = _ "Draaitabel - Externe gegevens" wsN.Cells(R, 4).Value = "'" & .Connection wsN.Cells(R, 5).Value = .CommandText End If Case xlScenario wsN.Cells(R, 3).Value = "Draaitabel - Scenario" wsN.Cells(R, 4).Value = "Gebaseerd op een scenario " & _ "in deze werkmap" wsN.Cells(R, 5).Value = "n.v.t." End Select End With Next For Each qt In ws.QueryTables R = R + 1 wsN.Cells(R, 1).Value = qt.Name wsN.Cells(R, 2).Value = ws.Name & "!" & _ qt.ResultRange.Address(False, False) Select Case qt.QueryType Case xlTextImport wsN.Cells(R, 3).Value = "Text importeren" wsN.Cells(R, 4).Value = "'" & qt.Connection wsN.Cells(R, 5).Value = "n.v.t." Case xlOLEDBQuery wsN.Cells(R, 3).Value = "Querytabel - OLEDB-query" wsN.Cells(R, 4).Value = "'" & qt.Connection wsN.Cells(R, 5).Value = "'" & qt.CommandText Case xlWebQuery wsN.Cells(R, 3).Value = "Webquerytabel" wsN.Cells(R, 4).Value = "'" & qt.Connection wsN.Cells(R, 5).Value = "n.v.t." Case xlADORecordset wsN.Cells(R, 3).Value = "Querytabel - ADO-recordset" wsN.Cells(R, 4).Value = PROGCREATE wsN.Cells(R, 5).Value = "'" & qt.Recordset.Source Case xlDAORecordset wsN.Cells(R, 3).Value = "Querytabel - DAO-recordset" On Error Resume Next wsN.Cells(R, 4).Value = "'" & qt.Recordset.Parent.Name If Err.Number <> 0 Then wsN.Cells(R, 4).Value = PROGCREATE Err.Clear End If wsN.Cells(R, 5).Value = "'" & qt.Recordset.Name If Err.Number <> 0 Then wsN.Cells(R, 5).Value = PROGCREATE Err.Clear End If On Error GoTo errHandler Case xlODBCQuery wsN.Cells(R, 3).Value = "Querytabel" wsN.Cells(R, 4).Value = "'" & qt.Connection wsN.Cells(R, 5).Value = qt.CommandText End Select
Next Next vLnkSrc = wbA.LinkSources If Not IsEmpty(vLnkSrc) Then For i = 1 To UBound(vLnkSrc) R = R + 1 wsN.Cells(R, 1).Value = "n.v.t." wsN.Cells(R, 2).Value = "n.v.t." wsN.Cells(R, 3).Value = "Koppelingsbron (Bewerken | Koppelingen)" wsN.Cells(R, 4).Value = vLnkSrc(i) Next End If wsN.Cells.WrapText = False wsN.Columns.AutoFit wsN.UsedRange.AutoFilter Exit SuberrHandler: MsgBox "Er is een fout opgetreden." & vbCr & Err.Number & _ vbCr & Err.Description Resume NextEnd Sub
   
5. Klik in het menu Bestand op Sluiten en terugkeren naar Microsoft Excel.
6. Wijs in het menu Extra de optie Macro aan en klik op Macro's.
7. Klik in de lijst met macro's op ListWebQueryPivotTableLinks en klik vervolgens op Uitvoeren.
   
   Alle externe gegevensbronnen worden weergegeven in een nieuwe werkmap, waarbij bron, locatie, type en gegevens over de verbinding worden weergegeven.

Alle externe gegevensbereiken in een werkblad selecteren

Alle externe gegevensbereiken in een Microsoft Excel-werkmap weergeven:

1. Start Excel en open de werkmap met de externe gegevensbereiken die u wilt selecteren.
   
   Klik op de tab van het werkblad dat u wilt controleren.
2. Wijs Macro aan in het menu Extra en klik op Visual Basic-editor.
3. Open het menu Invoegen van de Visual Basic-editor en klik op Module.
4. Typ in het modulevenster de volgende programmacode:
   'Met deze Sub-procedure worden alle cellen in het werkblad geselecteerd die deel uitmaken van'externe gegevensbereikenSub SelectAllQueryTables() FirstCell = 1 For Each xQuery In ActiveSheet.QueryTables If FirstCell = 1 Then Set xRange = xQuery.ResultRange FirstCell = 0 Else Set xRange = Application.Union(xRange, xQuery.ResultRange) End If Next xQuery xRange.SelectEnd Sub
   
5. Klik in het menu Bestand op Sluiten en terugkeren naar Microsoft Excel.
6. Wijs in het menu Extra de optie Macro aan en klik op Macro's.
7. Klik in de lijst met macro's op SelectAllQueryTables en klik op Uitvoeren.
   
   Alle externe gegevensbereiken worden geselecteerd en u kunt van cel naar cel in de selectie gaan door op de TAB-toets te drukken. Selecteer een ander werkblad en herhaal stap 6 en 7 om de externe gegevensbereiken in dat werkblad te selecteren.

Bepalen of een extern gegevensbereik in een werkmap automatisch wordt bijgewerkt

Bepalen of een extern gegevensbereik in een werkmap automatisch gegevens ophaalt:

1. Selecteer alle externe gegevensbereiken in het werkblad met behulp van de macro SelectAllQueryTables.
2. Klik met de rechtermuisknop op een cel van een geselecteerd extern gegevensbereik en klik vervolgens op Gegevensbereikeigenschappen.
3. Kijk in het dialoogvenster Eigenschappen extern gegevensbereik naar het selectievakje Vernieuwen om de n minuten en het selectievakje Gegevens vernieuwen bij Bestand openen.
   
   Als een van beide selectievakjes is ingeschakeld, worden de gegevens automatisch vernieuwd op basis van de ingestelde interval of wanneer het bestand wordt geopend. Als u niet wilt dat de gegevens automatisch worden vernieuwd, schakelt u het selectievakje Vernieuwen om de n minuten en het selectievakje Gegevens vernieuwen bij Bestand openen uit.
4. Klik met de rechtermuisknop op een cel in hetzelfde geselecteerde extern gegevensbereik en klik vervolgens op Parameters als deze opdracht beschikbaar is.
5. Klik op alle items aan de linkerzijde van het dialoogvenster.
   
   Als het selectievakje Automatisch vernieuwen bij gewijzigde celwaarde is ingeschakeld, kan het externe gegevensbereik automatisch gegevens ophalen. U kunt dit voorkomen door het selectievakje uit te schakelen.
6. Herhaal stap 2 tot en met 5 voor elk extern gegevensbereik in de werkmap.

Alle draaitabellen in een werkmap selecteren

Alle draaitabellen in een Microsoft Excel-werkmap selecteren:

1. Start Excel en open de werkmap met de draaitabellen die u wilt selecteren. Klik op de tab van het werkblad dat u wilt controleren.
2. Wijs Macro aan in het menu Extra en klik op Visual Basic-editor.
3. Open het menu Invoegen van de Visual Basic-editor en klik op Module.
4. Typ in het modulevenster de volgende programmacode:
   'Met deze Sub-procedure worden alle cellen in het werkblad geselecteerd die deel uitmaken van'draaitabelrapportenSub SelectAllPivotTables() FirstCell = 1 For Each xQuery In ActiveSheet.PivotTables If FirstCell = 1 Then Set xRange = xQuery. TableRange2 FirstCell = 0 Else Set xRange = Application.Union(xRange, xQuery.TableRange2) End If Next xQuery xRange.SelectEnd Sub
   
5. Klik in het menu Bestand op Sluiten en terugkeren naar Microsoft Excel.
6. Wijs in het menu Extra de optie Macro aan en klik op Macro's.
7. Klik in de lijst met macro's op SelectAllPivotTables en klik op Uitvoeren.
   
   Alle draaitabellen in het werkblad zijn nu geselecteerd. Selecteer een ander werkblad en herhaal stap 6 en 7 om de draaitabellen in dat werkblad te selecteren.

Bepalen of een draaitabelrapport in een werkmap automatisch wordt bijgewerkt

Bepalen of een draaitabel automatisch gegevens ophaalt:

1. Selecteer alle draaitabellen in het werkblad met behulp van de macro SelectAllPivotTables.
2. Klik met de rechtermuisknop in een draaitabel en klik vervolgens op Tabelopties.
3. Kijk in het dialoogvenster Opties voor draaitabel naar het selectievakje Vernieuwen om de n minuten en het selectievakje Vernieuwen bij openen.
   
   Als een van beide selectievakjes is ingeschakeld, worden de gegevens automatisch vernieuwd op basis van de ingestelde interval of wanneer het bestand wordt geopend. Als u niet wilt dat de gegevens automatisch worden vernieuwd, schakelt u het selectievakje Vernieuwen om de n minuten en het selectievakje Vernieuwen bij openen uit.
4. Herhaal stap 2 en 3 voor elke draaitabel in de werkmap.

Het dialoogvenster Koppelingen bewerken gebruiken

Als cellen in de werkmap formules bevatten die naar externe Excel-werkmappen verwijzen, kunt u met het dialoogvenster Koppelingen bewerken deze koppelingen weergeven en bepalen hoe deze worden bijgewerkt. U gebruikt dit dialoogvenster als volgt:

1. Klik in het menu Bewerken op Koppelingen om het dialoogvenster Koppelingen bewerken te openen.
2. In het dialoogvenster Koppelingen bewerken wordt een lijst met koppelingen naar externe werkmappen weergegeven.
3. Als u alle gegevens handmatig wilt bijwerken, klikt u op Waarden bijwerken.
4. Als u de bron van een koppeling wilt wijzigen, selecteert u eerst de koppeling die u wilt wijzigen en klikt u vervolgens op Bron wijzigen.
   
   Selecteer een nieuwe bron en klik op OK.
5. Als u een bron wilt openen, selecteert u eerst de koppeling die u wilt openen en klikt u vervolgens op Bron openen.
6. Als u de koppeling met de externe gegevensbron wilt verbreken, klikt u op Koppeling verbreken.
   
   Bij deze opdracht blijven de huidige gegevens in de cel aanwezig, maar wordt de verwijzing naar de externe gegevens verwijderd.
7. Als u de status van koppelingen wilt controleren en wilt vaststellen of deze nog beschikbaar zijn, klikt u op Status controleren.