Converting XLSM to XLSX for Power Automate Workflows in Excel VBA

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

The Problem with Uploading XLSM Files to SharePoint

When working with Excel VBA, one of the common issues users face is converting file formats before uploading them to a SharePoint site. Power Automate workflows often do not support XLSM files, which can be frustrating when you need to upload these files as part of your automated process.

Why This Happens

The core issue lies in the file format compatibility between Excel and Power Automate. While VBA allows you to save a workbook with various file formats, Power Automate might not recognize or support the XLSM extension. The solution involves converting your XLSM files to XLSX before uploading them.

Step-by-Step Solution

Here’s how to tackle this issue step by step:

Open Your Excel Workbook: Start with the workbook you want to convert and upload.
Modify VBA Code for File Conversion:

Below is a sample VBA code that demonstrates how to rename, save as XLSX, and upload to SharePoint.

Sub SaveAsXLSXAndUpload() Dim wb As Workbook Dim filePath As String Dim newFileName As String


    ' Set the workbook you're working with

    Set wb = ThisWorkbook
    ' Define your file path and desired filename

    filePath = "C:\YourFolder\"  ' Update this to match your directory

    newFileName = "YourNewFilename.xlsx"  ' New name for XLSX format
    ' Save the workbook as XLSX

    wb.SaveAs Filename:=filePath & newFileName, FileFormat:=xlOpenXMLWorkbook

' Upload code here (omitted for brevity) End Sub

This script saves your active workbook in XLSX format. To upload it to SharePoint, you can use additional VBA functions or libraries that support HTTP requests.

Advanced Variation: Automating the Entire Process with CelTools

While you can do this manually, CelTools automates this entire process. With its advanced features, frequent users can handle these tasks more efficiently.

Common Mistakes and Misconceptions

File Path Errors: Ensure the file path is correct; incorrect paths will cause the code to fail.
File Format Enumeration: Make sure you use the correct FileFormat value for XLSX (51).
SharePoint Permissions: Ensure your account has permissions to upload files to SharePoint.

Example: Uploading with HTTP Requests in VBA

If you need more control over the upload process, consider using an HTTP request. Here’s a simplified example of how to upload a file via HTTP:

Sub UploadToSharePoint() Dim http As Object Dim filePath As String Dim boundary As String


    ' Create the HTTP object

    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    ' File path to your XLSX file

    filePath = "C:\YourFolder\YourNewFilename.xlsx"
    ' Open a POST request to SharePoint API

    http.Open "POST", "https://yoursharepointsite.com/_api/web/GetFolderByServerRelativeUrl('/sites/yoursite/Shared%20Documents')/Files/add(url='YourNewFilename.xlsx',overwrite=true)", False
    ' Add headers (authentication, content type)

    http.setRequestHeader "Content-Type", "application/json;odata=verbose"

    http.setRequestHeader "Accept", "application/json;odata=verbose"
    ' Send the request

    http.Send

' Check response status If http.Status = 201 Then MsgBox "File uploaded successfully!" Else MsgBox "Error uploading file: " & http.statusText End If End Sub

Enhanced Solution with CelTools

CelTools simplifies this process even further. With its advanced automation features, users can convert and upload files to SharePoint without writing extensive VBA code.

Conclusion: Combining Manual Skills with Specialized Tools

The combination of manual VBA coding and specialized tools like CelTools provides a robust solution for converting XLSM files to XLSX format and uploading them to SharePoint. This approach ensures compatibility with Power Automate workflows, streamlining your entire process.

Spreadsheet closeup