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

Eawyne

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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 !
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,052
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.

VBA Code:
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
 
Solution

Eawyne

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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 :confused: Is there a way to use wildcards in this variable ?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,052
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 :confused: 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.

VBA Code:
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

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2021
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,052
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:
VBA Code:
    If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub
Although that line should really be put after the Intersect check.
 

Forum statistics

Threads
1,144,619
Messages
5,725,331
Members
422,617
Latest member
rahul27ragit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top