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]

You delete the file first if it's existed by using Kill command and then use SaveAs to save the file.

First you need to check if the file exists. Reference:

VBA Check if File or Folder Exists - Automate Excel

VBA allows you to check if a file or folder exists by using the Dir function. Using the Dir Command to Check If a File Exists As we mentioned in the introduction, the Dir function allows us to check if a selected file exists on the computer. Here is the code: Sub CheckFileExists () Dim strFileName…

www.automateexcel.com

 

[Razor_Rob]

Will I lose the data that's getting deleted? What I'm doing with the file is adding zero on a 9 digit number as you know with CSV once you open the file it removes the leading zeros. So the VBA code let's the user select the file that was recently opened and process it then "save" the changes automatically without creating a new file name.

 

[Zot]

Will I lose the data that's getting deleted? What I'm doing with the file is adding zero on a 9 digit number as you know with CSV once you open the file it removes the leading zeros. So the VBA code let's the user select the file that was recently opened and process it then "save" the changes automatically without creating a new file name.

Looks like you opened a file and define it as wbImport. Modified a worksheet defined as wsImport and then you want to save it

'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

In the part of your code above, your remarked

    'Close the Source Workbook
    'wbImport.Close SaveChanges:=True

That code will just close and save the wbImport like you intended to. Any reason? After that, you don't need this part already

   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

 

[Razor_Rob]

Excel still has the file open unless I save it as with user to select the file to overwrite.
But I want to open the file make changes and save (overwrite) with the same file without any user interaction.

 

[Zot]

Excel still has the file open unless I save it as with user to select the file to overwrite.
But I want to open the file make changes and save (overwrite) with the same file without any user interaction.

I believe the wbImport.Close True command will save and close the fileNameAndPath as you defined it as wbImport without interaction.

 

[Razor_Rob]

Hi
I just tested it again and the Excel Save As window prompts for a file name to save it as.

 

[Zot]

Hi
I just tested it again and the Excel Save As window prompts for a file name to save it as.

Do you have the Application.DisplayAlerts = False before the wbImport.Close True?

This will suppress the prompt.

VBA code to save workbook with no prompt

What's the syntax for saving an Excel workbook file so it saves over the existing same filename without prompting you? Can't seem to find it...thanks! The code I'm using to save the workbook is: ActiveWorkbook.SaveAs Filename:="C:\Data\testfile.xls", FileFormat:= _ xlNormal...

www.mrexcel.com

 

[Razor_Rob]

Yes

 

[Razor_Rob]

This code kinda works but not, the file gets overwritten by the date of modified in the file explorer and it doesnt show the Save As window prompt
but the file gets corrupted and cannot be opened anymore. The file still has the csv extension.
the error is
Excel cannot open the file 'xxx.csv' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

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
    
    wbImport.Close
    ActiveWorkbook.SaveAs Filename:=fileNameAndPath, FileFormat:=51