CODEWITHSUNDEEP
excelvba
rod-san
rod-san

Reputation: 45

Open Excel Workbook based on formula

I am looking for a code that will open a workbook based on the output of a formula. I have files which are named by the date e.g. 20210807 in the format JJJJMMTT. What I need is that if I run the VBA on the file "20210807" then the workbook "20210806" shall be opened. The purpose of this is, because the VBA I run on a file always reference to the previous day and I cannot retrieve data if the workbook is not open.

Is that possible? I have tried it with that code, but it doesn't work and it looks very wrong to me, but I have no clue.

Workbooks.Open FileName:= _
        "=INDIRECT(CONCATENATE(LEFT(CELL(""filename""),LEN(CELL(""filename""))-13),LEFT(RIGHT(CELL(""filename""),13),8)+1,"".xlsx"",)"

Upvotes: 0

Views: 409

Answers (2)

rod-san
rod-san

Reputation: 45

I have tried, but I don't know if I understood it perfectly. I have an additional question. So I have built this Vlookup and now want to replace a part from it with your code.

This is the code: "=IF(ISNA(VLOOKUP(RC[-1],INDIRECT(CONCATENATE(""'"",LEFT(RIGHT(CELL(""Dateiname""),13),8)-1,"".xlsx'!$A:$AP"")),2,FALSE)),DATE(LEFT(LEFT(RIGHT(CELL(""Dateiname""),13),8),4),MID(LEFT(RIGHT(CELL(""Dateiname""),13),8),5,2),RIGHT(LEFT(RIGHT(CELL(""Dateiname""),13),8),2)),VLOOKUP(RC[-1],INDIRECT(CONCATENATE(""'"",LEFT(RIGHT(CELL(""Dateiname""),13),8)-1,"".xlsx'!$A:$AP"")),2,FALSE))"

Whereas the part: CONCATENATE(""'"",LEFT(RIGHT(CELL(""Dateiname""),13),8)-1,"".xlsx'!$A:$AP"") is equal to: CONCATENATE(GetPreviousDayFileName, "$A:$AP")

But this is not working. What do I miss

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57673

You need to evaluate the formula to get its result:

Workbooks.Open FileName:=Evaluate("=INDIRECT(CONCATENATE(LEFT(CELL(""filename""),LEN(CELL(""filename""))-13),LEFT(RIGHT(CELL(""filename""),13),8)+1,"".xlsx"",)")

And you should check if the file exists or put some error handling (see VBA Error Handling – A Complete Guide) so in case the file cannot be loaded your code can handle this.

For Example:

Option Explicit

Public Sub Example()

    ' your code goes here …

    Dim OpenFileName As String
    OpenFileName = Evaluate("=INDIRECT(CONCATENATE(LEFT(CELL(""filename""),LEN(CELL(""filename""))-13),LEFT(RIGHT(CELL(""filename""),13),8)+1,"".xlsx"",)"))
    
    On Error Goto ERR_OPEN_FILE  ' on error jump to error handler
    Dim WbOpen As Workbook
    Set WbOpen = Workbooks.Open(FileName:=OpenFileName)
    On Error Goto 0  ' re-activate error reporting

    ' your code goes here …

    ' example:
    WbOpen.Worksheets("Sheet1").Range("A1").Value = "Test"
    WbOpen.Close SaveChanges:=False
    

    Exit Sub  ' exit here if no error occured.
ERR_OPEN_FILE:
    MsgBox "File '" & OpenFileName & "' could not be opened:" & vbCrLf & Err.Description, vbCritical, "Error " & Err.Number
    Err.Clear
End Sub

If your filename is 20210807.xlsm you can subtact 1 from the day 07. But what if the file name is 20210801 then this idea ob subtracting one does not work anymore.

You need to convert the string 20210807 into a real numeric date to be able to subtract one day and get the correct result as a date that you can use to build your new file name:

Public Function GetPreviousDayFileName(ByVal ThisFileName As String) As String
    'ThisFileName = "20210807.xlsm"
    
    Dim ThisYear As String
    ThisYear = Left$(ThisFileName, 4)  ' 2021
    
    Dim ThisMonth As String
    ThisMonth = Mid$(ThisFileName, 5, 2)  ' 08
    
    Dim ThisDay As String
    ThisDay = Mid$(ThisFileName, 7, 2)  ' 07
    
    
    Dim ThisDate As Date
    ThisDate = DateSerial(CInt(ThisYear), CInt(ThisMonth), CInt(ThisDay))
    
    Dim PreviousDate As Date
    PreviousDate = DateAdd("d", -1, ThisDate)  ' subtract one day
    
    ' generate file name
    GetPreviousDayFileName = Format$(PreviousDate, "YYYYMMDD") & ".xlsx"
End Function

And use it in the first example code like this:

OpenFileName = ThisWorkbook.Path & Application.PathSeparator & GetPreviousDayFileName(ThisWorkbook.Name)

Upvotes: 1

Related Questions