How can I have my macro run on a differently named workbook without the 'subscript out of range-CodePudding

All;

I have been stumped on this problem for a few weeks now and can't seem to figure it out.

I created a macro to run on files I download daily - they contain information on a select number of stocks. The macro isn't particularly complex or deep. It bolds some columns, performs a calculation in a new column, sorts by that calculation (Z-A), and creates a text-joined list of the top x number of stocks to be imported into a watchlist on TradingView.

My problem is that the macro only runs on workbooks of the same name. I tried to get around this by creating the macro under a standard file name and then manually changing the downloads to this name in Finder so the macro can run once I open the file and activate it. This has been the only way to circumvent the run time error 9: 'subscript out of range' error message. Doing this everyday, however, is cumbersome and seems to me like a problem that wouldn't be too difficult to code around provided.

For example:

russell-2000-index-12-23-2021.csv is the download name (date will vary) while russell-2000-index.csv the file name currently in the VBA code of the macro

Each subsequent day I download these, the file name will reflect the date of the download. Ive tried using VBA to edit the code to use a wildcard after the 'russell-2000-index' portion of the file name but this doesn't work for me. The files themselves have an identical structure with the same number of columns, titles, etc. each day they are downloaded.

If someone could help me out with how to code my macro with a wildcard in VBA to be able to run on the different file names that will be passed through it, I would much appreciate it.

Thank you!

CodePudding user response：

If you are putting the code into the workbook before running it, you can use the built in Object identifiers to refer to the workbook without using its name. Eg. ThisWorkbook instead of Workbooks("Name").

If you are opening the workbook alongside your macro workbook/add-on then you can search through the Workbooks collection to find a name that matches your pre-defined patterns like

  Dim WB as Workbook, csvWB As Workbook
  For Each WB in Application.Workbooks
    If ... Then Set csvWB = WB
  Next WB

The key then, is to find a way to match the name of the workbook to your pattern. You can try using the Like Operator.

If FileName Like "russell-2000-index-??-??-????.csv" Then ...

If FileName Like "russell-2000-index*" Then ...

You can also use the InStr function to test if the string contains a specific sub-string. InStr returns the position of the substring if found in the main string, or 0 if not found. So we can test <> 0 (not equal to zero).

If Instr(1, FileName, "russell-2000-index", vbTextCompare) <> 0 Then ...

Once you have successfully found the workbook using one of these methods, you can continue the rest of the macro by using the variable csvWB and not need to use the file name anymore.

CodePudding user response：

Identify File by Date

Option Explicit

Function GetTodaysRussell() As String
    
    Const FolderPath As String = "C:\Test\"
    Const BaseName As String = "russell-2000-index"
    Const Delimiter As String = "-"
    Const DateFormat As String = "mm-dd-yyyy"
    Const FileExtension As String = ".csv"
    
    Dim FilePath As String: FilePath = FolderPath & BaseName _
        & Delimiter & Format(Date, DateFormat) & FileExtension
    
    If Len(Dir(FilePath)) > 0 Then GetTodaysRussell = FilePath

End Function

Sub GetTodaysRussellTEST()
    
    Dim swbPath As String: swbPath = GetTodaysRussell
    
    If Len(swbPath) = 0 Then
        MsgBox "Today's file hasn't been downloaded yet.", vbExclamation
        Exit Sub
    End If
    
    Dim swb As Workbook: Set swb = Workbooks.Open(swbPath)
    ' Continue...
    ' e.g.:
    MsgBox "Opened file '" & swb.Name & "'.", vbInformation
    
End Sub