Creating Code to open a file whose name changes by week

I want to create code that will open up an excel workbook that has a different name every week. In the file path it is "we 11-08-14" I want to be able to open that folder up and select a filename based on wildcard values included around the day I want to access. This is what I have so far. It is compiling it successfully but it won't open the document. I suspect an error in the directory.

Sub ImportOpenWK()

Dim directory As String
Dim fileName As String
Dim foldername As String
Dim sheet As Worksheet
Dim total As Integer
Dim weeknum As Integer
Dim dayser As Long
Dim weekof As Long
Dim convertser As Date
Dim wbk As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False


weeknum = Format$(Now(), "ww", vbSaturday)
dayser = DateSerial(2014, 1, 4)
weekof = dayser - 7 + 7 * weeknum
convertser = weekof
convertser = Format$(weekof, "mm-dd-yy")



directory = "S:\District\WP\All\H2K\Planning\Plans\Lineups\we&convertser"
fileName = Dir(directory & "Monday Lineup??")

Do While fileName <> ""
Set wbk = Workbooks.Open(directory & fileName)
wbk.Worksheets("Imported Data").Range("A2:N10000").Copy
Worksheets("This Week SP").Range("A2").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Workbooks(fileName).Close

fileName = Dir()


Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Hi dafigu01 and welcome to ExcelForum,

When you post code, you need 'code tags'. See the forum rules for instructions:
http://www.excelforum.com/forum-rule...rum-rules.html

We're not allowed to answer your questions without 'Code Tags'.

I think you have two problems:
a. The date has to be a string to be formatted the way you want
b. The directory should probably have a trailing backslash "\".

Run this in the debugger and open the Immediate Window (CTRL G). The two debug.print lines should give you a clue to whether the directory has the correct name, and if the Dir() command found the file you are looking for.

Lewis

Changes in red:

Please Login or Register  to view this content.

LJMetzger,

Thanks for the quick response I will be sure to employ code tags in any future posts. The changes you made allowed me to fix the code.

I'm glad it worked out for you. Don't hesitate to ask if you have any more problems and/or questions.