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.

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.

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

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.

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.

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.