Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening worbooks with a variable file name.
I am tring to open each workbook found in a filesearch, extract some info and
then close the workbook. I don't know the name of the filepath as it is a variable from the filesearch. How do I do this. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening worbooks with a variable file name.
not sure how you're doing the filesearch - but if using the filesearch
method (of file scripting object) then use "foundfiles(i)" and the workbooks.open method - voodooJoe Sub ff() Set fs = Application.FileSearch With fs .LookIn = "C:\" .Filename = "*.xls" If .Execute 0 Then For i = 1 To .FoundFiles.Count Debug.Print .FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With End Sub "ibbm" wrote in message ... I am tring to open each workbook found in a filesearch, extract some info and then close the workbook. I don't know the name of the filepath as it is a variable from the filesearch. How do I do this. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening worbooks with a variable file name.
Thanks for your help Bernie! -- pauloreiss ------------------------------------------------------------------------ pauloreiss's Profile: http://www.excelforum.com/member.php...o&userid=29820 View this thread: http://www.excelforum.com/showthread...hreadid=497800 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening worbooks with a variable file name.
"voodooJoe" wrote: not sure how you're doing the filesearch - but if using the filesearch method (of file scripting object) then use "foundfiles(i)" and the workbooks.open method - voodooJoe here is the code Sub GetMonthlySales() Dim FS As Office.FileSearch Dim strPath As String Dim vaFileName As Variant Dim strMessage As String Dim i As Long Dim iCount As Long Dim strMonth As String Dim strOpenFile As String Set FS = Application.FileSearch strPath = "x:\ Info" strMonth = "December" With FS .NewSearch .LookIn = strPath .SearchSubFolders = True '.FileType = msoFileTypeExcelWorkbooks .Filename = strMonth iCount = .Execute strMessage = Format(iCount, "0 ""Files Found""") For Each vaFileName In .FoundFiles ' this is where I want to open the workbook but it doesn't like using the vaFileName on Workbook open. Once it's opened I want to extract data from it for my work sheet and then close it and move on to the next file found. Workbook.Open vaFileName Next vaFileName End With End Sub Can you help me this? Thanks Sub ff() Set fs = Application.FileSearch With fs .LookIn = "C:\" .Filename = "*.xls" If .Execute 0 Then For i = 1 To .FoundFiles.Count Debug.Print .FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With End Sub "ibbm" wrote in message ... I am tring to open each workbook found in a filesearch, extract some info and then close the workbook. I don't know the name of the filepath as it is a variable from the filesearch. How do I do this. Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening worbooks with a variable file name.
ibbm -
your code if fine ... except for the workbook.open line. two things to remember: (a) whenever you refer to a collection, the syntax is plural (e.g., workbookS) (b) when you open the workbook, you need to set it up as an object. -- this is necessary ... and very helpful to referring to it later try: Set wb = Workbooks.Open(vaFileName) a few tips: ** remember, the workbook you open WILL become the active workbook, so unless you want XL to turn into a flickering screen show from hell, turn off screenupdating (application.screenupdating = false) ** you can turn screen updating back on or just let it go (it turns back on itself after the code is done ** when you have the wb workbook open, be aware of which workbook is the 'activewporkbook' --- better yet, specify exactly what workbook you are referring to (e.g., thisworkbook.sheets(1).cells(1,1).delete) or you may do something you didn't mean to ** close the wb BEFORE you loop to the next one cheers - voodooJoe "ibbm" wrote in message ... "voodooJoe" wrote: not sure how you're doing the filesearch - but if using the filesearch method (of file scripting object) then use "foundfiles(i)" and the workbooks.open method - voodooJoe here is the code Sub GetMonthlySales() Dim FS As Office.FileSearch Dim strPath As String Dim vaFileName As Variant Dim strMessage As String Dim i As Long Dim iCount As Long Dim strMonth As String Dim strOpenFile As String Set FS = Application.FileSearch strPath = "x:\ Info" strMonth = "December" With FS .NewSearch .LookIn = strPath .SearchSubFolders = True '.FileType = msoFileTypeExcelWorkbooks .Filename = strMonth iCount = .Execute strMessage = Format(iCount, "0 ""Files Found""") For Each vaFileName In .FoundFiles ' this is where I want to open the workbook but it doesn't like using the vaFileName on Workbook open. Once it's opened I want to extract data from it for my work sheet and then close it and move on to the next file found. Workbook.Open vaFileName Next vaFileName End With End Sub Can you help me this? Thanks Sub ff() Set fs = Application.FileSearch With fs .LookIn = "C:\" .Filename = "*.xls" If .Execute 0 Then For i = 1 To .FoundFiles.Count Debug.Print .FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With End Sub "ibbm" wrote in message ... I am tring to open each workbook found in a filesearch, extract some info and then close the workbook. I don't know the name of the filepath as it is a variable from the filesearch. How do I do this. Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening worbooks with a variable file name.
SUCCESS!!! thanks so much!
"voodooJoe" wrote: ibbm - your code if fine ... except for the workbook.open line. two things to remember: (a) whenever you refer to a collection, the syntax is plural (e.g., workbookS) (b) when you open the workbook, you need to set it up as an object. -- this is necessary ... and very helpful to referring to it later try: Set wb = Workbooks.Open(vaFileName) a few tips: ** remember, the workbook you open WILL become the active workbook, so unless you want XL to turn into a flickering screen show from hell, turn off screenupdating (application.screenupdating = false) ** you can turn screen updating back on or just let it go (it turns back on itself after the code is done ** when you have the wb workbook open, be aware of which workbook is the 'activewporkbook' --- better yet, specify exactly what workbook you are referring to (e.g., thisworkbook.sheets(1).cells(1,1).delete) or you may do something you didn't mean to ** close the wb BEFORE you loop to the next one cheers - voodooJoe "ibbm" wrote in message ... "voodooJoe" wrote: not sure how you're doing the filesearch - but if using the filesearch method (of file scripting object) then use "foundfiles(i)" and the workbooks.open method - voodooJoe here is the code Sub GetMonthlySales() Dim FS As Office.FileSearch Dim strPath As String Dim vaFileName As Variant Dim strMessage As String Dim i As Long Dim iCount As Long Dim strMonth As String Dim strOpenFile As String Set FS = Application.FileSearch strPath = "x:\ Info" strMonth = "December" With FS .NewSearch .LookIn = strPath .SearchSubFolders = True '.FileType = msoFileTypeExcelWorkbooks .Filename = strMonth iCount = .Execute strMessage = Format(iCount, "0 ""Files Found""") For Each vaFileName In .FoundFiles ' this is where I want to open the workbook but it doesn't like using the vaFileName on Workbook open. Once it's opened I want to extract data from it for my work sheet and then close it and move on to the next file found. Workbook.Open vaFileName Next vaFileName End With End Sub Can you help me this? Thanks Sub ff() Set fs = Application.FileSearch With fs .LookIn = "C:\" .Filename = "*.xls" If .Execute 0 Then For i = 1 To .FoundFiles.Count Debug.Print .FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With End Sub "ibbm" wrote in message ... I am tring to open each workbook found in a filesearch, extract some info and then close the workbook. I don't know the name of the filepath as it is a variable from the filesearch. How do I do this. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
Clarification on "Application.Worbooks.Count" proceedure | Excel Programming | |||
Opening Variable File Names | Excel Programming | |||
excel VBA problem - setting workbook as variable & opening/re-opening | Excel Programming | |||
Help in opening powerpoint file variable from excel | Excel Programming |