Opening worbooks with a variable file name.

[ibbm]

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.

[voodooJoe]

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" <[email protected]> wrote in message
news:[email protected]...
>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.

[ibbm]

"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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

[voodooJoe]

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>

[ibbm]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>