analitica.jimdo.com

Интеграция с другими программами - Макросы в Excel

9-13 минут

---

Не стоит забывать и о возможностях интеграции VBA с другими приложениями. Монополия это конечно плохо, но в данном случае именно монопольное положение Microsoft дало нам столько возможностей по интеграции различных приложений друг в друга.

Итак, с чем и как можно интегрировать VBA?

Текстовый файл

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

    ‘ объявляем о начале использования такого типа объекта

    Set fso = CreateObject("scripting.filesystemobject")

    ‘ открываем нужный файл

    Set tS = fso.OpenTextFile(full_name_of_txt-file, 1, True)

    ‘ присваиваем стринговой переменной весь текст, содержащийся в файле

    msg_text = tS.ReadAll

    ‘ закрываем текстовый файл

    tS.Close

Просмотр файлов в папке

Средствами VBA можно получить список файлов, находящихся в папке, причем не только файлов MS Office, но и всех остальных. Это бывает нужно, например, чтобы открыть все файлы и напечатать, открыть несколько стандартных файлов и агрегировать хранящуюся в них информацию или чтобы просто получить список имеющихся файлов. Для этого можно воспользоваться функцией Dir(путь, тип файла). Функция возвращает имена файлов из папки.

При первом запуске функция выдает имя первого по порядку подходящего файла из указанной папки. Важно указывать \ на конце папки, иначе функция выдаст ошибку либо пустое значение. После того, как мы получили имя файла, мы можем его открыть и работать с ним. Потом нам нужно открыть следующий файл из папки. Для этого снова вызываем Dir, но без аргументов. А когда файлы закончатся, функция выдаст пустое значение - "".

Работу функции проще понять на примере.

Допустим, в папке "C:\Documents and Settings\" лежит три Excel-файла, и мы хотим их открыть.

