Open File in Folder
 

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)

Open File in Folder
 

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


--
Tim Williams
Palo Alto, CA


"CV323" wrote in message ...
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)

Open File in Folder
 

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)



"CV323" wrote in message
...
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)

Open File in Folder
 

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?

"Tim Williams" wrote:

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


--
Tim Williams
Palo Alto, CA


"CV323" wrote in message ...
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)

Open File in Folder
 

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

"Bob Phillips" wrote:

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)



"CV323" wrote in message
...
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)

Open File in Folder
 

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




"CV323" wrote:

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

"Bob Phillips" wrote:

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)



"CV323" wrote in message
...
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)

Open File in Folder
 

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

Open File in Folder
 

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

Open File in Folder
 

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

Open File in Folder
 

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

Open File in Folder
 

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



"Vergel Adriano" wrote:

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

Open File in Folder
 

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




"CV323" wrote:

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



"Vergel Adriano" wrote:

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