Preeti's Blog

Tag: Workbooks.Open

  • VBA Code To Create A New Excel File By Deleting The Existing File

    Sub CreateFile ( )

    If fso.FileExists(wbAddr + “Output.xls”) Then
            Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)
            Set wkOut = Workbooks(“Output.xls”)
            wkOut.Close
            fso.DeleteFile wbAddr + “Output.xls”
    End If
    Set wkOut = Workbooks.Add
    wkOut.SaveAs filename:=wbAddr + “Output.xls”
    Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)
    Set wkOut = Workbooks(“Output.xls”)

     End Sub

    What The Code Does?

    Each time you execute the program the file,  ” Output.xls” is created if it do not exist otherwise it’s deleted and then created.  In the If part to delete the file I have closed it because any open workbook cannot be deleted. Also before closing I have opened it because a wokbook is closed only when it is open. If the workbook is already open no error will occur if you again open it.   Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)  is done only to make sure the workbook is open before closing it.
     

  • VBA Code To Create An excel File

    Sub CreateNewFile ()

    Dim wkOut as Workbook
    Dim wbAddr as string 
    Dim fso
     ‘wbAddr holds the path where the file needs to be created
     ‘like wbAddr=”D:\VbaCodes” 
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set wkOut = Workbooks.Add
    ‘Output.xls is the file to be created. A file or workbook is same.
    wkOut.SaveAs filename:=wbAddr + “Output.xls”
    Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)
    Set wkOut = Workbooks(“Output.xls”)

    End Sub