How to Fix Excel VBA Shapes.AddPicture Error After Office 2208 Update
Question details
Fix the VBA Shapes.AddPicture method failing when passing an API URL that returns image data instead of a direct static image file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to insert an image into an Excel worksheet via an API endpoint, using ActiveSheet.Shapes.AddPicture with SaveWithDocument set to true.
- Observed behavior
- The macro code works perfectly in Excel version 2202 but throws an error in version 2208, failing to insert the image.
Ensure you have developer tools enabled in Excel, administrative access to modify your VBA macro code, and that you understand how to write or edit basic HTTP requests in VBA.
Save the API Image to a Local Temporary File First
Bypass the URL parsing issue in Shapes.AddPicture by explicitly downloading the image response to a local drive before inserting it into the worksheet.
Since Office update 2208, the Shapes.AddPicture method struggles to process API endpoints that return dynamic image streams instead of standard image file extensions. Downloading the payload to a local temporary file circumvents this limitation.
Use MSXML2.XMLHTTP or WinHttp.WinHttpRequest.5.1 in your VBA code to send a GET request directly to your API endpoint.
Use an ADODB.Stream object to read the responseBody from your API call, and use the SaveToFile method to save it as a local .jpg temporary file on your hard drive.
Modify the ActiveSheet.Shapes.AddPicture function in your macro to use the local file path instead of the API URL. Ensure SaveWithDocument remains set to true.
Clean up your local storage by using the Kill statement in VBA to delete the temporary image file after it has been successfully embedded into the active cell.
Contact Microsoft Support or Revert Office Build
If you are unable to modify the VBA macro code, report the bug to Microsoft Support or roll back to an earlier Office build like 2202.
Use WPS Office as a Stable Alternative for Macro Execution
If frequent Microsoft Excel updates unexpectedly break your existing macro workflows, consider switching to WPS Office. It provides a highly compatible environment with excellent support for VBA macros, offering a stable alternative that ensures your automated tasks run smoothly without sudden feature regressions.
- 1. Download and install WPS Office: Get the free WPS Office suite from the official website and follow the brief installation prompt.
- 2. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your .xlsm file containing the Shapes.AddPicture macro.
- 3. Enable macros and execute: Click 'Enable Macros' in the security warning banner, then run your VBA scripts smoothly in a stable environment.
Frequently Asked Questions
Why does Shapes.AddPicture fail with API URLs after the Office 2208 update?
Office updates starting from version 2208 altered how Excel parses URLs in the AddPicture method. It now struggles to process API endpoints that dynamically translate parameters and return an image stream rather than pointing to a static file extension.
Can I still use SaveWithDocument=True when using a local file?
Yes. When you download the API response to a local temporary file and pass that file path to Shapes.AddPicture, setting SaveWithDocument to True will successfully embed the image directly into your workbook, just as it did with the URL.
Will reverting to Excel version 2202 fix this VBA error automatically?
Yes, rolling back your Office installation to version 2202 will restore the previous AddPicture functionality. However, updating your VBA code to download the image locally is highly recommended to prevent your macro from breaking during future software updates.
