VBA Reference Unsaved Files

[kaylan]

Hello,

This is probably a quick fix but for the life of me I cannot figure it out.

I have a project that references an open unsaved file ("Invoice Data.xlsx"). Every day a new file is emailed to us with invoicing information. The macro file imports the relevant data and runs some calculations. The problem is that sometimes Excel will add a number to the end of the file name, I believe from opening multiple "Invoice Data.xlsx" files in a short period of time. For example, the first file will open normally but then others will open as "Invoice Data (003).xlsx" instead.

This does not work with the macro, since it is coded to specifically draw from "Invoice Data.xlsx". Is there a way to code the macro to only look for "Invoice Data" and ignore the end of the file name?

Thank you in advance!

 

[Fazza]

hello, kaylan. please consider code below. regards, Fazza

Sub Maybe()

    Dim wbkInvoiceData As Excel.Workbook
        
    Set wbkInvoiceData = FindWorkbook(sWithNameLike:="Invoice Data")

    If wbkInvoiceData Is Nothing Then
        MsgBox Prompt:="didn't find it", Buttons:=vbExclamation, Title:="file not found"
    Else
        MsgBox Prompt:=wbkInvoiceData.Name, Buttons:=vbInformation, Title:="file found"
    End If

End Sub

Private Function FindWorkbook(ByVal sWithNameLike As String) As Excel.Workbook
    
    Dim wbkLoop As Excel.Workbook
        
    For Each wbkLoop In Application.Workbooks
        If InStr(wbkLoop.Name, sWithNameLike) > 0 Then
            Set FindWorkbook = wbkLoop
            Exit For
        End If
    Next wbkLoop
    Set wbkLoop = Nothing

End Function

 

[kaylan]

Thank you Fazza, that works perfectly!

 

[Fazza]

great. all the best