Macro Workbook Save As Excel Workbook

mrmdsims

New Member
Joined
Apr 8, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I have a macro-enabled workbook and would like to save it to SharePoint as a .xlsx file to run Power Automate on the file later. The typical File, Save As works but after the save you will have the .xlsx file loaded and not the .xlsm file. Basically, I am looking for a macro that saves the .xlsx file in the background and keeps the .xlsm file loaded. I do not believe the SaveCopyAs method will work with SharePoint?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
One example.
VBA Code:
'Save a copy of your current .xlsm Workbook to new a new .xlsx file format workbook while keeping original file open.
'General Example. You will need to modify to make it specific for your needs.

'Save WB to new format while keeping original file open
Sub SaveWB()
    Dim Folder As String, FileName As String, FilePath As String
    Dim DestWB As Workbook

    Folder = "D:\xOptimumC\tmp"     'Edit - folder must already exist
    FileName = "MyNewWorkbook.xlsm" 'Edit
    
    Folder = Trim(Folder)
    If Not Right(Folder, 1) = "\" Then
        Folder = Folder & "\"                         'add backslash if not present
    End If
    
    If InStr(FileName, ".") > 0 Then
        FileName = Left(FileName, InStr(FileName, ".") - 1)
    End If
    
    FilePath = Folder & FileName & ".xlsm"
    
    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs (FilePath)
    DoEvents
    Set DestWB = Application.Workbooks.Open(FileName:=FilePath)
    DoEvents
    
    
    DestWB.SaveAs FileName:=Folder & FileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook    'see XlFileFormat Enumeration for different formats
    DoEvents
    DestWB.Close False
    Kill FilePath
    
    Application.DisplayAlerts = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top