代码之家 › 专栏 › 技术社区 › Deepak

删除并新建同名工作簿

excel-2010 excel vba

Deepak · 技术社区 · 6 年前

我想创建一个名为“Land DE”的新工作簿。如果目录中已存在该文件,则必须在创建新文件之前自动将其删除。我尝试使用以下代码,但它不起作用。

Sub createwb()
    Workbooks.add
    Dim FSO
    Dim path As String
    Set FSO = CreateObject("Scripting.FileSystemObject")
    set path = "D:\Job\Land-DE.xlsx"
    If FSO.FileExists(path) Then
        FSO.DeleteFile path, True
    else
        ActiveWorkbook.SaveAs "D:\Job\Land-DE.xlsX"
    End If
End Sub

4 回复 | 直到 6 年前

MarcinSzaleniec 6 年前

为什么在保存之前必须将其删除?是否足够:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "D:\Job\Land-DE.xlsx"
Application.DisplayAlerts = True

Tom 6 年前

请看以下内容。这将检查是否存在具有中定义的名称的文件 path 如果是,请在再次保存之前将其删除。

Sub Createwb()
    Dim path As String

    path = "D:\Job\Land-DE.xlsx"

    If Dir(path) <> "" Then Kill path

    ActiveWorkbook.SaveAs path
End Sub

John Ink 6 年前

Sub createwb()
    Workbooks.Add
    Dim FSO
    Dim path As String
    Set FSO = CreateObject("Scripting.FileSystemObject")
    path = "D:\Job\Land-DE.xlsx"
    If FSO.FileExists(path) Then
        FSO.DeleteFile path, True
    End If
    ActiveWorkbook.SaveAs path
End Sub

有两件事: Set 用于设置对象。路径只有一个字符串变量。对象是一组函数和变量,就像您在其上创建的FSO对象一样。

接下来,您需要确保删除后进行保存,并且再次循环时,保存的最后版本尚未打开。如果是的话,您将因为试图保存当前打开的文件而被拒绝。

marc_s Anurag 6 年前

您的代码存在多个问题

set path = "D:\Job\Land-DE.xlsx" 'It is syntax error, you can only set object in VBA , string is not considered as object

FSO.DeleteFile path, True 'USE kill instead, better performance
ActiveWorkbook.SaveAs "D:\Job\Land-DE.xlsX" ' Do not user active keyword, always set the object

代码如下:

Sub createwb()

    Dim wbnew As Workbook
    Set wbnew = Workbooks.Add

    Dim path As String
    path = "D:\Job\Land-DE.xlsx"

    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
            If FSO.FileExists(path) Then
            On Error Resume Next
                Workbooks("Land-DE").Close False ' Close the workbook if open
            Kill path
            wbnew.SaveAs path
    Else
        wbnew.SaveAs path
    End If
End Sub

推荐文章