Check file open based on name in cell

[excel01noob]

Hi

What is the best way to have the following:

-I have a .xlsm file that serves as template for other users and where I have VBA code written.
-In cell O19 I have the name and extension of the file we receive each month (Rawdata)
-my code is set in a way that if that raw data file name and extension is not open, there is a messagebox warning about it. If it is open, then code runs.

The rawdata file is sent via email and I would not want the users to save it, just open it from the email and then run the code.

But I am testing I see that this raw file is adding numbers to the file name and showing the Msgbox as if I don't have the file open (I have but not entirely with that name)
This is my code

++++++++++++++++++++++++++++
Sub checking_file_open ()

'For the Template workbook: TDATA is the defined format template

Dim wbTemp As Workbook
Dim wsTDATA As Worksheet,
Dim cName As String 'picks name and extension file required to check with Rawdata file

Set wbTemp = ActiveWorkbook
Set wsTDATA = wbTemp.Sheets("Data")

cName = wsTDATA.Range("O19")

'for the Rawdata workbook: FDATA for the data tab to use in the upload

Dim wbRaw As Workbook
Dim wsFDATA As Worksheet,

Set wbRaw = Nothing

On Error Resume Next

Set wbRaw = Workbooks(cName)

On Error GoTo 0

If wbRaw Is Nothing Then

MsgBox Range("O19") & " is not open!"
Else

'runs the rest of the code

End Sub
++++++++++++++++++++++++

The cell O19 is filled with

Monthly_report.cvs


I get the Msgbox when I have a file open as

Monthly_report (002).cvs

and I wanted to still run the rest of the code.
Anyway of doing that?

 

[JGordon11]

try

    found = False
    For Each wb In Workbooks
        If Left(wb.Name, Len(cName)) = cName Then
            Set wbRaw = wb
            found = True
            Exit For
        End If
    Next
    If Not found Then MsgBox Range("O19") & " is not open!"

 

[excel01noob]

Hi

I added this, as I was getting and error for variable "found"

Dim found As Boolean
Dim WB As Workbooks

found = False

For Each WB In Workbooks
If Left(WB.Name, Len(cName)) = cName Then
Set wbRaw = WB
found = True
Exit For
End If
Next
If Not found Then MsgBox Range("O19") & " is not open!"


But now I am still getting a compile error on the WB.Name

 

[GWteB]

But I am testing I see that this raw file is adding numbers to the file name

Not the file is doing that, Outlook is doing that.

Neither a workbook nor any other email attachment can be opened without being saved onto disk first. If within that specific folder already a file is located with the exact same name as the to be openened attachment, the attachment gets a suffix. That "specific folder" is (at least if you are on Windows; consider updating your MrExcel profile) in most cases C:\Users\Excel01noob\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\........\
whereas on the dots could be any folder name.
You probably noticed during testing that the workbook is opened from the email as [Read-Only]. There is a reason for this: when Outlook is closed, the (temporary) file of the workbook is deleted again. So if the workbook still has to be saved while Outlook is already closed, the user is forced to perform a Save As, whereby a suitable folder (and workbook name) can be chosen himself.

The rawdata file is sent via email and I would not want the users to save it,

Perhaps you should consider to encourage (ie coerce) users to save the workbook attached in a particular folder to be designated by you. Then you have full control and when your macro is about to finish, you can nicely close that file and delete it from disk.

 

[excel01noob]

Thank you GWteB

In our company we use MS excel 365, I prefer to use desktop app as macro don't run in excel online
I was able to run my macro with the opened file from the email (without saving it first) in some occasions.

Was trying to avoid the additional step of saving the email file to reopen it using the macro.
If the best is really to save it, then I have no issue if saved by each user in their personal directory

I just need to ensure that the user saves it with the same name I have in my cell O19 or
I include a code in VBA that forces the user to open the file from the saved location

does this make sense?

 

[GWteB]

Forget the considerations of your previous post. The code below might offer you a workaround so that users can open the attachment from the email without saving it elsewhere before running your specific code. There is a catch here though, because we can never determine whether it is actually the correct file.
The file name in cell O19 is split into base and extension. Then is checked among all open workbooks whether there is one with a ditto extension and of which its name starts with the base name according to cell 019. See if this works for you.

Sub checking_file_open_v2()

    'For the Template workbook: TDATA is the defined format template

    Dim wbTemp As Workbook
    Dim wsTDATA As Worksheet
    Dim cName As String                 'picks name and extension file required to check with Rawdata file

    Set wbTemp = ThisWorkbook           ' >>>  "ActiveWorkbook" could be any workbook, you better be specific, so changed to "ThisWorkbook"
    Set wsTDATA = wbTemp.Sheets("Data")

    cName = wsTDATA.Range("O19")

'  = = = = = = = = = = = = = = = ADDED = = = = = = = = = = = = = =
    Dim BaseName        As String
    Dim ExtensionName   As String
    Dim FSO             As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    BaseName = FSO.GetBaseName(cName)
    ExtensionName = FSO.GetExtensionName(cName)
' -----------------------------------------------------------------
    
    'for the Rawdata workbook: FDATA for the data tab to use in the upload

    Dim wbRaw As Workbook
    Dim wsFDATA As Worksheet

'  = = = = = = = = = = = = = = = ADDED = = = = = = = = = = = = = =
    For Each wbRaw In Application.Workbooks
        If StrComp(FSO.GetExtensionName(wbRaw.Name), ExtensionName, vbTextCompare) = 0 Then
            If StrComp(Left(wbRaw.Name, Len(BaseName)), BaseName, vbTextCompare) = 0 Then
                Exit For
            End If
        End If
    Next wbRaw
' -----------------------------------------------------------------
    
    If wbRaw Is Nothing Then
        MsgBox Range("O19") & " is not open!"
    Else
        'runs the rest of the code
    End If
End Sub

 

[excel01noob]

Worked perfectly, thank you very much.

Concerning the change you did from ActiveWorkbook to ThisWorkbook, I believe I could leave with ActiveWorkbook as I had inserted a button that users just need to click to run the macro.
In my view, it implies that they will always have to switch into the workbook with the macro (turning it into the ActiveWorkbook)

 

[GWteB]

You are welcome and thanks for the feedback.
Your assumption is correct in view of the button, but if a smart and curious person activates your macro via ALT F8 from another workbook, then you might have quite a problem.