Вопрос или проблема
Я получаю ошибку:
не могу открыть файл источника сводной таблицы ...XLSX ЛИСТ...
при обновлении всех данных. У меня много сводных таблиц, и я не могу найти ту, в которой ошибка, при отдельном обновлении – ошибок нет, скрытых листов тоже нет.
Как мне узнать, где проблема?
У меня была такая проблема в сложной таблице. Я удалил все вкладки, пока не осталась пустая вкладка, и я все равно получил это сообщение. Я обнаружил, что исходная модель данных PowerPivot ссылалась на связанную таблицу, которой больше не существовало. Удаление связанной таблицы в модели PowerPivot решило проблему.
Проверьте код VBA здесь. Я его изменил. Если вы запустите код, он прервется, если возникнет ошибка.
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.name & "-->" & Pivot.name
Pivot.RefreshTable
Pivot.Update
Next
Next
End Sub
Ответ или решение
Для поиска источника проблемы с ошибкой, связанной с PivotTable в Excel 2010, выполните следующие шаги:
1. Проверка источников данных PivotTable
Первым шагом проверьте все источники данных для ваших сводных таблиц. Возможно, некоторые из используемых вами данных были удалены или изменены:
- Кликните правой кнопкой мыши на каждую сводную таблицу.
- Выберите "Изменить источник данных" (Change Data Source).
- Убедитесь, что источник данных доступен и правильно настроен. Если источник недоступен, эта таблица, скорее всего, и вызывает ошибку.
2. Использование макроса для выявления ошибок
Вы также можете воспользоваться VBA для проверки всех сводных таблиц и их попытки обновления. Это позволит быстро найти сводную таблицу, вызвавшую ошибку. Вот модифицированный код на VBA, который поможет вам:
Public Sub RefreshAllPivotTables()
Dim Sheet As Worksheet
Dim Pivot As PivotTable
On Error Resume Next ' Игнорируем ошибки
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
Debug.Print Sheet.Name & " --> " & Pivot.Name
Pivot.RefreshTable
If Err.Number <> 0 Then
Debug.Print "Ошибка в сводной таблице: " & Pivot.Name & " на листе: " & Sheet.Name
Err.Clear ' Сбрасываем ошибку
End If
Next Pivot
Next Sheet
On Error GoTo 0 ' Сбрасываем обработку ошибок
End SubКак использовать макрос:
- Откройте Excel и нажмите
Alt + F11, чтобы открыть редактор VBA. - Выберите
Insert>Module, чтобы создать новый модуль. - Вставьте приведенный выше код в модуль.
- Закройте редактор VBA и вернитесь в Excel.
- Нажмите
Alt + F8, выберитеRefreshAllPivotTablesи нажмите "Выполнить".
3. Проверка PowerPivot
Если ваша книга использует PowerPivot, проверьте, нет ли потерянных или некорректных ссылок на источники данных внутри модели PowerPivot:
- Откройте вкладку
PowerPivotи выберитеУправление(Manage). - Проверьте все таблицы и убедитесь, что источники данных доступны.
4. Прочие рекомендации
- Убедитесь, что вы не скрыли листы, содержащие данные. Иногда данные могут находиться на скрытых листах.
- Если у вас есть контейнеры с данными (например, внешние соединения), убедитесь, что они действительны.
Заключение
Следуя этим шагам, вы сможете выявить, какая именно сводная таблица вызывает ошибку, и принять соответствующие меры для ее устранения. Удачи!