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
