Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Обновление сводной таблицы / 14 сообщений из 14, страница 1 из 1
26.01.2011, 11:45
    #37079161
YarmakIN
YarmakIN
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
Доброго времени суток!

Нуждаюсь в совете по формированию отчета в excel.
Мне нужно периодически получать отчет по результату выполнения SQL-скрипта.
Первоначально я делал так: в экселе создавал сводную таблицу, которая брала данные из внешнего источника (таблицы SQL-сервера). Грабли оказались в том, что юзеры не имеют прав обновлять эту сводную таблицу. Создать спецюзера и жестко прописать логин-пароль в самом файле экселя не получилось.
Тогда я подумал про другой вариант. Средствами SQL (OPENDATASOURCE) закидывать данные в эксель, а на соседнем листе в этом же файле создать сводную таблицу, которая бы автоматически обновлялась. Никакие пароли тут нужны не будут. Но тут какие грабли еще есть: 1. Перед вставкой нужно удалять старые данные. Я не знаю, как. 2. Количество записей разное каждый раз. Сводная таблица должна понимать, какой брать диапазон. Я не знаю, как это сделать, но, думаю, возможно, т.к. видел всякие похожие фрагменты этого тут на форуме.
Возможно, я вообще неправильно подхожу к данному вопросу и надо как то иначе вообще все делать?
Буду благодарен за любой совет!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2011, 12:59
    #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, 13:27
    #37079437
YarmakIN
YarmakIN
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
Да, мне бы хотелось управлять экселем со стороны. Т.е. чтоб файл отчета не имел никаких внешних связей, паролей и т.д
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2011, 13:28
    #37079443
big-duke
big-duke
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
YarmakIN,

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

Какой у вас Excel ?

2003. У юзеров тоже в основном он.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2011, 14:07
    #37079547
кладовщик
кладовщик
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
YarmakINВот по п1: как извне очистить лист? Или можно на какое то событие в файле повесить чистку, тогда на какое?
//temp - название очищаемого листа
Код: plaintext
Worksheets("temp").Cells.Clear
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2011, 14:13
    #37079569
YarmakIN
YarmakIN
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
Это понятно. Но когда его очистить? На какое событие повесить чистку?
Ведь его надо почистить перед заливкой информации запросом SQL.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2011, 14:22
    #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:26
    #37079609
big-duke
big-duke
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
YarmakIN,

Так, а если вам поставить автообновление на этот диапазон ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.01.2011, 15:02
    #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:10
    #37079749
YarmakIN
YarmakIN
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обновление сводной таблицы
big-dukeYarmakIN,

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

Порепетирую.
Но вот, что интересно.
Делаю Данные -> Импорт внешних данных -> Импортировать внешние данные.
Далее выбираю секретный *.udl файл, где уже все настроено с сохранением пароля.
Далее выбираю нужную таблицу на SQLServer.
При клике на кнопочке Свойства выбираю Обновлять при открытии и Сохранить пароль.
Далее... Если просто создать таблицу, то при каждом запуске файла эта таблица обновляется (напомню, стоит галочка Сохранить пароль). Т.е. все хорошо. Но если создать сводную таблицу, то при каждом запуске файла нужно вводить пароль!!! Вот этой байды я все никак не возьму в толк :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.01.2011, 09:44
    #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
| Ответить | Цитировать | Написать  
29.01.2011, 15:54
    #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
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Обновление сводной таблицы / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 364ms
Закрыть
start [/forum/topic.php?fid=61&tablet=1&tid=2177223]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 65ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 213ms
total:  364ms
x
x
Закрыть


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