Тогда наш макрос будет выглядеть так:

       dr = Dir("C:\Documents and Settings\", vbNormal)

       Do While dr <> ""

              Workbooks.Open("C:\Documents and Settings\" & dr)

             dr = Dir()

       Loop

SQL-server

Подключение VBA к SQL может потребоваться в куче разных мест. Например у меня оно понадобилось совсем внезапно: caption полей в кубе и их key не совпадали. То есть когда я хотела проставить какие-то вещи, я имела их "человеческий" список, а в макрос мне нужно было подставлять "компьютерный". Соответствие одного с другим я и получала в базе.

Итак приступим.

Сначала запомним в переменную строку подключения, которую будем использовать потом. Её легче всего получить, настроив такое же подключение в Excel и потом просто скопировать оттуда готовую. Она будет примерно такая:

    cnstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MyServerName;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MyCompName;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MyDatabaseName"

     ' делаем коннект

    Set cn = New ADODB.Connection

    ' указываем, что в этом коннекте у нас будет recordset с именем rs

    Set rs = New ADODB.Recordset

    ' открываем коннект с нашей строкой подключения

    cn.Open cnstr

Затем получаем данные. Обратите внимание, что так как скрипт это всего лишь строка, вы можете подставлять туда разные переменные и др., полученные до этого в коде.

    rs.Open «select * from table», cn

Пробег по строчкам recordset-а описывается достаточно странно, работать с ним неудобно. Гораздо удобнее перезаписать данные в массив VBA. Поэтому бежим по строчкам recordset-а

    i = 0

    Do While Not rs.EOF

        ReDim Preserve arr(i)

        arr(i) = rs.Fields(0).Value

        rs.MoveNext

        i = i + 1

    Loop

Если вам не нужно получать данные, а нужно просто выполнить код, то можно воспользоваться командой execute

    StrSql = "insert into ReportByIdList(ID,Name)      values(1, report)"

    cn.Execute StrSql

LotusNotes и другие Lotus-приложения.

LotusNotesявляется почтовым клиентом, однако, позволяет делать еще массу других вещей, например, он также является браузером и календарем. Можно совершать различные действия в Lotus, не выходя из Excel’я или Word’а. Скрипт достаточно труден, а подсказок в интернете не так уж и много, но иногда результат оправдывает все потраченные силы.

Начать сессию можно двумя способами.

Способ 1. Надо поставить галочку в Tools - References на объекты Лотуса, чтоб VBA подгрузил команды и справку Lotus'а. Лучше только одну галку, а то всё будет дублироваться

    Dim session As New NotesSession

    session.Initialize

Способ 2 (в дальнейшем все команды представлены для способа 2)

    Dim NotesSession As Object

    Dim NotesDB As Object

    Dim NotesDOC As Object

    Dim rrr As Object

    Dim vview As Object

    Set NotesSession = CreateObject("Notes.Notessession")

Далее нужно обозначить, в какой базе данных Лотуса мы будем работать. Можно работать в текущей базе, т.е. в последней открытой

    Setdb = NotesSession.CurrentDatabase

Или обозначить базу, используя имя сервера и путь к файлу, который хранит базу

    Set db = NotesSession.GetDatabase("Server22 ", "organization.nsf")

Такая база может быть не открыта, тогда её надо открыть (уточнить, open или openmail)

    If db.IsOpen = False Then db.OPENMAIL

В базах есть разные представления данных (например, входящие письма, исходящие письма и др). Нужно выбрать, из какого представления нам нужны документы. К представлению можно обратиться по имени

    Set vview = db.GetView("($Sent)")

А можно перебрать все имеющиеся в базе представления (здесь именно View, а не абы что, иначе не работает)

    For Each View In db.Views

        MsgBox (View.Name)

    Next

Далее из выбранных вьюх можно вытащить помещенные в них документы

    Set rrr = vview.GetFirstDocument

или соответственно

    Set rrr = View.GetFirstDocument

Чтобы перейти к следующему документу, пишем

    Set rrr = vview.GetFirstDocument(rrr)

или

    Set rrr = View.GetFirstDocument(rrr)

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

Значение тех или иных полей можно получить, например, так

    MsgBox (rrr.GetItemValue("Name")(0)) 

    MsgBox (rrr.GetItemValue("Branch")(0))

    MsgBox (rrr.GetItemValue("Sendto")(0))

Поля - это массивы. Отсюда и (0) в конце - это просто обращение к конкретному элементу массива. Каков размер этих массивов можно узнать функцией ubound. Поэтому тот же результат можно получить через присваивание массиву:

    Dim doc() As String

    doc = rrr.GetItemValue("$Name")

    MsgBox (doc(0))

Можно создавать новые документы (по крайней мере письма можно, остальное, возможно, зависит от наличия админских прав). Пример создания письма:

    Set NotesDOC = NotesDB.CreateDocument

    Call NotesDOC.ReplaceItemValue("From", "адрес отправителя")

        With NotesDOC

          .Form = "Memo"

          .sendto = "тут адрес получателя"

          .Principal = "тут адрес отправителя"

          .subject = "Тема письма"

          .body = "Текст письма"

          .SAVEMESSAGEONSEND = True

          .PostedDate = Now()

          .Send 0

        EndWith

Можно делать вложения файлов в письмо. Для этого после создания письма нужно определить файлы, которые будут вложены. Получим:

   Set NotesDOC = NotesDB.CreateDocument

   ' первое вложение

      Set NotesAttachment = NotesDOC.CreateRichTextItem("stFileName")

      Set NotesEmbedObject = NotesAttachment.EmbedObject(1454, "", stFileName, stFileName2)    '1454 - параметр lotus (attachment)

      ' второе вложение

      Set NotesAttachment2 = NotesDOC.CreateRichTextItem("stFileName2")

      Set NotesEmbedObject2 = NotesAttachment.EmbedObject(1454, "", stFileName2)

      и т.д.

После этого идут все те же строки отправки:

    Call NotesDOC.ReplaceItemValue("From", "адрес отправителя")

        With NotesDOC

          .Form = "Memo"

          .sendto = "тут адрес получателя"

          .Principal = "тут адрес отправителя"

          .subject = "Тема письма"

          .body = "Текст письма"

          .SAVEMESSAGEONSEND = True

          .PostedDate = Now()

          .Send 0

        EndWith

Отправлять письмо сразу нескольким получателям можно, записав адреса получателей в массив и указав этот массив в строке sendto. Например:

   resipients = Split("test1, test2, test3")

   .SendTo = resipients

или 

   resipients[0] = "test1"

   resipients[1] = "test2"

   resipients[2] = "test3"

   .SendTo = resipients

Свойства баз Lotus

Сервер и путь к файлу базы данных можно посмотреть где-то в свойствах, а можно сначала войти в неё через

Setdb = NotesSession.CurrentDatabase,

а потом вытащить её свойства:

    MsgBox (db.Server)

    MsgBox (db.FilePath)

Есть еще другие свойства, и если поставлена галочка в Tools - References на объекты Лотуса, то их можно смотреть в ObjectBrowser.

Кроме того, в параметрах Excel(Файл -> Параметры) есть некоторые настройки, связанные с Lotus, которые могут быть полезны.