Close workbook and save as the same name (Overwrite)

[Razor_Rob]

My code opens a workbook and I want to close it with the same file name, no user entry (Save As), just overwrites on the same file.

Dim wbImport As Workbook
Dim wsImport As Worksheet
Dim cell As Range
Dim fileNameAndPath As Variant
Dim lDestLastRow As Long

    'Open a workbook
    fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select File To Be Opened")
    If fileNameAndPath = False Then Exit Sub
    'Workbooks.Open Filename:=fileNameAndPath
    Set wbImport = Workbooks.Open(Filename:=fileNameAndPath, ReadOnly:=True)
    Set wsImport = wbImport.Worksheets(1)
    
Application.EnableEvents = False

    'Find last row in the destination range based on data in column A
    lDestLastRow = wsImport.Cells(wsImport.Rows.Count, "A").End(xlUp).Row
    
    'Remove spaces from the mobile number
    With wsImport
        .Range("Q3:Q" & lDestLastRow).Replace " ", vbNullString, xlPart
    End With

    'Add Zero in front of the mobile number
    With wsImport
        .Range("Q3:Q" & lDestLastRow).NumberFormat = "@"      'format range as text
        For Each cell In .Range("Q3:Q" & lDestLastRow)
             cell.Value = Format(cell * 1, "0000000000")      'Convert each cell
        Next cell
    End With
    
    'Clear contents of existing data range
    wsImport.Range("AO3:AY" & lDestLastRow).ClearContents
    wsImport.Range("BB3:BF" & lDestLastRow).ClearContents
    
    'Close the Source Workbook
    'wbImport.Close SaveChanges:=True
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    'Set wbImport = Workbooks(fileNameAndPath & ".csv")
    Set wbImport = Workbooks(fileNameAndPath)
    If wbImport Is Nothing Then
        'On Error GoTo zero
        Exit Sub
    Else
        Workbooks(fileNameAndPath).Close SaveChanges:=True, Filename:=fileNameAndPath
    End If
    
    'ActiveWorkbook.SaveAs Filename:=fileNameAndPath, FileFormat:=51
    
    
    
    Application.DisplayAlerts = True
    
    
    MsgBox "Added Zeros in front of the Mobile Number and Deleted Employer Info not required", vbOKOnly

 

[Zot]

It is because you use FileFormat:=51. You should use FileFormat:=6 or could also be FileFormat:=xlCSV.

Here's your reference

XlFileFormat enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Razor_Rob]

Well I found out the issue, I couldnt believe it was so simple as having the open workbook as read only....
Thanks everyone

 

[Zot]

Well I found out the issue, I couldnt believe it was so simple as having the open workbook as read only....
Thanks everyone

I think I saw that too but since I usually use SaveAs not to alter the original, I did not pay much attention on it . Things happened sometimes.