CODEWITHSUNDEEP
vbaexcel
Tawm
Tawm

Reputation: 545

VBA code to open a changing file name

I'm trying to figure out a line of code to open a file. The path is constant, that is 

"H:\silly\goose\*filename.xlsm*"

However, this file name will change each time I try to run this macro. This is because I will be using this macro to automate a report which I run weekly. Each report is saved with the date in the title and all reports are kept in the same folder, meaning I can't just start naming them all the same. Examples:

H:\silly\goose\Report 06-03-15.xlsm
H:\silly\goose\Report 05-27-15.xlsm

The only helping piece of information is that this report is to be run every Wednesday. Therefore, each filename will have a difference of 7 days. I don't know if there is anything I can do with the Date method here, though.

Upvotes: 2

Views: 11520

Answers (2)

L42
L42

Reputation: 19737

What you need to do is re-construct your file name first.

Const fpath As String = "H:\silly\goose\" ' your fixed folder
Dim fname As String

' Below gives you the Wednesday of the week
fname = Format(Date - (Weekday(Date) - 1) + 3, "mm-dd-yy") ' returns 06-03-15 if run today
fname = "Report " & fname & ".xlsm" ' returns Report 06-03-15.xlsm
fname = fpath & fname ' returns H:\silly\goose\Report 06-03-15.xlsm

Then execute opening of the file:

Dim wb As Workbook
Set wb = Workbooks.Open(fname)
If wb Is Nothing Then MsgBox "File does not exist": Exit Sub

' Rest of your code goes here which works on wb Object

Upvotes: 2

Mauricio Moraes
Mauricio Moraes

Reputation: 7373

This reference has this function:

Function GetFileList(FileSpec As String) As Variant
'   Returns an array of filenames that match FileSpec
'   If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String

    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound

'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

'   Error handler
NoFilesFound:
    GetFileList = False
End Function

Now you can do:

p = "H:\silly\goose\*.xlsm"
x = GetFileList(p)

And get the file you want

Upvotes: 0

Related Questions