freedom-man.com
2014-05-24

VBAからSalesforceのAPI叩いてみる。

Salesforceの導入支援とかしてると、VBAを使ってSalesforceのAPIを叩きたくなることは多々あります。

Excel内でやりたいこと、色々ありすぎて困ります。

業務用のツールとか作れば社内でモテること間違いなし！

ということで今回はVBAからSalesforceのAPI叩いてみることにします。

ちなみにForce.com Office Toolkitが有りますが、ツールキット依存になる上に、MetadataAPIとか叩けないので自前でAPIコールしてます。

ちなみにちなみにVBAレベル1のスライムベスなのでコードレベルはご了承ください。

1. SOAPでログイン

RESTでログインしても良いんだけど、その場合接続アプリケーション用意しないといけなくなって、やや面倒なのでSOAP使いました。

こんな感じのHTTP通信をすればOK

POST https://login.salesforce.com/services/Soap/u/30.0 HTTP/1.1
soapaction: ""
Content-Type: text/xml;charset=UTF-8
Host: login.salesforce.com

==以下Body==

 

SOAPメッセージBodyはこんな感じ

<?xml version="1.0" encoding="utf-8"?>
<env:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Body>
    <n1:login xmlns:n1="urn:partner.soap.sforce.com">
      <n1:username>*******</n1:username>
      <n1:password>******</n1:password>
    </n1:login>
  </env:Body>
</env:Envelope>

で、こんな感じのレスポンスが返って来ます

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns="urn:partner.soap.sforce.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
    <loginResponse>
      <result>
        <metadataServerUrl>
        https://********.salesforce.com/services/Soap/m/30.0/00D*********</metadataServerUrl>
        <passwordExpired>false</passwordExpired>
        <sandbox>false</sandbox>
        <serverUrl>https://******.salesforce.com/services/Soap/u/30.0/00D*********</serverUrl>
        <sessionId>00D*********</sessionId>
...

ここでsessionIdとかserverUrlとかmetadataServerUrlとかを取得して、後のMetadataAPIのコールに使用します。

VBAだとこんな感じ

Private Sub login()
    Dim sfc As SfdcSoapXml
    Set sfc = New SfdcSoapXml
    
    Dim body As String
    body = sfc.LOGIN
    body = Replace(body, "{{username}}", "*******")
    body = Replace(body, "{{password}}", "******")
    Dim response As String
    response = callSoap("https://login.salesforce.com/services/Soap/u/30.0", body)
    
    Dim oXml As DOMDocument
    Set oXml = createObject("MSXML.DOMDocument")
    oXml.LoadXML (response)
    
    Dim serverUrl As String
    serverUrl = oXml.getElementsByTagName("serverUrl").Item(0).Text
    
    Dim sessionId As String
    sessionId = oXml.getElementsByTagName("sessionId").Item(0).Text
    Dim orgId As String
    orgId = oXml.getElementsByTagName("organizationId").Item(0).Text

    'https://*.salesforce.com/services/Soap/u/のドメイン名まで取得
    Dim re As RegExp
    Dim mc As MatchCollection
    Set re = New RegExp
    re.Pattern = "^(https://[a-zA-Z0-9-\.]*)/"
    Set mc = re.Execute(serverUrl)
    Dim m As Match
    
    Dim instanceUrl As String
    instanceUrl = mc.Item(0).SubMatches.Item(0)
End Sub

Private Function callSoap(ByVal endpoint As String, ByVal body As String) As String
    Dim objXmlHttp As Object
    Set objXmlHttp = createObject("MSXML2.XMLHTTP")
    
    objXmlHttp.Open "POST", endpoint, False
    Call objXmlHttp.setRequestHeader("Content-Type", "text/xml;charset=UTF-8")
    Call objXmlHttp.setRequestHeader("SOAPAction", """""")
    
    objXmlHttp.send (body)
    
    callSoap = objXmlHttp.responseText
End Function

2. RESTでデータ取得

VBAでJSON扱うのは色々面倒らしいのでレスポンスはXMLで

HTTPはこんな感じ(Bodyはなし)

GET https://******.salesforce.com/services/data/v30.0/query/?q=SELECT%20id%20FROM%20Account HTTP/1.1
Content-Type: application/json
Authorization: Bearer 00D**********
Accept: application/xml
Host: ******.salesforce.com

レスポンスはこんな感じ

<?xml version="1.0" encoding="utf-8"?>
<QueryResult>
  <done>true</done>
  <records type="Account"
  url="/services/data/v30.0/sobjects/Account/001*****">
    <Id>001A000000fAXuvIAG</Id>
  </records>
  <records type="Account"
  url="/services/data/v30.0/sobjects/Account/001*****">
    <Id>001A000000fAXuwIAG</Id>
  </records>
...
  <totalSize>51</totalSize>
</QueryResult>

あとは頑張ってパースするだけ！

VBAだとこんな感じ

Dim objXmlHttp As Object
Set objXmlHttp = createObject("MSXML2.XMLHTTP")
objXmlHttp.Open "GET", "https://***.salesforce.com/services/data/v30.0/query/?q=SELECT id FROM Account")
Call objXmlHttp.setRequestHeader("Content-Type", "application/json")
Call objXmlHttp.setRequestHeader("Authorization", "Bearer " + sessionId)
Call objXmlHttp.setRequestHeader("Accept", "application/xml")

objXmlHttp.send
CallRestApi = objXmlHttp.responseText

3. SOAPでカスタムオブジェクト作る

