open file with wildcard name

[smakatura]

I have a file 09-08-11 tmp.csv in a folder

• the file names will change (date wise) so the file name with a wildcard would be *tmp.csv
  • for future reference the *tmp.csv file is the variable tmpfilename
• there may not be a tmpfilename file in the folder
• there will never be more than one tmpfilename
• the location(directory) of the tmpfilename will remain the same at all times

I want to

• search the directory location
• see if a tmpfilename exists
• if it does open the tmpfilename that is in the folder
  • keeping in mind that the actual file name will change as a new one is created on a new date.

I can get the macro to searh to see if tmpfilename exists...but I can't get it to open it if it does. (so the workbooks.open part is what is not working).

below is my partial code.

    Dim tmpfilename As String
 
    tmpfilename = "*tmp.csv"
 
    If Len(Dir("C:\Users\smakatura\Documents\call report\" & tmpfilename,
            vbDirectory)) <> 0 Then
 
        Workbooks.Open filename:="C:\Users\smakatura\Documents\call report\" & tmpfilename

I am thinking that somehow I need to assign the actual file name (09-08-11 tmp.csv) to a new variable (we will call it actualtmpfilename) and then have it open the actualtmpfilename document. but not sure how to assign that actual file name to the new variable.

I know I have not added actualfilename as a variable above yet.

 

[Richard Schollar]

Hi

You are using DIR with the vbDirectory attribute but are searching for a file, not a folder. This would cause a problem.

Try like this:

Dim strMyFile As String

strMyFile = Dir(Path & "*tmp.csv")

If strMyFile<>"" Then 
  Set wb = Workbook.sOpen(Path & "strMyFile")
  'more code...
Else
  Msgbox "File Not found!"
End if

 

[smakatura]

thanks that worked.

the code had one typo of workbook.sopen instead of workbooks.open but typing errors occur.