0

I have a VBA application. In this i need to call (URL)other enterprise application, when call happens it goes to authenticate the user with login credentials. based authentication it will proceed to next process. For this authentication previously I used internet explorer browser but IE is going to close soon by Microsoft. so i want make this process excel adding. When the url called it need to call the adding in excel right need to show the login page of application once user loggedin it need to diapprear.

I have tried basic addin template development using Visual studio. I have explored in internet but those are directing me to do fresh login page and do so. buti dont want to do this, just when we call the url it goes to some browser(we should configure it) with login page that need to populate in excel sheet right side.

below are code snippets of same.

Home.js

'use strict';

(function () {

    Office.onReady(function () {
        // Office is ready
        $(document).ready(function () {
            // The document is ready
            $('#set-color').click(setColor);
        });
    });

    async function setColor() {
        await Excel.run(async (context) => {
            var range = context.workbook.getSelectedRange();
            range.format.fill.color = 'green';

            await context.sync();
        }).catch(function (error) {
            console.log("Error: " + error);
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
        });
    }
})();

addin manifest xml

 <Version>1.0.0.0</Version>
  <ProviderName>T2V</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <!-- The display name of your add-in. Used on the store and various places of the Office UI such as the add-ins dialog. -->
  <DisplayName DefaultValue="Infor M3" />
  <Description DefaultValue="A task pane add-in for Excel"/>
  <!-- Icon for your add-in. Used on installation screens and the add-ins dialog. -->
  <IconUrl DefaultValue="~remoteAppUrl/Images/Button32x32.png" />

  <SupportUrl DefaultValue="http://www.contoso.com" />
  <!-- Domains that will be allowed when navigating. For example, if you use ShowTaskpane and then have an href link, navigation will only be allowed if the domain is on this list. -->
  <AppDomains>
    <AppDomain>AppDomain1</AppDomain>
    <AppDomain>AppDomain2</AppDomain>
    <AppDomain>AppDomain3</AppDomain>
  </AppDomains>
  <!--End Basic Settings. -->
  
  <!--Begin TaskPane Mode integration. This section is used if there are no VersionOverrides or if the Office client version does not support add-in commands. -->
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>

Home.html

<body class="ms-font-m ms-welcome">
    <div id="content-header">
        <div class="padding">
            <h1>POC</h1>
        </div>
    </div>
    <div id="content-main">
        <div class="padding">
            <p>Choose the button below to set the color of the selected range to green.</p>
            <br />
            <h3>Sample testing</h3>
            <button class="ms-Button" id="set-color">Set color</button>
        </div>
    </div>
</body>

VBA code for calling URL but i need to replace it with adding ralted code.

Dim browser As clsIE
Set browser = New clsIE
        
With browser
.IE.Visible = True
.IE.Navigate URL
End With

As I am replacing authentication with IE browser with other browser i need to change this part

xxz
  • 11
  • 4
  • You have tagged this is VBA - where is your VBA code and what problem are you having? Have you tried anything yet? – SierraOscar Mar 22 '22 at 10:13
  • In VBA i am calling a URL from that i need this addin part for authentication of that URL related application. i gave description about this in questions please point me if wrong in explaining – xxz Mar 22 '22 at 10:27
  • @SierraOscar Edited my question with VBA code – xxz Mar 22 '22 at 10:32

1 Answers1

0

You can do this with the xmlhttp object. Here's an example of sending a post request, as you would want to do if you are sending any kind of credentials:

    Sub authenticate(username As String, password As String)

    Dim formdata As String
    
    formdata = "username=" & URLEncode(username)
    formdata = formdata & "&password=" & URLEncode(password)
    
    Dim xmlhttp As Object
    Dim myurl As String
    Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
    myurl = "https://your.authserver.com/authenticate.aspx"
    xmlhttp.Open "POST", myurl, False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlhttp.Send formdata
    
    Debug.Print xmlhttp.responseText

End Sub

The following SO answer will tell you about how to implement the URLEncode function I call.

How can I URL encode a string in Excel VBA?

Gove
  • 1,745
  • 10
  • 11
  • this looks helpfull, thanks Gove. but not sure how it goes bcz i dont have much details about the destination application except URL. I dont know how the username and password are using. we need to identify proper keys for login and password right. and also based on authentication there will be Yes/No dialog will appear. By taking all consideration i am thinking to develop a addin. Even I tried SeleniumBasic driver that is working fine with other browsers but this is a open source which is not recommended. – xxz Mar 23 '22 at 09:40
  • If you need help understanding what the web browser is sending to the server on a login, have a look here https://stackoverflow.com/questions/15603561/how-can-i-debug-a-http-post-in-chrome you are trying to get VBA to send a request that looks just like the request that comes from a regular browser. – Gove Mar 23 '22 at 11:10
  • If you are going to develop an add-in, there are a few different approaches. I'm familiar with writing an add-in using VBA and with using the Office-JS API (introduced in 2013). I developed an add-in that allows you to build apps using Office-JS API, without all the overhead. It's called JADE and is available for free in the Excel add-in store. It's another option. https://support.jsvba.com/?introduction – Gove Mar 23 '22 at 11:15
  • Thanks @Gove there ways really helpfull I will try either of the ways and get back. Thanks again – xxz Mar 24 '22 at 02:43