Automate Copilot in Excel

AFAIK, it is not possible currently to use VBA to directly open the side chat pane or pass a prompt into the user interface of Copilot in Excel. Microsoft does not expose the Copilot sidebar UI components to the VBA COM object model. However, if your goal is to achieve a "one-click" automation that passes a prompt and processes data using Microsoft's AI, then you should be able to to this by leveraging some alternative approaches.

One would involve the native =COPILOT() worksheet function, which evaluates natural language prompts directly inside cells. Instead of opening the chat sidebar, you can use a standard VBA macro to insert this formula into a specific cell and reference your data range. A basic implementation would look like Range("G1").Formula2 = "=COPILOT(""Summarize the trends in this data"", A1:E10)". Assigning this macro to a clickable shape or form button creates a single-click solution where Excel executes the prompt against your dataset and outputs the AI response directly into your spreadsheet grid.

Another approach is to utilize direct API calls via VBA to bypass the Excel interface entirely. You can use VBA's MSXML2.ServerXMLHTTP.6.0 object to send HTTP POST requests directly to an AI endpoint, such as the Microsoft Graph Copilot API or Azure OpenAI services. This method requires constructing a JSON payload containing your prompt, appending your API authorization headers, and using a script to send the payload. Once the server responds, your macro can parse the returned text and paste it into any designated cell or dialog box in your workbook.

If you intend to shift away form VBA, you might consider pairing Office Scripts with Power Automate. You can create a cloud flow triggered by a button within your Excel workbook. This flow can automatically extract the required context from your spreadsheet, pass the data directly to the Microsoft 365 Copilot connector using an automated cloud workflow, capture the generated output, and write the response back into the spreadsheet rows without any manual copy-pasting.

If you need to interact with the literal sidebar UI pane, you should be able to use the Application.SendKeys method to simulate keyboard shortcuts. This method forces Excel to open the search bar with "%q", types a string like "Chat with Copilot" followed by the enter key "~", waits for the pane to load using Application.Wait, and then attempts to paste or type your prompt. Keep in mind though that this approach is prone to breaking if system latency occurs or if the user clicks out of the window, and is generally not recommended for production environments.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin