Excel VBA Ignore one file whilst using Loop through a folder

Question

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.

It looks like your `FolderName` includes the filename as well - otherwise adding only ".xlsx" to `Foldername` would return nothing ... maybe there is the problem already. The way to go is sth like `If not filename like "*XXX.xlsx"` ... — Ike, Jan 09 '23 at 11:42
If I remove the one problem document the rest of the macro runs and updates the other documents fine, so i'm hoping that's not causing an issue! I've tried using that If Not Filename, but I can't seem to get that right. — user20964314, Jan 09 '23 at 11:49
You should show us what you have tried - otherwise it is hard to help you. — Ike, Jan 09 '23 at 11:55
It's just been mostly trying to add 'If Not FileName.Name="*xxx*"' instead of searching for all files. I deleted all the failed attempts so I sadly don't have them. If that's shot myself in the foot i'll just have the macro move the problem spreadsheet away and put it back at the end — user20964314, Jan 09 '23 at 12:08
How can we identify this file? Is it the one containing this code? What is its name e.g. `Test.xlsx`? Or does it begin, contain or end with "AA" or something else? Also, you should post the SheetUpdate procedure to get fixed since it should at least have a worksheet argument. — VBasic2008, Jan 09 '23 at 12:37

score 0 · Accepted Answer · answered Jan 09 '23 at 12:46

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

Excel VBA Ignore one file whilst using Loop through a folder

1 Answers1