ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / [VBA Excel] отчет на основании большого количества файлов
23 сообщений из 23, страница 1 из 1
[VBA Excel] отчет на основании большого количества файлов
    #36685663
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
Доброго Всем времени суток, уважаемые программисты
Начну с предисловия. Так уж случилось, но я не программист, я проектировщик ИС (я хорошо умею проектировать информационные системы, но, к сожалению, очень плохо кодирую)
Вот наступила пора практики, и в организации мне дали задание, поработать с VBA и сделать отчет (как уже говорил, мудрёный). У меня, конечно, был студенческий опыт работы с VBA в Excel в универе на первых курсах, но те задания в подметки не годятся тому что я получил сейчас.

Хочу чтобы знающие люди помогли кое в чем, направили в нужное русло, так сказать.

Итак, задание.
Существует некоторая автоматизированная система, способная выдавать пользователю отчеты на один день (ссудные счета и остатки по ним в нескольких определенных филиалах). Выгрузка отчетов производиться раз в месяц и сохраняется в определенной директории. От меня требуется написать макрос, способный формировать отчеты на определенный период, и в разрезе филиалов.
Как я понял, сперва необходимо просканировать директорию, выбрать файлы с нужной датой и работать с массивом данных.

Начал реализацию.
Создал надстройку, при вызове которой будет появляться форма следующего вида:


Нужно будет выбрать период (использовал Microsoft Date and Time Picker Control 6.0).
Тут появляется сразу вопрос как написать процедуру задающую начало и конец периода из датапикера?
Дату файлов нужно вынимать из исходных отчетов




как откинуть лишний текст и взять лишь дату?
Третий вопрос
как отобрать файлы с нужной датой, попадающей в заданный нами период?

Вот, это пока самые интересующие меня вопросы.
Заранее благодарен всем участвующим в обсуждении!
...
Рейтинг: 0 / 0
14.06.2010, 12:27
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36686736
gatogordo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gatogordo
Гость
KiRx,
Какого вида имена файлов в папке? В любом случае, в работе со списком файлов тебе поможет либо функция dir(), либо http://wm-help.net/books/book/32.html FileSystemObject.
Насчет получения значений из формы - посмотри в конструкторе форм имена элементов на своей форме (выбираешь элемент, нажимаешь F4), потом в коде используешь что то типа имя_формы.имя_элемента.value или text.
Ну и под конец добавь в код цикл перебора файлов из директории на форме.
Можно использовать что то похожее на

Код: plaintext
1.
2.
3.
4.
5.
for each file in folder.files
if file.name>=дата_1 and file.name <= дата_2 then
_твои действия_
end if
next file
Тут еще нужно выбрать из имени файла часть, которая является датой, затем преобразовать ее в дату.
Также file и folder надо заменить на переменные FSO (в книге из ссылки все понятно расписано).

В общем, задавай более конкретные вопросы, гугли, разберешься за пару дней)
...
Рейтинг: 0 / 0
15.06.2010, 00:28
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36686850
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
gatogordoКакого вида имена файлов в папке?
gatogordo,
Имена файлов задает пользователь, но на них не стоит рассчитывать. Дату стоит брать из ячейки, отчеты же выгружаются раз в месяц, не думаю, что пользователь корректно все сделает.
Да насчет использования FSO вы правы, сегодня буду пробовать.
Насчет значений из ячейки тоже попробую сегодня.


gatogordoВ общем, задавай более конкретные вопросы, гугли, разберешься за пару дней)
хорошо, спасибо! Буду стараться :)
...
Рейтинг: 0 / 0
15.06.2010, 05:00
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36686993
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
KiRx,

после обработки файлов и т.д, копайте в сторону сводных таблиц.
...
Рейтинг: 0 / 0
15.06.2010, 10:03
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36688050
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
KiRx,

Если я Вас верно понял в папке лежит куча файлов сформированных в конкретный месяц и в конкретный день. Нужно периодически обрабатывать часть из них?

Первый вопрос - почему не Access?

1. Засосали туда ВСЕ файлы, при помощи VBA
2. подключились к access сводной таблицей на основе sql-запроса
3. программно меняйте текст sql запроса (а если данных менее 1млн строк засосите все данные в excel)
4. формируйте пожалуйста любой детализации отчет

Я думаю, что так кошернее (и быстрее) чем открывать все файлы, вылавливать в cells нужный текст, потом этом тексте распознавать на дату и выбирать оттуда данные (тем более если я верно понял файл Вы не фильтруете)
...
Рейтинг: 0 / 0
15.06.2010, 16:02
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36688603
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
Shamanus,

только сегодня в руки попал отчет с количеством строк равным 60987. и это только за один день..
Видимо, прислушаюсь к Вашему совету.

