Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Office Новый топик    Ответить
 Автоматическое чтение данных документов Excel из почты  [new]
net_Alex_tut
Member

Откуда:
Сообщений: 9
Добрый день!

Помогите пожалуйста со скриптом VB, который
1. перебирает почтовые сообщения (по Subject, имени Excel документа в скрепке)
2. найдя нужные параметры читает данные из оперделенной ячейки Excel документа (который в скрепке)
3. складывает эти данные в другой документ Excel

Спасибо
С уважением
Алексей
28 ноя 18, 07:19    [21746771]     Ответить | Цитировать Сообщить модератору
 Re: Автоматическое чтение данных документов Excel из почты  [new]
big-duke
Member

Откуда:
Сообщений: 6720
net_Alex_tut,

Начните с :
  • Outlook.MAPIFolder
  • GetDefaultFolder(olFolderInbox)
  • For Each Item In olFolder.Items
  • TypeOf Item Is Outlook.MailItem
  • 28 ноя 18, 10:09    [21746897]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    net_Alex_tut
    Member

    Откуда:
    Сообщений: 9
    big-duke,

    спасибо за ответ
    и данный совет
    я погуглю

    буду также признателен если дадите ссылки на готовый скрипт похожий на то что я перечислил

    сам программированием давно не занимюсь
    по работе возникла необходимость обработать данные
    28 ноя 18, 12:14    [21747125]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    net_Alex_tut
    Member

    Откуда:
    Сообщений: 9
    net_Alex_tut,

    Разобрался
    Написал скрипт
    Все работает :
    Ищутся непрочтенные письма по теме и типу приложения выбираются аттач и читаются данные
    после прочтения данных сообщение помечается как прочитанное

    два но:
    1) при небольшом количестве непрочтенных сообщений - 6-15 при общем колве в инбох около 300-400 работает норм
    если же количество непрочтенных 30-60 начинаются глюки- перескакивает через пачку сообщений в результате куски сообщений нерочтенные
    приходится по нескольку раз запускать чтобы все сообщения перебрались
    в чем тут дело ?
    2) для чтения из эксель файла - аттачмента его нужно открыть что занимет время особенно если док эксель большой
    можно ли без открытия эксель файла из него читать данные ?

    Const olFolderInbox As Integer = 6
    '~~> Path for the attachment
    Const AttachmentPath As String = "e:\temp\"
    
    
    Sub DORscan_1()
        Dim oOlAp As Object, oOlns As Object, oOlInb As Object
        Dim oOlItm As Object, oOlAtch As Object
        Dim wb As Workbook
        Dim i As Integer
        i = 1
        Dim value As Variant
        Dim value1 As Variant
        Dim value2 As Variant
        Dim value3 As Variant
        Dim value4 As Variant
        Dim value5 As Variant
        
        Dim v_date As String
            
        '~~> New File Name for the attachment
        Dim NewFileName As String
        NewFileName = AttachmentPath & "test1.xls"
    
        '~~> Get Outlook instance
        Set oOlAp = GetObject(, "Outlook.application")
        Set oOlns = oOlAp.GetNamespace("MAPI")
        Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)
    
        '~~> Check if there are any actual unread emails
        If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
            MsgBox "NO Unread Email In Inbox"
            Exit Sub
        End If
    
        '~~> Extract the attachment from the 1st unread email
        For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
    
        
            '~~> Check if the email actually has an attachment
            sSubj = oOlItm.Subject
            If Left(sSubj, 9) = "WD/CD DOR" Then
                If oOlItm.Attachments.Count <> 0 Then
                    For Each oOlAtch In oOlItm.Attachments
                               
                            If Left(Right(oOlAtch.Filename, 5), 4) = ".xls" And Left(oOlAtch.Filename, 2) = "WD" Then
                                '~~> Download the attachment
                                oOlAtch.SaveAsFile NewFileName
                                Set wb = Workbooks.Open(NewFileName, UpdateLinks:=0)
                                i = i + 1
                                
                                Sheets("WDDP-A Daily Highlight").Select
                                
                                v_date = Range("L4")
                                value = Range("E33")
                                value1 = Range("E34")
                                
                                
                                Windows("WD DOR.xlsm").Activate
                                Sheets("list1").Select
                                Cells(i, 1).value = v_date
                                Cells(i, 2).value = value
                                Cells(i, 3).value = value1
                                
                                If i = 30 Or i = 100 Or i = 200 Or i = 300 Or i = 400 Then
                                    ActiveWorkbook.Save
                                Else
                                End If
                                Application.DisplayAlerts = False
                                Workbooks("test1.xls").Close SaveChanges:=False
                                
                                
                                Else
                               ' MsgBox "attachment is not excel"
                            End If
                            'Exit For
                     Next
                Else
                    'MsgBox "The First item doesn't have an attachment"
                End If
            Else
            End If
        
            
            oOlItm.UnRead = False
            'DoEvents
            'oOlItm.Save
        
        
        Next
             
     End Sub
    
    Модератор: Учимся использовать тэги оформления кода - FAQ


    Сообщение было отредактировано: 29 ноя 18, 16:13
    29 ноя 18, 15:39    [21748759]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    net_Alex_tut
    Member

    Откуда:
    Сообщений: 9
    модератору

    эх нэкогда ! :)
    посовнтуйте лучше :)
    29 ноя 18, 16:30    [21748853]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    ldfanate
    Member

    Откуда:
    Сообщений: 145
    относительно перескакивания - это известная фича оутлуковской объектной модели: когда считываете только непрочитанные, за время пробега цикла часть сообщений могут стать прочитанными, плюс прийти новые. Поэтому коллекция oOlInb.Items бывает динамически меняется на ходу (до окончания цикла).
    '~~> Extract the attachment from the 1st unread email
        For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
    


    Надёжный способ - вычитать коллекцию перед for-each - т.е. сделать "снимок" в отдельную переменную. И по нему уже крутить цикл.
    30 ноя 18, 15:22    [21750139]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    ldfanate
    Member

    Откуда:
    Сообщений: 145
    и в плане производительности - не лучшее решение открывать-закрывать книгу на каждом обороте цикла по аттачам.

    Почему бы сначала в цикле не сгрузить все аттачи (можно в массив копить ссылки), а потом после него 1 раз их просканировать и консолидировать в общую книгу?

    Тем более, что у вас там походу простая поколоночная сборка - можно вообще в итоговой книге формулами связи собрать, а не тратить ресурсы на обработку "открой книгу-скопируй 3 цифры-закрой книгу".
                                
    Sheets("list1").Select
    Cells(i, 1).value = v_date
    Cells(i, 2).value = value
    Cells(i, 3).value = value1
    
    30 ноя 18, 15:28    [21750161]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    net_Alex_tut
    Member

    Откуда:
    Сообщений: 9
    ldfanate,

    спасибо за ответ

    можете пример скрипта кинуть
    6 дек 18, 12:10    [21755614]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    net_Alex_tut
    Member

    Откуда:
    Сообщений: 9
    ldfanate,

    насчет непрочитынных которые в ходе цикла становятся прочитанными и типа поэтому не читаются

    так когда я по нескольку раз скрипт запускаю то все непрочитанные дочитываются

    Вообщем покажите пожалуйста как
    " вычитать коллекцию перед for-each - т.е. сделать "снимок" в отдельную переменную. И по нему уже крутить цикл."
    скрипт пожалуста


    насчет
    "грузить все аттачи (можно в массив копить ссылки), а потом после него 1 раз их просканировать и консолидировать в общую книгу? "
    когда аттачей более 300 ? книга станет оооочень большая
    Подскажите лучше как считывать даные с книги не открывая ее
    7 дек 18, 08:03    [21756592]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    ldfanate
    Member

    Откуда:
    Сообщений: 145
    net_Alex_tut
    вычитать коллекцию перед for-each - т.е. сделать "снимок" в отдельную переменную. И по нему уже крутить цикл."


    dim snimok as Outlook.MailItems
    Outlook.MAPIFolder
    GetDefaultFolder(olFolderInbox)
    set snimok = olFolder.Items 'снимок (коллекция писем)
    For Each Item In snimok
    ...

    net_Alex_tut
    Подскажите лучше как считывать даные с книги не открывая ее


    Разные варианты:
    1 вариант - Формулами связей ессно. Откройте две книги, напишите формулу "=" и кликните мышкой в ячейку соседней книги, потом закройте соседнюю книгу. Увидите полный текст формулы ссылки, чтото типа ='путь\имясоседнейкниги.xls![Лист1]$A$1'.
    Вот в сводной книге можно макросом пробивать такие формулы ссылок, они вытянут из принятых почтой книг в сводную значения, не открывая книги. Потом заменять их на значения через
    Range.Copy
    Range.Pastespecial xlpastevalues
    application.cutcopymode=false

    Это хорошо работает, если исходные книги имеют статичную структуру (фиксированное кол-во строк и столбцов). А если у них ещё и имена файлов унифицированы (т.е. в почту шлют правильно прокодированные файлы одинаковой структуры, у которых в названии закодирован отправитель), - то можно вообще динамически текст формулы связи собирать формулой СЦЕПИТЬ() и оборачивать её формулой ДВССЫЛ(). Тогда формула в сводной таблице будет сама тянуться в нужный файл, который из почты сложится в каталог обработки.

    2 вариант - штатным экселевским инструментом "Консолидация". Особенно когда файлы одинаковые по структуре.

    3 вариант - через query (кодится вызов объекта QueryTable). Там можно наворотить очень сложные правила отбора из исходных книг (и он очень шустро работает на книгах большого объёма 10-50000 позиций), т.к. сводная экселина к экселине обращается не поячеечно, а как к таблице базы данных, через специальный диалект SQL. Там можно условия WHERE наворотить в полстраницы текста. Но там свои грабли, если исходные книги плохо структурированы, или если в одной колонке вперемешку числовые и текстовые значения лежат.
    7 дек 18, 15:58    [21757217]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    net_Alex_tut
    Member

    Откуда:
    Сообщений: 9
    ldfanate,

    Спасибо большое за совет и за Ваше время!
    Осталось мне найти время и поэксперементировать :)
    14 дек 18, 06:37    [21763594]     Ответить | Цитировать Сообщить модератору
     Re: Автоматическое чтение данных документов Excel из почты  [new]
    ldfanate
    Member

    Откуда:
    Сообщений: 145
    Ползи улитка на вершину фудзи :)
    14 дек 18, 07:35    [21763609]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft Office Ответить