ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Обновление сводной таблицы
14 сообщений из 14, страница 1 из 1
Обновление сводной таблицы
    #37079161
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
Доброго времени суток!

Нуждаюсь в совете по формированию отчета в excel.
Мне нужно периодически получать отчет по результату выполнения SQL-скрипта.
Первоначально я делал так: в экселе создавал сводную таблицу, которая брала данные из внешнего источника (таблицы SQL-сервера). Грабли оказались в том, что юзеры не имеют прав обновлять эту сводную таблицу. Создать спецюзера и жестко прописать логин-пароль в самом файле экселя не получилось.
Тогда я подумал про другой вариант. Средствами SQL (OPENDATASOURCE) закидывать данные в эксель, а на соседнем листе в этом же файле создать сводную таблицу, которая бы автоматически обновлялась. Никакие пароли тут нужны не будут. Но тут какие грабли еще есть: 1. Перед вставкой нужно удалять старые данные. Я не знаю, как. 2. Количество записей разное каждый раз. Сводная таблица должна понимать, какой брать диапазон. Я не знаю, как это сделать, но, думаю, возможно, т.к. видел всякие похожие фрагменты этого тут на форуме.
Возможно, я вообще неправильно подхожу к данному вопросу и надо как то иначе вообще все делать?
Буду благодарен за любой совет!
...
Рейтинг: 0 / 0
26.01.2011, 11:45
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079350
кладовщик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кладовщик
Участник
YarmakIN, я не очень понял, Вы Экселем управляете из стороннего приложения? В таком случае как вар-т Вам нужно:
1. очистить диапазон листа-приемника
2. скинуть данные на лист-приемник
3. передать в сводную таблицу диапазон
По п.3 такой код у меня вроде рабортал в прошлом году:
Код: plaintext
1.
2.
3.
4.
//PT - имя листа, на кот-м сводная, data - лист исходных данных
Worksheets("PT").PivotTables("СводнаяТаблица1").ChangePivotCache ActiveWorkbook. _
       PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ThisWorkbook.Worksheets("data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), _
        Version:=xlPivotTableVersion12)
...
Рейтинг: 0 / 0
26.01.2011, 12:59
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079437
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
Да, мне бы хотелось управлять экселем со стороны. Т.е. чтоб файл отчета не имел никаких внешних связей, паролей и т.д
...
Рейтинг: 0 / 0
26.01.2011, 13:27
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079443
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
YarmakIN,

Какой у вас Excel ?
...
Рейтинг: 0 / 0
26.01.2011, 13:28
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079444
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
Вот по п1: как извне очистить лист? Или можно на какое то событие в файле повесить чистку, тогда на какое?
...
Рейтинг: 0 / 0
26.01.2011, 13:28
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079446
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
big-dukeYarmakIN,

Какой у вас Excel ?

