[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
34
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 !
 

Eawyne

New Member
Joined
Jun 28, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
Hi =)

sorry for not chiming up earlier, but I was on vacation, and had no possibilities to do further tests.

I've actually narrowed the problem down : it happens when you select multiple rows/columns and delete their content. This error happens :

Run-time error : '13':
Type mismatch

And this is the line that gets selected in the code :

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

I tried to relocate that line in the code under the Intersect check, but it didn't change anything.

Previously, I also changed this line :

VBA Code:
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 2 Then
into
VBA Code:
If Target.Column = 2 Then
to find out if it helped go around that error, but it still happens, as it's not the source of the conflict, apparently.

What's interesting is that having this :

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

       If Target.Column = 2 Then
       
        fileName = Dir(searchFolder & "*" & Target.Value & "*.ppt*")

Creates the very same error if I try to expand cells anywhere in the sheeet ; but this :

VBA Code:
 If Target.Column = 2 Then
  If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub

       If Target.Column = 2 Then
     
        fileName = Dir(searchFolder & "*" & Target.Value & "*.ppt*")

doesn't... it only happens in the second column.

I'm at a loss here ! I hope this has all been clear enough...
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Eawyne

New Member
Joined
Jun 28, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
As an example :

if I delete, or expand, the contents of all the cells in the screenshot, nothing will happen. If I select all but the first column, it bugs. So it appears that if the second column is the first one to be in a selection, or the only one, it creates some kind of conflict.
 

Attachments

  • select.jpg
    select.jpg
    27 KB · Views: 2

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
I've actually narrowed the problem down : it happens when you select multiple rows/columns and delete their content. This error happens :

Run-time error : '13':
Type mismatch

And this is the line that gets selected in the code :

VBA Code:
If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub
I tried to relocate that line in the code under the Intersect check, but it didn't change anything.
Putting each part of the compound If on separate lines usually works:
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 Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 2 Then
        If Target.CountLarge > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        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
That's based on my previous code and doesn't account for any changes you've made since.
 

Eawyne

New Member
Joined
Jun 28, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
Yes, it worked ! How simple, I should've thought about it I guess... Anyway, amazing help from you. It helped me tremendously, even on the code-grasping aspect =)

Though I have to admit, I don't really get why putting this on two lines doesn't trigger the bug ^^' So maybe I couldn't have thought about it regardless.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
Though I have to admit, I don't really get why putting this on two lines doesn't trigger the bug ^^' So

VBA Code:
If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub
VBA always evaluates all parts of a compound If statement, even if the first part is True. When Target is multiple cells, Target.Value is not a single value, but a 2-dimensional array of values, therefore comparing that array to a string ("") , fails with the error "Type mismatch" because the data types being compared are different.

VBA Code:
        If Target.CountLarge > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
With the 2 checks separated, VBA doesn't execute the second check when Target is multiple cells, therefore the error doesn't occur.
 

Eawyne

New Member
Joined
Jun 28, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
Cool. It sounds quite logical once explained.

Actually, it's the kind of detailed information I always find hard to find. It's either buried deep in thick layers of hard to digest articles, or not explained "properly" (aka. for newbies) in simpler documentation...

So again, thanks for your time and dedication :)
 

Forum statistics

Threads
1,148,158
Messages
5,745,108
Members
423,924
Latest member
Gazzat

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