xlsm file with vba code to save copy as xlsx on close

[mandy4514]

I have an xlsm file that I cannot open on excel online. So I am trying to create a code that every time I close the xlsm file it saves the information to a xlsx file that I can access on excel online.

I have the following code:

Sub SaveAs()

Dim FName As String
Dim FPath As String
Dim NewBook As Workbook

FPath = "C:\Users\arichards\Dropbox\Documents\Income vs Expens"
FName = "Income vs Expense" & Format(Date, "ddmmyyyy") & ".xlsx"

Set NewBook = Workbooks.Add

ThisWorkbook.Sheets("DataSort").Copy Before:=NewBook.Sheets(1)

If Dir(FPath & "" & FName) <> "" Then
MsgBox "File " & FPath & "" & FName & " already exists"
Else
NewBook.SaveAs Filename:=FPath & "" & FName
End If

End Sub



My issue with this code is error on every run that I cannot save to file type and save dialog appears. I want it to save automatically in the back ground. Help please?

 

[Fluff]

Try

Sub SaveAs()

Dim FName As String
Dim FPath As String
Dim NewBook As Workbook

FPath = "C:\Users\DaveC\OneDrive\"
FName = "Income vs Expense" & Format(Date, "ddmmyyyy") & ".xlsx"

Set NewBook = Workbooks.Add

ThisWorkbook.Sheets("Task").Copy Before:=NewBook.Sheets(1)

If Dir(FPath & "" & FName) <> "" Then
MsgBox "File " & FPath & "" & FName & " already exists"
Else
NewBook.SaveAs FileName:=FPath & FName, FileFormat:=51
End If

End Sub

 

[Leith Ross]

Hello mandy4514,

This VBA macro will remove all VBA code from the worksheets and the workbook and save a copy as an XLSX workbook. The copy is saved to the same folder as the original. You can easily modify the macro to save it somewhere else.

Sub RemoveAllMacros()


    ' Written:  Decmber 05, 2015
    ' Author:   Leith Ross
    ' Summary:  Removes all macro code from the workbook and saves it as an XLSX workbook.
    
    Dim Newname     As String
    Dim OldName     As String
    Dim VBCode      As Object
    Dim VBComp      As Object
    Dim VBProj      As Object
    
        Set VBProj = ThisWorkbook.VBProject
        
        With VBProj
            For Each VBComp In .VBComponents
                Select Case VBComp.Type
                    Case 1, 2, 3
                        VBProj.VBComponents.Remove VBComp
                    Case 100
                        Set VBCode = VBComp.CodeModule
                        VBCode.DeleteLines 1, VBCode.CountOfLines
                End Select
            Next VBComp
        End With
        
        OldName = ThisWorkbook.FullName
        Newname = Left(OldName, InStrRev(OldName, ".")) & "xlsx"
        
        Application.DisplayAlerts = False
            ThisWorkbook.SaveAs Newname, xlOpenXMLWorkbook
            ThisWorkbook.Close SaveChanges:=True
        Application.DisplayAlerts = True
        
        Kill OldName
        
End Sub