[VBA] Check if a cell content has a match in a folder and if yes, open file

[Eawyne]

Hi all =)

Here's what I need :

=> I enter a value in a cell (usually, a number, like 141KL540024)
=> There's an autocheck in a folder to determine if there exists a corresponding Powerpoint file (it would be 141KL540024.pptx)
=> If the file exists, a message box will warn us, and ask if we want to open said file : if yes it opens it ; if no, nothing happens

I'm a relative newbie in terms of VBA ; I've already tinkered a few things so far, and I've managed to locate some old codes that were previously used in another file, but in that case, the code pointed to a sheet in the same workbook.

Sub Affichagedérogation()
Application.ScreenUpdating = False
On Error GoTo ErrorHandler

Dim masque As Variant
' copie la cellule sur elle même pour que l'auto recipe checker fonctionne
    ActiveCell.Offset(0, 0).Select
    'ActiveCell.Select
    Selection.Copy
    ActiveCell.Select
    ActiveSheet.Paste

masque = ActiveCell.Value
 
 Sheets("Liste").Select
    Cells.Find(What:=masque, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
   MsgBox ("Il y a une fiche de dérogation pour ce masque.")
Selection.Hyperlinks(1).Follow
Sheets("LSTAR03").Select

ErrorHandler:
Windows("STARLights3 2018.xlsm").Activate
Sheets("LSTAR03").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've tried to come up with something, using the DIR variable, but here's where my limited knowledge comes to a halt. I can't manage to assign a "value" that I can use throughout the code to be referenced without having to name the value to a corresponding file in my folder ; the value should always be what's in the cell ; it appears the "masque = ActiveCell.Value" command does just that, but...

Thanks for any help !

 

[John_w]

Try this Worksheet_Change event handler, which runs automatically when the user types or pastes a value in a cell. The code must be pasted into the sheet module of the sheet where you enter the cell value. You can open this module by right-clicking the sheet's tab and clicking 'View Code'.

=> I enter a value in a cell (usually, a number, like 141KL540024)

Change "$A$1" in the code to the absolute address of the cell.

=> There's an autocheck in a folder to determine if there exists a corresponding Powerpoint file (it would be 141KL540024.pptx)

Change the searchFolder path in the code to the folder to search.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim searchFolder As String, fileName As String
    Static PowerPointApp As Object
    
    searchFolder = "C:\path\to\folder\"
    
    If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
    
    If Target.Address = "$A$1" Then
        fileName = Dir(searchFolder & Target.Value & ".pptx")
        If fileName <> vbNullString Then
            If MsgBox(fileName & " exists.  Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject("PowerPoint.Application")
                PowerPointApp.Presentations.Open searchFolder & fileName
            End If
        End If
    End If
    
End Sub

 

[Eawyne]

Wow that's cool, it works perfectly fine, and so smoothly.

But I realize I forgot an important info =( The input for those numbers will be in a whole column, so it should be within a range, and not with a single target. Again, I tried to find out something with the Range variable, but to no avail... It sucks being so limited.

 

[Eawyne]

Wow that's cool, it works perfectly fine, and so smoothly.

But I realize I forgot an important info =( The input for those numbers will be in a whole column, so it should be within a range, and not with a single target. Again, I tried to find out something with the Range variable, but to no avail... It sucks being so limited.

Oh never mind, I found it !

Thanks so much for your help !!

 

[Eawyne]

Ah, I stumbled upon an unforseen problem : normally, the files are all named with only the string of 12 numbers , but of course, some are not Is there a way to use wildcards in this variable ?

 

[John_w]

But I realize I forgot an important info =( The input for those numbers will be in a whole column, so it should be within a range, and not with a single target.

The modified code below looks for a changed cell in column A from row 2.

Ah, I stumbled upon an unforseen problem : normally, the files are all named with only the string of 12 numbers , but of course, some are not Is there a way to use wildcards in this variable ?

Yes, we can use wildcards (the * character, which matches zero or more characters) in the Dir function. This will match a full or partial file name entered in column A.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim searchFolder As String, fileName As String
    Static PowerPointApp As Object
    
    searchFolder = "C:\path\to\folder\"
    
    If Right(searchFolder, 1) <> "\" Then searchFolder = searchFolder & "\"
    
    If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub
    
    If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 2 Then
        fileName = Dir(searchFolder & "*" & Target.Value & "*.pptx")
        If fileName <> vbNullString Then
            If MsgBox(fileName & " exists.  Do you want to open it?", vbYesNo + vbInformation, "Open PowerPoint file?") = vbYes Then
                If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject("PowerPoint.Application")
                PowerPointApp.Presentations.Open searchFolder & fileName
            End If
        End If
    End If
    
End Sub

 

[Eawyne]

Excellent =) I had found a way to do a search in a column :

If Target.Column = 1 Then

but I suppose a Range is suitable as well ; I'll see which suits me more, as both work fine.

And thanks a lot for the wildcard : I had tried to put it in the file name, but hadn't considered the "*" ahead of it.

Again, thanks a thousand lot. Your code is simple and elegant, and it saves a ton compared to the older method that was used and looked heavily clunky.

 

[Eawyne]

Of course, there's always a problem that comes when all seems clear !

When I delete the content of a cell now, the macro yields a result : 1101A23B0053_contabord.pptx"

Originally, the name was 1101A23B0053 conta bord, so I renamed it to remove the spaces, but it still comes up.

I tried to find a way to search in the cell only if it was empty with an IsEmpty variable, but then the code isn't correct as it should be in ( ) , which isn't the case here. I then tried to adapt the code with a <>"" but then it doesn't work anymore...

 

[Eawyne]

Of course, there's always a problem that comes when all seems clear !

When I delete the content of a cell now, the macro yields a result : 1101A23B0053_contabord.pptx"

Originally, the name was 1101A23B0053 conta bord, so I renamed it to remove the spaces, but it still comes up.

I tried to find a way to search in the cell only if it was empty with an IsEmpty variable, but then the code isn't correct as it should be in ( ) , which isn't the case here. I then tried to adapt the code with a <>"" but then it doesn't work anymore...

I can't find the Edit button, if there's one =( Sorry if it looks like I'm flooding.

After some more tests, it appears that when I delete the content of a cell in the impacted column, it simply comes up with the first file in the folder ! I've tried the different variants of the code that's been provided, but it still happens.

 

[John_w]

When I delete the content of a cell now, the macro yields a result : 1101A23B0053_contabord.pptx"

The code already handles deleting a cell's contents by exiting immediately, here:

    If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub

Although that line should really be put after the Intersect check.