No problem. You're almost there but you missed out the Dir command. You
need the Dir command to get the filename because the Open method doesn't take
wild card characters. The Dir command does take wild cards and returns the
filename of the first file that matches your criteria. Be careful here
because if you have 2 files that satisfies "*pop*.xls" criteria, there's a
chance you'd get the wrong file. If you lookup the help file for "Dir",
you'll see that you can loop through the results if needed.

Going back to your code, you just need to replace this line:

StrFilename = "*Pop*.xls"

with this:

StrFilename = Dir(StrPath & "*Pop*.xls")



"CV323" wrote:

I'm sorry but I'm still getting the same error, file not found.

I adjusted it like this:

CurrMon = InputBox("What Month is this report for? ex. Jan, Feb etc.")
StrPath = "W:\comp\HRA Files\2007\" & CurrMon & "\"
StrFilename = "*Pop*.xls"
sFile = StrPath & StrFilename
Workbooks.Open (sFile)


"Vergel Adriano" wrote:

I think this ought to do it. It turns out that the Dir command returns only
the filename and does not include that path information. So, it should be
like this:

Set xlbook = Workbooks.Open("W:\comp\HRA Files\2007\" & CurrMon & "\" & sFile)



"CV323" wrote:

Here is what it looks like:

Dim sFile As Variant
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer
Dim CurrMon As String
Dim xlbook As Workbook, xlbook2 As Workbook
Dim rng As Range, rng1 As Range, rng2 As Range
'Set the current month to look for files to use
CurrMon = InputBox("What Month is this report for? ex. Jan, Feb etc.")

'Open the current month's HRA file
sFile = Dir("W:\comp\HRA Files\2007\" & CurrMon & "\*Pop*.xls")
'even though it looks like it's found the file I need, it returns run time
1004 saying the file couldn't be found. But it's obviously located the
correct file.
Set xlbook = Workbooks.Open(sFile) '<<<----
'======== Why won't it open?

Set rng = Selection