前回fiddler使ってSOAPの中身を見たので、そのSOAPメッセージを書き換えて送信します。

POST https://*****.salesforce.com/services/Soap/m/30.0/00D******* HTTP/1.1
soapaction: ""
Content-Type: text/xml;charset=UTF-8
Host: *****.salesforce.com

==以下Body==

エンドポイントはmetadataServerUrlの値を書き換えてください。

でBodyは

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <SessionHeader xmlns="http://soap.sforce.com/2006/04/metadata">
      <sessionId>00D********</sessionId>
    </SessionHeader>
  </soap:Header>
  <soap:Body>
    <create xmlns="http://soap.sforce.com/2006/04/metadata">
      <metadata xsi:type="CustomObject">
        <fullName>VBA__c</fullName>
        <deploymentStatus>Deployed</deploymentStatus>
        <description>これはVBAから作ってるよ</description>
        <label>VBAミラクルオブジェクト</label>
        <nameField>
          <label>VBAミラクルラベル</label>
          <length>80</length>
          <type>Text</type>
        </nameField>
        <pluralLabel>VBAミラクルオブジェクツ</pluralLabel>
        <sharingModel>ReadWrite</sharingModel>
      </metadata>
    </create>
  </soap:Body>
</soap:Envelope>

RESTはHTTPヘッダにSessionIDを入れますが、SOAPはBodyのSOAPヘッダに入れます。

レスポンスは

<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://soap.sforce.com/2006/04/metadata">
  <soapenv:Body>
    <createResponse>
      <result>
        <done>false</done>
        <id>04sA0000005c*****</id>
        <state>InProgress</state>
      </result>
    </createResponse>
  </soapenv:Body>
</soapenv:Envelope>

VBAだとこんな感じ（ワークシートから作るデータ取得してます）

Dim ws As Worksheet
Set ws = Worksheets("CreateObject")
Dim createObjectBody As String
createObjectBody = sfc.CREATE_OBJECT
createObjectBody = Replace(createObjectBody, "{{sessionId}}", sessionId)
createObjectBody = Replace(createObjectBody, "{{fullName}}", ws.Cells(3, 2))
createObjectBody = Replace(createObjectBody, "{{deploymentStatus}}", "Deployed")
createObjectBody = Replace(createObjectBody, "{{description}}", ws.Cells(6, 2))
createObjectBody = Replace(createObjectBody, "{{label}}", ws.Cells(1, 2))
createObjectBody = Replace(createObjectBody, "{{nameField.label}}", ws.Cells(4, 2))
createObjectBody = Replace(createObjectBody, "{{nameField.length}}", "80")
createObjectBody = Replace(createObjectBody, "{{nameField.type}}", ws.Cells(5, 2))
createObjectBody = Replace(createObjectBody, "{{pluralLabel}}", ws.Cells(2, 2))
createObjectBody = Replace(createObjectBody, "{{sharingModel}}", ws.Cells(7, 2))
Debug.Print createObjectBody
Dim responseObjectBody As String
responseObjectBody = callSoap(instanceUrl & "/services/Soap/m/30.0/" & orgId, createObjectBody)
Debug.Print responseObjectBody

4. SOAPでカスタム項目を作る

HTTPヘッダは3と同じ。

Bodyは

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <SessionHeader xmlns="http://soap.sforce.com/2006/04/metadata">
      <sessionId>00D********</sessionId>
    </SessionHeader>
  </soap:Header>
  <soap:Body>
    <create xmlns="http://soap.sforce.com/2006/04/metadata">
      <metadata xsi:type="CustomField">
        <fullName>VBA__c.Mira__c</fullName>
        <defaultValue></defaultValue>
        <description>てす</description>
        <inlineHelpText>へる</inlineHelpText>
        <label>ミラ</label>
        <length>100</length>
        <type>Text</type>
      </metadata>
      <metadata xsi:type="CustomField">
...
    </create>
  </soap:Body>
</soap:Envelope>

VBAだとこんな感じ

Dim ws As Worksheet
Set ws = Worksheets("CreateField")
Dim createFieldsBody As String
createFieldsBody = sfc.CREATE_BASE
createFieldsBody = Replace(createFieldsBody, "{{sessionId}}", sessionId)
Dim createFields As String
createFields = ""
For rowNum = 4 To 7
    Dim createField As String
    createField = sfc.CREATE_TEXTFIELD_BASE
    createField = Replace(createField, "{{sobject}}", ws.Cells(1, 2))
    createField = Replace(createField, "{{fullName}}", ws.Cells(rowNum, 3))
    createField = Replace(createField, "{{description}}", ws.Cells(rowNum, 5))
    createField = Replace(createField, "{{inlineHelpText}}", ws.Cells(rowNum, 6))
    createField = Replace(createField, "{{label}}", ws.Cells(rowNum, 1))
    createField = Replace(createField, "{{length}}", ws.Cells(rowNum, 4))
    createField = Replace(createField, "{{type}}", ws.Cells(rowNum, 2))
    createFields = createFields & createField
Next

createFieldsBody = Replace(createFieldsBody, "{{metadatas}}", createFields)
Dim responseFieldBody As String
responseFieldBody = callSoap(instanceUrl & "/services/Soap/m/30.0/" & orgId, createFieldsBody)
Debug.Print responseFieldBody

こんな感じでExcelから簡単にSalesforceのレコードやメタデータを操作することができます。

VBA使って社内限定でモテるエンジニアになりましょー！

このエントリーをはてなブックマークに追加