Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open each file in a folder and closing it | Excel Discussion (Misc queries) | |||
Open the last file in a folder | Excel Programming | |||
open file from folder save in new folder | Excel Programming | |||
open OQY file from http:// folder | Excel Programming | |||
How can i open a folder or file | Excel Programming |