Open File in Folder

[Guest]

Can anyone tell me why this isn't working Please?
Basically, the files are saved in a folder (CurrMonth), and within the
current month's folder I want it to open the file that has the word "Pop" in
it.

Dim sFile
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
'Set the current month to look for files to use
CurrMon = InputBox("What Month is this report for?")
'Open the current month's HRA file
sFile = Workbooks.Open( _
" W:\comp\HRA Files\2007\CurrMon\*Pop*.xls")
Set xlbook = Workbooks.Open(sFile)

 

[Tim Williams]

set xlbook = Workbooks.Open("W:\comp\HRA Files\2007\" & CurrMon & "\*Pop*.xls")

 

[Bob Phillips]

Dim sFile
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
'Set the current month to look for files to use
CurrMon = InputBox("What Month is this report for?")
'Open the current month's HRA file
sFile = Dir("W:\comp\HRA Files\2007\CurrMon\*Pop*.xls")
Set xlbook = Workbooks.Open(sFile)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I tried the code you offered and a message came back that the file could not
be found. Run time '1004. Is there a reason why?

 

[Guest]

The same thing happened, "file could not be found" I've checked and
re-checked the path.

 

[Guest]

It looks like Bob might have missed the spec that CurrMon is a variable. So,
replace this line of his code:

sFile = Dir("W:\comp\HRA Files\2007\CurrMon\*Pop*.xls")

With this

sFile = Dir("W:\comp\HRA Files\2007\" & CurrMon & "\*Pop*.xls")

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

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)

 

[Guest]

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")

 

[Guest]

Oh my goodness! Thank you so much!!- it finally worked.

Once it's opened, I'd like to run some code which will copy and paste to a
separate workbook and close the original one without saving. How to
accomplish this?


Here is what I ended up with:

Dim CurrMon As String
Dim StrPath As String
Dim StrFilename As String
CurrMon = InputBox("What Month is this report for? ex. Jan, Feb etc.")
MsgBox "Please wait while HRA File opens"
StrPath = "W:\comp\HRA Files\2007\" & CurrMon & "\"
StrFilename = Dir(StrPath & "*Pop*.xls")
sFile = StrFilename
Workbooks.Open (sFile)
Set rng = Selection
ng.AutoFilter Field:=1, Criteria1:="IS Cont Europe"
rng.SpecialCells(xlCellTypeVisible).Copy
ChDir "H:\Automating Practice\REPORTING TOOLKIT"
'OPENS THE ISCE FILE
Set xlbook2 = Workbooks.Open( _
"H:\Automating Practice\REPORTING TOOLKIT\ISCE.xls")
'ADDS A SHEET NAMED ISCE TO PLACE THE FILTERED SELECTION
Sheets.Add.Name = "ISCE"
myShtName = ActiveSheet.Name
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
sFile.Close <<<--------
xlbook2.Activate

 

[Guest]

Glad it finally worked.

Something like this will copy data from one workbook to another. In this
case, it will copy range A1:B2 from xlbook1.Sheet1 to A1:B2 of
xlbook2.Sheet1. I put only relevant lines of code to make it clear. Make
necessary adjustments to fit your purpose...


Dim xlbook1 As Workbook
Dim xlbook2 As Workbook

Set xlbook1 = Workbooks.Open("book1.xls")
Set xlbook2 = Workbooks.Open("book2.xls")

'Copy A1:B2 in Sheet1 of xlbook1 to A1:B2 in Sheet1 of xlbook2
xlbook1.Sheets("Sheet1").Range("A1:B2").Copy
xlbook2.Sheets("Sheet1").Range("A1")