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

#### Eawyne

##### New Member
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.")
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
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

#### Eawyne

##### New Member
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
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

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

##### MrExcel MVP
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.

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

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
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
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
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.

Replies
18
Views
181
Replies
16
Views
303
Replies
21
Views
492
Replies
5
Views
205
Replies
4
Views
89

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.

### Which adblocker are you using?

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

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