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

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

Excel VBA Ignore one file whilst using Loop through a folder

问题

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

以下是宏的代码：

  1. Sub FileUpdate()
  2.     Application.AskToUpdateLinks = False
  3.     Application.ScreenUpdating = False
  4.     Application.Calculation = xlManual
  5.     FolderName = (Sheets("Filepaths").Range("c22"))
  6.     If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
  7.     Filename = Dir(FolderName & "*.xlsx")
  8.     Do While Len(Filename)
  9.         If Filename <> "要忽略的文件名.xlsx" Then ' 将要忽略的文件名替换成实际的文件名
  10.             With Workbooks.Open(FolderName & Filename)
  11.                 Call SheetUpdate
  12.             End With
  13.             ActiveWorkbook.Close SaveChanges:=True
  14.         End If
  15.         Filename = Dir
  16.     Loop
  17.     Application.AskToUpdateLinks = True
  18.     Application.Calculation = xlAutomatic
  19. 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:

  1. Sub FileUpdate()
  2.     Application.AskToUpdateLinks = False
  3.     Application.ScreenUpdating = False
  4.     Application.Calculation = xlManual
  5.     FolderName = (Sheets(&quot;Filepaths&quot;).Range(&quot;c22&quot;))
  6.     If Right(FolderName, 1) &lt;&gt; Application.PathSeparator Then FolderName = FolderName &amp; Application.PathSeparator
  7.     Filename = Dir(FolderName &amp; &quot;.xlsx&quot;)
  8.     Do While Len(Filename)
  9.         With Workbooks.Open(FolderName &amp; Filename)
  10.             Call SheetUpdate
  11.         End With
  12.         Filename = Dir
  13.         ActiveWorkbook.Close SaveChnges = True
  14.     Loop
  15.     Application.AskToUpdateLinks = True
  16.     Application.Calculation = xlAutomatic
  17. 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，您可能会对此帖子感兴趣。

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

  1. Sub FileUpdate()
  2.     Application.AskToUpdateLinks = False
  3.     Application.ScreenUpdating = False
  4.     Application.Calculation = xlManual
  5.     
  6.     Dim folderName As String
  7.     
  8.     folderName = ThisWorkbook.Worksheets("Filepaths").Range("C22").Value
  9.     
  10.     If Right(folderName, 1) <> Application.PathSeparator Then _
  11.         folderName = folderName & Application.PathSeparator
  12.     
  13.     Dim fileName As String
  14.     fileName = Dir(folderName, vbNormal)
  15.     
  16.     Do While fileName <> ""
  17.         If Not fileName = "要忽略的文件名" Then
  18.             With Workbooks.Open(folderName & fileName)
  19.                 Call SheetUpdate
  20.             End With
  21.             ActiveWorkbook.Close SaveChanges:=True
  22.         End If
  23.         fileName = Dir
  24.     Loop
  25.     Application.AskToUpdateLinks = True
  26.     Application.Calculation = xlAutomatic
  27. 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

  1. Sub FileUpdate()
  2.     Application.AskToUpdateLinks = False
  3.     Application.ScreenUpdating = False
  4.     Application.Calculation = xlManual
  5.     
  6.     Dim filderName As String
  7.     
  8.     folderName = ThisWorkbook.workshSheets(&quot;Filepaths&quot;).Range(&quot;c22&quot;).value
  9.     
  10.     If Right(folderName, 1) &lt;&gt; Application.PathSeparator Then _
  11.         folderName = folderName &amp; Application.PathSeparator
  12.     
  13.     Dim fileName As String
  14.     fileName = Dir(folderName, vbNormal)
  15.     
  16.     Do While fileName &lt;&gt; &quot;&quot;
  17.         If Not fileName = &quot;name of file to ignore&quot; Then
  18.             With Workbooks.Open(folderName &amp; fileName)
  19.                 Call SheetUpdate
  20.             End With
  21.             ActiveWorkbook.Close SaveChanges = True
  22.         End If
  23.         fileName = Dir
  24.     Loop
  25.     Application.AskToUpdateLinks = True
  26.     Application.Calculation = xlAutomatic
  27. End Sub

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

发表评论

匿名网友

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

确定