Значит нужно содержимое xls переместить в БД Access и потом работать с файлом через MSQUERY, так?
Задание облегчилось тем что возможна обработка файлов поштучно, не обязательно с директорией.
...
Рейтинг: 0 / 0
15.06.2010, 20:47
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36689081
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
KiRxтолько сегодня в руки попал отчет с количеством строк равным 60987. и это только за один день..
Если ежедневно будет попадать в БД такой объем информации, то акса, с его ограничением на 2 гига, ненадолго хватит. Лучше уж сразу примеривайтесь к полноценному серверу БД.
...
Рейтинг: 0 / 0
16.06.2010, 06:32
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36689123
Фотография Shamanus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shamanus
Участник
mds_world,

согласен с Вами. Если данные нужно хранить хотябы полугодичные Access не справится
Автор, а за какой интервал может быть отчет, максимум?

[quot KiRx
Значит нужно содержимое xls переместить в БД Access [/quot]

все верно, можно соответствующую обработку написать на VBA, она будет открывать первый по списку файл, проверять содержимое ячейки, определять куда положить данные и засасывать данные в таблицу. На деле не все так страшно как звучит. Буквально 50 строк кода.

[quot KiRx
и потом работать с файлом через MSQUERY, так?[/quot]

Ну тут вопрос собственно подхода. Я бы делал это не через query, а автоматизировал бы процесс на основе VBA. Т.е. элементарная форма интервала отчета и содержимого. А на эту форму повесил бы процесс изменения запроса и обновления данных. Типа Вашей формы в нулевом посте, только без директории, но с типом отчета.
...
Рейтинг: 0 / 0
16.06.2010, 08:38
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696077
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
решил подойти по-другому.
руками записал макрос на сортировку и промежуточные итоги.
Сортировка по значению по столбцу с номером филиала
Промежуточные итоги
– при каждом изменении в столбце Номер филиала суммировать значения
столбца Остатки
– при каждом изменении в столбце Номер филиала подсчитывать количество
значений в столбце Номер ссудных счетов.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
Sub sortANDrez()
    ActiveWorkbook.Worksheets("Остатки_1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Остатки_1").Sort.SortFields.Add Key:=Range("D8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Остатки_1").Sort
        .SetRange Range("B8:O60994")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:= 3 , Function:=xlSum, TotalList:=Array( 9 ), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
    Selection.Subtotal GroupBy:= 3 , Function:=xlCount, TotalList:=Array( 5 ), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True
End Sub
Как оптимизировать этот код для того чтобы он пробегал по всем листам книги?
...
Рейтинг: 0 / 0
19.06.2010, 10:42
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696145
Фотография vlth
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlth
Участник
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets
    With WS.Sort.SortFields
        .Clear
        .Add Key:=Range("D8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Parent
            .SetRange Range("B8:O60994")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    Selection.Subtotal GroupBy:= 3 , Function:=xlSum, TotalList:=Array( 9 ), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
    Selection.Subtotal GroupBy:= 3 , Function:=xlCount, TotalList:=Array( 5 ), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True
Next
...
Рейтинг: 0 / 0
19.06.2010, 12:22
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696151
Фотография vlth
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlth
Участник
Только вместо Selection
 
Код: plaintext
1.
2.
3.
Selection.Subtotal GroupBy:= 3 , Function:=xlSum, TotalList:=Array( 9 ), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
Selection.Subtotal GroupBy:= 3 , Function:=xlCount, TotalList:=Array( 5 ), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True

нужно указать конкретный диапазон. Или предварительно активировать лист (WS).
...
Рейтинг: 0 / 0
19.06.2010, 12:35
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696285
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
vlth, спасибо! с перебором страниц получилось
а вот насчет промежуточных итогов. я их использовал для того, чтобы получить сумму по одному столбцу и количество по другому, когда в отсортированном столбце меняется значение.
Как думаете, это рационально? Просто потом мне промежуточные итоги нужно выцепить и сохранить в другой книге. а значения ячеек с промежуточными итогами это формулы типа
Код: plaintext
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 3 ;F8:F23)
...
Рейтинг: 0 / 0
19.06.2010, 15:04
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696493
Фотография vlth
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlth
Участник
KiRx, здесь я "пас": с промежуточными никогда не работал, а разбираться ради одного только интереса не хочется.
...
Рейтинг: 0 / 0
19.06.2010, 19:50
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696588
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
KiRxvlth, спасибо! с перебором страниц получилось
а вот насчет промежуточных итогов. я их использовал для того, чтобы получить сумму по одному столбцу и количество по другому, когда в отсортированном столбце меняется значение.
Как думаете, это рационально? Просто потом мне промежуточные итоги нужно выцепить и сохранить в другой книге. а значения ячеек с промежуточными итогами это формулы типа
Код: plaintext
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 3 ;F8:F23)

И что не так ?
...
Рейтинг: 0 / 0
19.06.2010, 21:24
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696656
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
big-dukeKiRxvlth, спасибо! с перебором страниц получилось
а вот насчет промежуточных итогов. я их использовал для того, чтобы получить сумму по одному столбцу и количество по другому, когда в отсортированном столбце меняется значение.
Как думаете, это рационально? Просто потом мне промежуточные итоги нужно выцепить и сохранить в другой книге. а значения ячеек с промежуточными итогами это формулы типа
Код: plaintext
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 3 ;F8:F23)

