locked
ms word Macro to extract Great Plains Dynamics CRM 2015 Data RRS feed

  • Question

  • Hello,

    I have a macro in MS Word that I am trying to use from a remote server (URL)

    How do I reconfigure my datasource to utilize a url such as:

    http://555.555.00.000/ABCCRM/main.aspx#960021546

    Sub LoadOpportunityList()

    '

    ' LoadOpportunityList Macro

    '

    '

        Set opportunityList = ActiveDocument.SelectContentControlsByTitle("OpportunityNumber").Item(1)

        Dim cnn As New ADODB.Connection

        Dim rst As New ADODB.Recordset

        cnn.Open "Provider=SQLOLEDB;" & _

                "Data Source=ABCCRM\DB1;" & _

                "Initial Catalog=ABC_MSCRM;" & _

                "User Id=sa;" & _

                "Password=abc123"

        rst.Open "SELECT DISTINCT ws_opportunitynumber FROM Opportunity WHERE ws_opportunitynumber IS NOT NULL AND StateCode = 0 AND StepName = '2 - Estimating' Order By ws_opportunitynumber;", _

            cnn, adOpenStatic

        rst.MoveFirst

        With ActiveDocument.SelectContentControlsByTitle("OpportunityNumber").Item(1).DropdownListEntries

            .Clear

            Do

                .Add rst![ws_opportunitynumber]

                rst.MoveNext

            Loop Until rst.EOF

        End With

        rst.Close

        cnn.Close

        Set rst = Nothing

        Set cnn = Nothing

    End Sub

    Sub LoadOpportunityDetails()

    '

    ' LoadOpportunityDetails Macro

    '

    '

        Set opportunityList = ActiveDocument.SelectContentControlsByTitle("OpportunityNumber").Item(1)

        Call GetOpportunityDetails(opportunityList.Range.Text)

    End Sub

    Private Sub GetOpportunityDetails(OpportunityName As String)

        Dim cnn As New ADODB.Connection

        Dim rst As New ADODB.Recordset

        cnn.Open "Provider=SQLOLEDB;" & _

                "Data Source=Server1\DB1;" & _

                "Initial Catalog=ABC_MSCRM;" & _

                "User Id=sa;" & _

                "Password=abc123"

         rst.Open "SELECT TOP 1 * FROM WS_PROPOSALDATA Where ws_opportunitynumber ='" & OpportunityName & "';", _

            cnn, adOpenStatic

    '    rst.Open "SELECT TOP 1 Address1_Street1, Address1_Street2 FROM OpportunityBase Where Name ='" & OpportunityName & "';", _

            cnn, adOpenStatic

        rst.MoveFirst

        Set proposaldate = ActiveDocument.SelectContentControlsByTitle("ProposalDate").Item(1)

        Set projecttype = ActiveDocument.SelectContentControlsByTitle("ProjectType").Item(1)

        Set projectname = ActiveDocument.SelectContentControlsByTitle("ProjectName").Item(1)

        Set opportunitycontact = ActiveDocument.SelectContentControlsByTitle("OpportunityContact").Item(1)

        Set customerquoteto = ActiveDocument.SelectContentControlsByTitle("CustomerQuoteTo").Item(1)

        Set quotetoaddress1 = ActiveDocument.SelectContentControlsByTitle("QuoteToAddress1").Item(1)

        Set quotetoaddress2 = ActiveDocument.SelectContentControlsByTitle("QuoteToAddress2").Item(1)

        Set quotetocity = ActiveDocument.SelectContentControlsByTitle("QuoteToCity").Item(1)

        Set quotetostate = ActiveDocument.SelectContentControlsByTitle("QuoteToState").Item(1)

        Set quotetozip = ActiveDocument.SelectContentControlsByTitle("QuoteToZIP").Item(1)

        Set billtocustomer = ActiveDocument.SelectContentControlsByTitle("BillToCustomer").Item(1)

     '   Set billtoaddress1 = ActiveDocument.SelectContentControlsByTitle("BillToAddress1").Item(1)

     '   Set billtoaddress2 = ActiveDocument.SelectContentControlsByTitle("BillToAddress2").Item(1)

     '   Set billtocity = ActiveDocument.SelectContentControlsByTitle("BillToCity").Item(1)

     '   Set billtostate = ActiveDocument.SelectContentControlsByTitle("BillToState").Item(1)

     '   Set billtozip = ActiveDocument.SelectContentControlsByTitle("BillToZIP").Item(1)

        Set locationcustomer = ActiveDocument.SelectContentControlsByTitle("LocationCustomer").Item(1)

        Set locationaddress1 = ActiveDocument.SelectContentControlsByTitle("LocationAddress1").Item(1)

        Set locationaddress2 = ActiveDocument.SelectContentControlsByTitle("LocationAddress2").Item(1)

        Set locationcity = ActiveDocument.SelectContentControlsByTitle("LocationCity").Item(1)

        Set locationstate = ActiveDocument.SelectContentControlsByTitle("LocationState").Item(1)

        Set locationzip = ActiveDocument.SelectContentControlsByTitle("LocationZIP").Item(1)

        Set contactname = ActiveDocument.SelectContentControlsByTitle("ContactName").Item(1)

        Set contactemail = ActiveDocument.SelectContentControlsByTitle("ContactEmail").Item(1)

        Set contactphone = ActiveDocument.SelectContentControlsByTitle("ContactPhone").Item(1)

        Set servicemgrname = ActiveDocument.SelectContentControlsByTitle("ServiceMgrName").Item(1)

        Set servicemgremail = ActiveDocument.SelectContentControlsByTitle("ServiceMgrEmail").Item(1)

        Set servicemgrphone = ActiveDocument.SelectContentControlsByTitle("ServiceMgrPhone").Item(1)

        Set customerservicename = ActiveDocument.SelectContentControlsByTitle("CustomerServiceName").Item(1)

        Set customerserviceemail = ActiveDocument.SelectContentControlsByTitle("CustomerServiceEmail").Item(1)

        Set customerservicephone = ActiveDocument.SelectContentControlsByTitle("CustomerServicePhone").Item(1)

        Set technicianname = ActiveDocument.SelectContentControlsByTitle("TechnicianName").Item(1)

        Set technicianemail = ActiveDocument.SelectContentControlsByTitle("TechnicianEmail").Item(1)

        Set technicianphone = ActiveDocument.SelectContentControlsByTitle("TechnicianPhone").Item(1)

        Set Location = ActiveDocument.SelectContentControlsByTitle("Location").Item(1)

        Set ProposalNumber = ActiveDocument.SelectContentControlsByTitle("ProposalNumber").Item(1)

        proposaldate.Range.Text = rst![ws_proposaldate]

        projecttype.Range.Text = rst![ws_bidtype]

        projectname.Range.Text = rst![Name]

        opportunitycontact.Range.Text = rst![ws_endcustomercontactidname]

        customerquoteto.Range.Text = rst![QuotedToName]

        quotetoaddress1.Range.Text = rst![QuotedToaddress1_line1]

        quotetoaddress2.Range.Text = rst![QuotedToaddress1_line2]

        quotetocity.Range.Text = rst![QuotedToaddress1_city]

        quotetostate.Range.Text = rst![QuotedToaddress1_stateorprovince]

        quotetozip.Range.Text = rst![QuotedToaddress1_postalcode]

        billtocustomer.Range.Text = rst![BillToName]

     '   billtoaddress1.Range.Text = rst![BillToaddress1_line1]

     '   billtoaddress2.Range.Text = rst![BillToaddress1_line2]

     '   billtocity.Range.Text = rst![BillToaddress1_city]

     '   billtostate.Range.Text = rst![BillToaddress1_stateorprovince]

     '   billtozip.Range.Text = rst![BillToaddress1_postalcode]

        locationcustomer.Range.Text = rst![Location]

        locationaddress1.Range.Text = rst![ws_Address1]

        locationaddress2.Range.Text = rst![ws_Address2]

        locationcity.Range.Text = rst![ws_Addresscity]

        locationstate.Range.Text = rst![ws_Addressstate]

        locationzip.Range.Text = rst![ws_AddressZip]

        contactname.Range.Text = rst![MaintenanceSalesRepfullname]

        contactemail.Range.Text = rst![MaintenanceSalesRepemailaddress1]

        contactphone.Range.Text = rst![MaintenanceSalesReptelephone1]

        servicemgrname.Range.Text = rst![AreaServiceManagerfullname]

        servicemgremail.Range.Text = rst![AreaServiceManageremailaddress1]

        servicemgrphone.Range.Text = rst![AreaServiceManagertelephone1]

        customerservicename.Range.Text = rst![CustomerServiceRepfullname]

        customerserviceemail.Range.Text = rst![CustomerServiceRepemailaddress1]

        customerservicephone.Range.Text = rst![CustomerServiceReptelephone1]

        technicianname.Range.Text = rst![Technicianfullname]

        technicianemail.Range.Text = rst![Technicianemailaddress1]

        technicianphone.Range.Text = rst![Techniciantelephone1]

        Location.Range.Text = rst![Location]

        ProposalNumber.Range.Text = rst![ProposalNumber]

    '    Set street1 = ActiveDocument.SelectContentControlsByTitle("Address1").Item(1)

    '    Set street2 = ActiveDocument.SelectContentControlsByTitle("Address2").Item(1)

    '    street1.Range.Text = rst![Address1_Street1]

    '    street2.Range.Text = rst![Address1_Street2]

        rst.Close

        cnn.Close

        Set rst = Nothing

        Set cnn = Nothing

    End Sub


    John


    • Edited by vsla Tuesday, April 14, 2015 9:53 PM
    Tuesday, April 14, 2015 9:52 PM

All replies