Share via

How can I get Excel to import data from a site using SSO to log in?

ALH 0 Reputation points
2025-12-10T22:35:34.54+00:00

My team has an important tool made of an Excel workbook running on a VBA script. Basically what this tool does is import data from our ServiceNow site and format it into a Word document or an Outlook email. A couple years ago, my organization pushed for everyone to move away from passwords and use single sign-on (SSO) for everything, so now that's how we get into ServiceNow. My team's tool still logs into ServiceNow the old-fashioned way, though, which means that any time I use it, I have to generate a temporary password, wait a bit for that password to sync to my account, then paste it into the site login popup in Excel and hope that the temporary password didn't mess up my network access or make my account get locked. Because this is such a pain, I'd like to modify the tool's VBA script to use SSO to get into ServiceNow instead. How can I do that?

Microsoft 365 and Office | Excel | For business | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sophie N 8,515 Reputation points Microsoft External Staff Moderator
    2025-12-11T01:38:31.6366667+00:00

    Dear @ALH,

    Thank you for contacting the Microsoft 365 Q&A forum community support team. Dealing with temporary passwords and sync delays just to run a critical tool is certainly not ideal. You've hit on a common challenge when legacy tools built with VBA/Excel need to adapt to modern security protocols like Single Sign-On (SSO). 

    Unfortunately, VBA and the standard Excel data connection tools (like the legacy Web Query) do not have built-in support for modern, browser-based Single Sign-On (SSO) or multi-factor authentication (MFA) processes which typically rely on protocols like OAuth 2.0 or SAML. 

    The SSO process requires interaction with a web browser to handle redirection, credentials, and tokens, which is beyond the scope of VBA's simple HTTP request capabilities or the Excel Web Query object. 

    To help me narrow down the cause, could you please provide the following details?  

    1. Do you have access to the ServiceNow API documentation for your SSO configuration?
    2. Which method of SSO does your organization use (e.g., OAuth, SAML)?
    3. Are you familiar with handling HTTP requests in VBA (using MSXML2.XMLHTTP or similar)?
    4. Have you tried accessing the ServiceNow API outside of Excel to understand the SSO flow?
    5. Is there a specific error you encounter when using the existing method or is it more about the process being cumbersome?

    In the meantime, please try the following official troubleshooting options which usually resolve issues with basic issues: 

     

    The core problem is that the SSO process requires a user to interact with a web page to authenticate, and VBA cannot natively handle this interactive, token-based process. 

    Step 1: Use the ServiceNow REST API 

    Since your goal is to import data from ServiceNow, the most modern, secure, and reliable approach is to connect directly to the ServiceNow REST API instead of relying on screen scraping the login page. 

    • ServiceNow APIs are designed for this exact purpose: fetching structured data programmatically. 
    • Instead of using your interactive SSO login, you should use API Keys, OAuth 2.0 Client Credentials, or a dedicated Integration User with a standard username/password (which may be excluded from the general SSO mandate) or a refresh token.

    Work with your ServiceNow Administrator to set up a dedicated "Integration User" (if allowed by your organization) or configure an OAuth 2.0 Client/Profile that is authorized to fetch the necessary data. This will provide you with a secure token you can pass directly in the VBA request header. 

    You would use the Microsoft XML, v6.0 (or similar) library in your VBA code to make GET requests to the ServiceNow API endpoints, passing the security token or credentials in the HTTP header. 

     

    Step 2: Power Query (Get & Transform) 

    If the data is simple enough, you might be able to bypass VBA altogether for the import step: 

    • Power Query (Data Tab > Get Data): Power Query (M Language) is the modern successor to Web Queries and has better handling for authentication, including the ability to connect to certain API endpoints and manage credentials more robustly than VBA. 
    • While it's better than VBA, it still often struggles with non-standard or highly complex SSO/MFA flows. However, if ServiceNow offers a standard Web API endpoint, Power Query can be configured to use a Web API Key or OAuth token via the advanced editor. 

     

    For more details, please refer to Microsoft’s official documentation: 

    Connecting to external data with Excel Online 

    I truly hope you understand that the initial steps I suggested may not have completely resolved the issue, as there might still be underlying causes, we haven't uncovered yet. Please know that I’m here with you every step of the way, ready to work together and do everything I can to help troubleshoot and find the best solution. 

    Please let me know if you have any further questions or if the problem persists after trying these solutions. Thank you for your patience and cooperation.  

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.