Vba excel soap запрос

Обновлено: 02.07.2024

If your spreadsheet needs to access constantly updated data, or if you need to access services hosted on another computer, Excel's Web Services support will enable you to get connected.

This tutorial uses Excel features that are available only in Excel XP and Excel 2003 on Windows. Earlier versions of Excel do not support this, and neither do current or announced Macintosh versions of Excel.

Making this work requires downloading the Office Web Services Toolkit. As its location has changed a few times, it's easiest to go to http://www.microsoft.com/downloads/search.aspx and search for "Office Web Services Toolkit". Separate versions are available for Office XP and Office 2003. You'll need to install this toolkit, using the directions that come with it, before proceeding with this tutorial.

Once you've installed the toolkit, you can start connecting your spreadsheet to web services. To get to the Web Service References Tool (its name inside of Excel), you'll need to select Tools » Macro » Visual Basic Editor. On the Tools menu of the VBE, you'll find Web Services References. Selecting this brings up the dialog box shown in figure.

The Microsoft Office Web Services Toolkit in action

Telling the Web Services Toolkit to generate code for a web service

Clicking the Add button will make Excel generate VBA code for the service, as shown in figure.

VBA code generated by the Web Services Toolkit for accessing the dns service

Next, close the VBE and set up a very simple spreadsheet such as the one shown in figure.

A spreadsheet for adding web services

To add a button for calling the service, display the Control toolbar by right-clicking a toolbar and choosing Control Toolbox from the pop-up menu. Click the button icon, and then click the spreadsheet wherever you want the button to go. Right-click the button, and choose Properties from the pop-up menu. Under Name, enter GetData ; under Caption, enter Get IP Address . Close the Properties dialog box, and your spreadsheet should look something like that shown in figure.

Spreadsheet with button for calling web services

To add the final piece, right-click the button you added and choose View Code. In the window that appears, enter this subroutine:

This code is pretty simple. It references the object the toolkit created for the web service, and creates variables for the name and IP address. It collects the name from cell B2, calls the web service with the name as an argument, and then puts the value returned into cell B3. Once you've entered this code and closed the VBE, you can leave design mode by making sure the triangle and ruler icon at the left of the Control toolbar isn't highlighted. The spreadsheet will now enable you to enter a domain name in cell B2. Clicking the Get IP Address button will put the IP address corresponding to that domain name in cell B3. Figures show this spreadsheet in action with different domain names.

A retrieved IP address

IP address resolution is one of the simpler services out there, but many times services this simple can be very useful in a spreadsheet - for instance, for currency converters, price retrieval, postal code processing, and much more. You don't even need to learn about SOAP or WSDL to use these services, as the Web Services Toolkit takes care of all of that for you.

A few caveats are worth mentioning, however. First, the computer has to be connected to a network for a web service to work. You probably don't want to create spreadsheets that depend heavily on web services if their users will be working on them at 30,000 feet and will be thoroughly disconnected. (Spreadsheets such as this one, which uses a web service to populate fields but doesn't need to be connected constantly, are probably OK.)

The other major issue with web services generally is that the field is in significant flux. At the time of this writing, SOAP had moved from Version 1.1 to 1.2, and a new version of WSDL was under development; what's more, many people feel UDDI might eventually be replaced with other technologies. For now, be certain to test the services you use, and keep an eye out for new versions of the Office Web Services Toolkit.

Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.

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

VBA и Python

VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.

Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.

В общем виде можно описать ситуацию через подобный график:

Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.

Если в силу разных причин возможности выбора нет, то и сравнивать нечего.

В пользу VBA

• Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
• Поддержка разных форматов MS Office. Самая большая проблема для внешних языков — это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA — это все "файл Excel", работа с которыми в целом одинаково хороша.
• Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
• Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит "установить" модель макроса в фон офиса.
• Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
• Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.

В пользу Python (и других внешних языков программирования)

• Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
• Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python — откровенно "на троечку".
• Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки — тоже неудобно.
• Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.

Кейсы

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

• Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
  
  • Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
  • Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
  • Мой выбор: Логика VBA + Python для мониторинга
    
    • Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
    • Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
    • Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
    • Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
    • Мой выбор: Python.
      
      • Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
      • Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
      • Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
      • Мой выбор: Python. Хотя API — простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
      • Здесь я выбирал очень долго, так как есть много альтернатив:
        
        • Сторонняя готовая система поручений
        • База данных с каким-то обработчиком
        • Access
        • Excel
        • Во-первых, Excel сам по себе является готовым UI для работы
        • Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
        • В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое

        Заключение

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

        Recently I have been involved with several integration projects between different EPM products. Initially it was quite challenging because as with any integration project the devil is always in the detail. The great thing with SAP EPM products is that there are web services for major processes and functions that need to be performed.

        I needed to invoke a web service for an EPM product but had to do it in SAP BPC NW. Now there is several methods to invoke a web service from BPC NW, but it wouldnt be transportable to the MS product. So the solution was to invoke it from the client machine using VBA because on the client machine it can invoke any web service. Because SAP BPC uses Microsoft Office, you can create custom macros and VBA code to execute specific functions and functionality.

        The answer to my particular challenge was to invoke a Web Service using custom VBA code embedded in an BPC Input Schedule and Report. Now there are several tutorials and links out on the internet which give you sample code to invoke a Web Service using VBA, but one of the major reasons for writing this blog is is to show how this approach can be used to solve several challenges and how powerful it can be.

        Imagine some of the following scenarios:

        • Invoking a FIM job from a click of a button on a report or input schedule.
        • Invoking a BPC web service to perform a specific action
        • Export data of BPC into another EPM application but simply invoking a web service to create a KPI in SSM

        If you are able to get the WSDL of the Web Service, you can build your SOAP message and invoke the web service to perform the desired function. This VBA code is used to call a SOAP Web Service. It is possible to invoke a REST Web Service which BPC 10 uses, I will do this in another blog detailing a scenario in which this code could be used to solve certain business requirements.

        VBA Code example : This is a simply Sub Routine in which will invoke a web service using the MSXML2 Object. This object is part of the Microsoft XML 6.0 library which is a pre-requisite for the EPM Add-In and should be installed on all client machines.

        Читайте также:

          
        • 1с не найдено связанное поле остатка для поля
          
        • Как зайти в биос на ноутбуке hp pavilion dv6
          
        • Интерфейсные механизмы 1с это
          
        • Прошить xbox 360 альметьевск
          
        • Как разработать свое решение на базе 1с