英文:
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("Filepaths").Range("c22"))
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
Filename = Dir(FolderName & ".xlsx")
Do While Len(Filename)
With Workbooks.Open(FolderName & 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("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 = "name of file to ignore" 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论