0

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"",)"
rod-san
  • 45
  • 5

2 Answers2

1

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)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hey Thanks for your reply. I have tried it, but I get a error 13 type mismatch. – rod-san Aug 13 '21 at 08:02
  • In which line of the code? Also see my edited code. You have another issue with your date because you cannot just subtract `1` from the day if your date is `20210801` that would result in `20210800` an not in `20210731`. Therefore it would be more complicated see my function. – Pᴇʜ Aug 13 '21 at 08:17
  • Thanks it works! :) Now I just have to figure out how I can put the function into a button. – rod-san Aug 13 '21 at 08:47
  • Sorry forgot to do that. Thanks :) I have one more question if you don't mind. Actually I have to open the excel file from the previous day to retrieve the data, but my actual code should be executed on the current day file. Can we implement in the code that after opening the previous day file we will jump back to todays file? Because right now it is executing the code on the previous day file. – rod-san Aug 13 '21 at 10:18
  • @rod-san of course we can: You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • You need to make clear for **every** `Range` or `Cells` object in which workbook/worksheet they are: `ThisWorkbook.Worksheets("Sheet1").Range("A1")` is in the workbook the VBA code is written in while `WbOpen.Worksheets("Sheet1").Range("A1")` is in the workbook you opened. Don't use `.Activate` or `.Select` and be clear in which workbooky/sheet your ranges are and you won't run into such issues. – Pᴇʜ Aug 13 '21 at 10:43
0

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

rod-san
  • 45
  • 5