И что не так ?
а проблем не будет с копированием из ячеек с формулами?
вот что имею:

может стоит пробегать по столбцам и суммировать нудные столбцы? без промежуточных итогов
...
Рейтинг: 0 / 0
19.06.2010, 22:28
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696802
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
KiRx,

так вы копируйте не формулу, а значение.
...
Рейтинг: 0 / 0
20.06.2010, 10:45
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696810
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
а может кто-нибудь подсказать примерный код макроса, который пробегает по строчкам, находит нужный текст (в моем случае "Количество" и "Итог") и копирует значения из нужных ячеек этой строки?
...
Рейтинг: 0 / 0
20.06.2010, 10:54
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36696815
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-duke
Участник
KiRx,

конечно можем. А вы не хотите сами попробовать ?
Тем более что вы разбили ее на шаги :
1. Обойти диапазон
2. Найти значения
3. Скопировать.
...
Рейтинг: 0 / 0
20.06.2010, 11:03
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36697598
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
vlthТолько вместо Selection+
 

Код: plaintext
1.
2.
3.
Selection.Subtotal GroupBy:= 3 , Function:=xlSum, TotalList:=Array( 9 ), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
Selection.Subtotal GroupBy:= 3 , Function:=xlCount, TotalList:=Array( 5 ), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True

нужно указать конкретный диапазон. Или предварительно активировать лист (WS).

Вот, работать хочет только для одного листа... почему?
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Sub PromItog()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
    With WS.Range("B7:O60994").Select
    Selection.Subtotal GroupBy:= 3 , Function:=xlSum, TotalList:=Array( 9 ), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
    Selection.Subtotal GroupBy:= 3 , Function:=xlCount, TotalList:=Array( 5 ), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True
    End With
Next
End Sub

и еще вопрос если у меня последний лист может кончаться не O60994 а O8876, как это учесть?
...
Рейтинг: 0 / 0
21.06.2010, 08:19
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36697821
Фотография vlth
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlth
Участник
KiRxВот, работать хочет только для одного листа... почему?
...
и еще вопрос если у меня последний лист может кончаться не O60994 а O8876, как это учесть?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Sub PromItog()
Dim WS As Worksheet, lngLRow As Range

'И здесь вместо ActiveWorkbook лучше ссылаться на конкретную книгу
For Each WS In ActiveWorkbook.Worksheets

    lngLRow = WS.Cells(WS.Rows.Count,  15 ).End(xlUp).Row
    With WS.Range(WS.Cells( 7 ,  2 ), WS.Cells(lngLRow,  15 ))
        .Subtotal GroupBy:= 3 , Function:=xlSum, TotalList:=Array( 9 ), _
            Replace:=True, PageBreaks:=True, SummaryBelowData:=True
        .Subtotal GroupBy:= 3 , Function:=xlCount, TotalList:=Array( 5 ), _
            Replace:=False, PageBreaks:=True, SummaryBelowData:=True
    End With
Next
End Sub

Будет работать, если ниже ячейки O60994 (или O8876) нет данных.
...
Рейтинг: 0 / 0
21.06.2010, 10:49
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36710469
KiRx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KiRx
Гость
подскажите, пожалуйста
Код: plaintext
ActiveWorkbook.SaveAs ("D:\IASK\" & d & ".xls")
сохраняет с именем
Код: plaintext
_11. 06 .2010_.xls
где _ - пробел.
как от них избавиться?
убираю в коде они снова появляются..
...
Рейтинг: 0 / 0
27.06.2010, 22:44
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36710480
Hugo121
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hugo121
Гость
KiRx,
убрать в коде, где задаётся d. Что ещё можно посоветовать, не видя кода?
...
Рейтинг: 0 / 0
27.06.2010, 22:58
| Ответить | Цитировать | Написать  
[VBA Excel] отчет на основании большого количества файлов
    #36710483
Hugo121
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hugo121
Гость
KiRx,
убрать в коде, где задаётся d. Что ещё можно посоветовать, не видя кода?
...
Рейтинг: 0 / 0
27.06.2010, 23:01
| Ответить | Цитировать | Написать  
23 сообщений из 23, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / [VBA Excel] отчет на основании большого количества файлов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=36696656&tid=2177940]:
 0ms
get settings:
 9ms
get forum list:
 20ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 190ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 80ms
get tp. blocked users:
 1ms
others: 223ms
total:  543ms
созд. / загр.: 543ms
x
x
Закрыть


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