Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / [VBA Excel] отчет на основании большого количества файлов / 23 сообщений из 23, страница 1 из 1
14.06.2010, 12:27
    #36685663
KiRx
KiRx
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
Доброго Всем времени суток, уважаемые программисты
Начну с предисловия. Так уж случилось, но я не программист, я проектировщик ИС (я хорошо умею проектировать информационные системы, но, к сожалению, очень плохо кодирую)
Вот наступила пора практики, и в организации мне дали задание, поработать с VBA и сделать отчет (как уже говорил, мудрёный). У меня, конечно, был студенческий опыт работы с VBA в Excel в универе на первых курсах, но те задания в подметки не годятся тому что я получил сейчас.

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

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

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


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




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

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


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

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

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

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

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

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

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

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

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

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

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

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

Ну тут вопрос собственно подхода. Я бы делал это не через query, а автоматизировал бы процесс на основе VBA. Т.е. элементарная форма интервала отчета и содержимого. А на эту форму повесил бы процесс изменения запроса и обновления данных. Типа Вашей формы в нулевом посте, только без директории, но с типом отчета.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2010, 10:42
    #36696077
KiRx
KiRx
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
решил подойти по-другому.
руками записал макрос на сортировку и промежуточные итоги.
Сортировка по значению по столбцу с номером филиала
Промежуточные итоги
– при каждом изменении в столбце Номер филиала суммировать значения
столбца Остатки
– при каждом изменении в столбце Номер филиала подсчитывать количество
значений в столбце Номер ссудных счетов.
Код: 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, 12:22
    #36696145
vlth
vlth
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
 
Код: 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:35
    #36696151
vlth
vlth
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
Только вместо 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, 15:04
    #36696285
KiRx
KiRx
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
vlth, спасибо! с перебором страниц получилось
а вот насчет промежуточных итогов. я их использовал для того, чтобы получить сумму по одному столбцу и количество по другому, когда в отсортированном столбце меняется значение.
Как думаете, это рационально? Просто потом мне промежуточные итоги нужно выцепить и сохранить в другой книге. а значения ячеек с промежуточными итогами это формулы типа
Код: plaintext
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 3 ;F8:F23)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2010, 19:50
    #36696493
vlth
vlth
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
KiRx, здесь я "пас": с промежуточными никогда не работал, а разбираться ради одного только интереса не хочется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2010, 21:24
    #36696588
big-duke
big-duke
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
KiRxvlth, спасибо! с перебором страниц получилось
а вот насчет промежуточных итогов. я их использовал для того, чтобы получить сумму по одному столбцу и количество по другому, когда в отсортированном столбце меняется значение.
Как думаете, это рационально? Просто потом мне промежуточные итоги нужно выцепить и сохранить в другой книге. а значения ячеек с промежуточными итогами это формулы типа
Код: plaintext
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 3 ;F8:F23)

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

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

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

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

конечно можем. А вы не хотите сами попробовать ?
Тем более что вы разбили ее на шаги :
1. Обойти диапазон
2. Найти значения
3. Скопировать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2010, 08:19
    #36697598
KiRx
KiRx
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
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, 10:49
    #36697821
vlth
vlth
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
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
| Ответить | Цитировать | Написать  
27.06.2010, 22:44
    #36710469
KiRx
KiRx
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
подскажите, пожалуйста
Код: plaintext
ActiveWorkbook.SaveAs ("D:\IASK\" & d & ".xls")
сохраняет с именем
Код: plaintext
_11. 06 .2010_.xls
где _ - пробел.
как от них избавиться?
убираю в коде они снова появляются..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2010, 22:58
    #36710480
Hugo121
Hugo121
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
KiRx,
убрать в коде, где задаётся d. Что ещё можно посоветовать, не видя кода?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2010, 23:01
    #36710483
Hugo121
Hugo121
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
[VBA Excel] отчет на основании большого количества файлов
KiRx,
убрать в коде, где задаётся d. Что ещё можно посоветовать, не видя кода?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / [VBA Excel] отчет на основании большого количества файлов / 23 сообщений из 23, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 491ms
Закрыть
start [/forum/topic.php?fid=61&mobile=1&tid=2177940]:
 0ms
get settings:
 6ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 187ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 36ms
get tp. blocked users:
 1ms
others: 238ms
total:  491ms
x
x
Закрыть


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