My Blog

Saving and Closing the Excel File (Save/Save As Method)

6

In continuation from here

There are two way to save the file:

  1. Save
  2. Save As

When you do a simple “Save” then the original file is saved with the changes that you made. Whereas in the “Save As” a copy of the original file is saved.

See the code snippets below.


Save

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some work

        '~~> Save the file
        xlWorkBook.Save()

        '~~> Close the file
        xlWorkBook.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

Save As

When you do a Save As, you have to specify the new file name and the path for the file. Additionally you can also specify a password to protect the file so that the user has to supply a password to open the file. You can also specify if the file is Write-Protected so that the user has to enter the password before modifying it. If you see the picture below then you can see the various settings that you can have access to)

Save As…

So a typical code snippet could be like this:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyPassword As String = "Password"

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some work

        '~~> Save As file
        xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51, Password:=MyPassword, _
        WriteResPassword:=MyPassword, ReadOnlyRecommended:=True, CreateBackup:=False)

        '~~> Close the file
        xlWorkBook.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

These are the file formats in Excel 2007/2010:

  1. 50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro’s, xlsb)
  2. 51 = xlOpenXMLWorkbook (without macro’s in 2007-2010, xlsx)
  3. 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2010, xlsm)
  4. 56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)
  1. kavta
    kavta11-01-2012

    i am not able to add the xlapp as excel application

    • Siddharth Rout
      Siddharth Rout11-01-2012

      And why are you not able to add? What is the problem that you are facing?

  2. christilda
    christilda02-28-2013

    Dim XLApp
    Dim wr
    Set XLApp = CreateObject(“Excel.Application”)
    xlapp.visible = true
    mypath = “C:\Users\88888888888888888888\Desktop”
    fname = dir(“C:\Users\88888888888888888\Desktop\Book2.xlsm”)
    do while len(fname) > 0
    set wr = xlapp.workbooks.Open(mypath & fname)
    wr.refreshall
    wr.Save
    wr.Close
    fname = dir
    xlapp.Quit
    End If
    set wr = nothing
    set xlapp = nothing

    can u please help me to automatically save changes and replace the contents in the same file instead of asking new location….

    • Siddharth Rout
      Siddharth Rout02-28-2013

      Your Do While is missing “loop”. Also check if you have Rights to write to the desktop? BTW Are you trying to do this?

      Sub Sample()
      Dim XLApp As Object, wr As Object

      Set XLApp = CreateObject(“Excel.Application”)
      XLApp.Visible = True

      mypath = “C:\Users\88888888888888888888\Desktop”
      fname = “Book2.xlsm”

      If Len(Trim(Dir(mypath & “\” & fname))) <> 0 Then
      Set wr = XLApp.Workbooks.Open(mypath & “\” & fname)
      wr.RefreshAll
      wr.Save
      wr.Close
      Set wr = Nothing
      XLApp.Quit
      Set XLApp = Nothing
      End If
      End Sub

  3. christilda
    christilda02-28-2013

    Set objXL = WScript.CreateObject(“Excel.Application”)
    objXL.Application.Visible = True
    objXL.workbooks.open “C:\Users\9999999999999999999\Desktop\Book2.xlsm”
    objXL.Application.Run “macro1”
    objXL.Save (“C:\Users\99999999999999999\Desktop\Book2.xlsm”)
    objXL.Application.Quit
    Set objXL = Nothing

    hi help me in this tooo//…the same problem…

    • Siddharth Rout
      Siddharth Rout02-28-2013

      See my other reply. You have to create object as I did and then save the file. Also ensure that you have Rights to write to that location.