VBA to covert sheet into *.csv with capital letter file extension-CodePudding

I have been working with the VBA code below and the code worked fine (creating a file with a .csv extension.

Today, my firm had an Office 365 update and for some reason the same code without edit, creates a file with a .CSV extension making it unable to be read by certain progams (it needs to be edited with the lowercase extension to be used further).

What do I need to know/alter in my settings to make my code run like prior?

Sub CSV_transfer()
    Dim header As Range
    Dim rngToSave As Range

Application.DisplayAlerts = False

    Windows("file.xlsm").Activate
    
    'Add a new sheet to become your csv and paste data
    Sheets.Add After:=ActiveSheet
    
    Set header = Sheets("sheet1").Range("AY3:AY6")
        header.Copy
    ActiveSheet.Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Set rngToSave = Sheets("sheet1").Range("AX3:AX450")
        rngToSave.Copy
    ActiveSheet.Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
        
    'Move the sheet into it's own instance
    ActiveSheet.Move
    'Rename the tab
    ActiveSheet.Name = "export"

    'Save the WB as a CSV and close
    ActiveWorkbook.SaveAs FileName:="*path*\export", _
        FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    ThisWorkbook.Saved = True
    ActiveWindow.Close
    Application.DisplayAlerts = True
    
End Sub

CodePudding user response：

If you specify the file extension, this might solve your problem. Instead of using the default (now .CSV), try changing:

ActiveWorkbook.SaveAs FileName:="*path*\export"

to:

ActiveWorkbook.SaveAs FileName:="*path*\export.csv"