2003. У юзеров тоже в основном он.
...
Рейтинг: 0 / 0
26.01.2011, 13:29
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079547
кладовщик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кладовщик
Участник
YarmakINВот по п1: как извне очистить лист? Или можно на какое то событие в файле повесить чистку, тогда на какое?
//temp - название очищаемого листа
Код: plaintext
Worksheets("temp").Cells.Clear
...
Рейтинг: 0 / 0
26.01.2011, 14:07
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079569
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
Это понятно. Но когда его очистить? На какое событие повесить чистку?
Ведь его надо почистить перед заливкой информации запросом SQL.
...
Рейтинг: 0 / 0
26.01.2011, 14:13
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079593
кладовщик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кладовщик
Участник
YarmakINЭто понятно. Но когда его очистить? На какое событие повесить чистку?
Ведь его надо почистить перед заливкой информации запросом SQL.
Я не знаю, как Вы реализуете выгрузку рекордсета на лист. Вот как бы я это делал в 1С.
1. Создал СОМ-объект Excel.Application, после чего пользовался бы всеми делами приложения, а именно
2. Подконнектился бы к источнику данных по ADO, получил бы объект recordset
3. Очистил лист Worksheets("data").Cells.Clear
4. Скинул бы рекордсет на лист (начиная с ячейки а1) Worksheets("data").Range(''A1").Copyfromrecordset Recordset
5. Передал бы в сводную таблицу ссылку на весь диапазон данных на листе
SourceData:=ThisWorkbook.Worksheets("data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
ЗЫ По синтаксису могу ошибаться - коллеги пускай поправят.
...
Рейтинг: 0 / 0
26.01.2011, 14:22
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079609
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
YarmakIN,

Так, а если вам поставить автообновление на этот диапазон ?
...
Рейтинг: 0 / 0
26.01.2011, 14:26
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079727
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
кладовщикYarmakINЭто понятно. Но когда его очистить? На какое событие повесить чистку?
Ведь его надо почистить перед заливкой информации запросом SQL.
Я не знаю, как Вы реализуете выгрузку рекордсета на лист. Вот как бы я это делал в 1С.
1. Создал СОМ-объект Excel.Application, после чего пользовался бы всеми делами приложения, а именно
2. Подконнектился бы к источнику данных по ADO, получил бы объект recordset
3. Очистил лист Worksheets("data").Cells.Clear
4. Скинул бы рекордсет на лист (начиная с ячейки а1) Worksheets("data").Range(''A1").Copyfromrecordset Recordset
5. Передал бы в сводную таблицу ссылку на весь диапазон данных на листе
SourceData:=ThisWorkbook.Worksheets("data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
ЗЫ По синтаксису могу ошибаться - коллеги пускай поправят.

Спасибо! Репетирую как раз нечто подобное :)
...
Рейтинг: 0 / 0
26.01.2011, 15:02
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37079749
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
big-dukeYarmakIN,

Так, а если вам поставить автообновление на этот диапазон ?

Порепетирую.
Но вот, что интересно.
Делаю Данные -> Импорт внешних данных -> Импортировать внешние данные.
Далее выбираю секретный *.udl файл, где уже все настроено с сохранением пароля.
Далее выбираю нужную таблицу на SQLServer.
При клике на кнопочке Свойства выбираю Обновлять при открытии и Сохранить пароль.
Далее... Если просто создать таблицу, то при каждом запуске файла эта таблица обновляется (напомню, стоит галочка Сохранить пароль). Т.е. все хорошо. Но если создать сводную таблицу, то при каждом запуске файла нужно вводить пароль!!! Вот этой байды я все никак не возьму в толк :(
...
Рейтинг: 0 / 0
26.01.2011, 15:10
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37083435
YarmakIN
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YarmakIN
Участник
кладовщик...
3. передать в сводную таблицу диапазон
По п.3 такой код у меня вроде рабортал в прошлом году:
Код: plaintext
1.
2.
3.
4.
//PT - имя листа, на кот-м сводная, data - лист исходных данных
Worksheets("PT").PivotTables("СводнаяТаблица1").ChangePivotCache ActiveWorkbook. _
       PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ThisWorkbook.Worksheets("data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), _
        Version:=xlPivotTableVersion12)

Я такой код не сумел прикрутить. Но получилось даже лучше, как мне кажется:
Код: plaintext
1.
2.
3.
4.
    Dim PV As PivotTable
    rcnt = Worksheets("Source").UsedRange.Rows.Count
    Set PV = Worksheets("Pivot").PivotTables("PivotTable")
    PV.SourceData = "Source!R1C1:R" & rcnt & "C7"
...
Рейтинг: 0 / 0
28.01.2011, 09:44
| Ответить | Цитировать | Написать  
Обновление сводной таблицы
    #37086050
кладовщик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кладовщик
Участник
YarmakINЯ такой код не сумел прикрутить. Но получилось даже лучше, как мне кажется:
Код: plaintext
1.
2.
3.
4.
    Dim PV As PivotTable
    rcnt = Worksheets("Source").UsedRange.Rows.Count
    Set PV = Worksheets("Pivot").PivotTables("PivotTable")
    PV.SourceData = "Source!R1C1:R" & rcnt & "C7"

Насколько я помню, получилось ровно так же, т.к. Worksheets("Source").UsedRange в Вашем случае равен Range("A1").CurrentRegion.
В случае, если для разных СводныхТаблиц на одном листе источниками данных являются разные отдельно стоящие диапазоны на листе, я использую именно Range("A1").CurrentRegion.Address.
...
Рейтинг: 0 / 0
29.01.2011, 15:54
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Обновление сводной таблицы
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=37079444&tid=2177223]:
 0ms
get settings:
 11ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 161ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 210ms
total:  476ms
созд. / загр.: 476ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]