Excel VBA在循环文件夹时忽略一个文件

huangapple go评论69阅读模式
英文:

Excel VBA Ignore one file whilst using Loop through a folder

问题

我有一个宏,可以查找一个文件夹并以相同的方式更新其中的所有Excel文档,但是我需要它忽略一个文件,但我无法实现。

以下是宏的代码:

Sub FileUpdate()
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    FolderName = (Sheets("Filepaths").Range("c22"))
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    Filename = Dir(FolderName & "*.xlsx")
    Do While Len(Filename)
        If Filename <> "要忽略的文件名.xlsx" Then ' 将要忽略的文件名替换成实际的文件名
            With Workbooks.Open(FolderName & Filename)
                Call SheetUpdate
            End With
            ActiveWorkbook.Close SaveChanges:=True
        End If
        Filename = Dir
    Loop
    Application.AskToUpdateLinks = True
    Application.Calculation = xlAutomatic
End Sub

在上面的代码中,我添加了一个条件来检查文件名是否与要忽略的文件名匹配。如果文件名匹配要忽略的文件名,则不会运行SheetUpdate宏,从而实现忽略特定文件的功能。

请将“要忽略的文件名.xlsx”替换为您要忽略的实际文件名。这应该解决您的问题。

英文:

I have a macro that finds a folder and updates all Excel documents within in the same way, however I need it to ignore one but I can't make it happen.

Here's the macro:

Sub FileUpdate()
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    FolderName = (Sheets(&quot;Filepaths&quot;).Range(&quot;c22&quot;))
    If Right(FolderName, 1) &lt;&gt; Application.PathSeparator Then FolderName = FolderName &amp; Application.PathSeparator
    Filename = Dir(FolderName &amp; &quot;.xlsx&quot;)
    Do While Len(Filename)
        With Workbooks.Open(FolderName &amp; Filename)
            Call SheetUpdate
        End With
        Filename = Dir
        ActiveWorkbook.Close SaveChnges = True
    Loop
    Application.AskToUpdateLinks = True
    Application.Calculation = xlAutomatic
End Sub

The files do all need to be in the same folder, so it's unfortunate that only one can't have this macro running through it.

For clarification in case, the filepath to the folder is in a sheet called 'Filepaths' and it calls another macro called 'SheetUpdate'.

I have tried using 'If Not' to kind of make it run backwards- so it updated all documents that didn't have the name of the file I wanted to avoid.
I also tried using 'Case' in different variations, but that was more of an attempt of putting other things together that I had seen work.

I'm very beginner to this and I've done a lot of searching for an answer, but i'm struggling for this specific one.

答案1

得分: 0

从您的描述中并不完全清楚SheetUpdate的工作方式,但似乎您正在使用ActiveSheet引用。如果您计划将来继续使用VBA,您可能会对此帖子感兴趣。

对您可能有用的一个解决方案可能如下所示:

Sub FileUpdate()
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    Dim folderName As String
    
    folderName = ThisWorkbook.Worksheets("Filepaths").Range("C22").Value
    
    If Right(folderName, 1) <> Application.PathSeparator Then _
        folderName = folderName & Application.PathSeparator
    
    Dim fileName As String
    fileName = Dir(folderName, vbNormal)
    
    Do While fileName <> ""
        If Not fileName = "要忽略的文件名" Then
            With Workbooks.Open(folderName & fileName)
                Call SheetUpdate
            End With
            ActiveWorkbook.Close SaveChanges:=True
        End If
        fileName = Dir
    Loop
    Application.AskToUpdateLinks = True
    Application.Calculation = xlAutomatic
End Sub

请注意,我已经修复了代码中的一些拼写错误和格式问题。

英文:

From your description it is not completely clear how SheetUpdate works but it seems like you are using ActiveSheet references. You might be interested in this post if you plan on continuing to use VBA in the future.

A solution that might work for you could look like this

Sub FileUpdate()
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    Dim filderName As String
    
    folderName = ThisWorkbook.workshSheets(&quot;Filepaths&quot;).Range(&quot;c22&quot;).value
    
    If Right(folderName, 1) &lt;&gt; Application.PathSeparator Then _
        folderName = folderName &amp; Application.PathSeparator
    
    Dim fileName As String
    fileName = Dir(folderName, vbNormal)
    
    Do While fileName &lt;&gt; &quot;&quot;
        If Not fileName = &quot;name of file to ignore&quot; Then
            With Workbooks.Open(folderName &amp; fileName)
                Call SheetUpdate
            End With
            ActiveWorkbook.Close SaveChanges = True
        End If
        fileName = Dir
    Loop
    Application.AskToUpdateLinks = True
    Application.Calculation = xlAutomatic
End Sub

huangapple
  • 本文由 发表于 2023年1月9日 19:37:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/75056720.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定