Open multiple "unknown" filenames within a macro (array setup)

I'm writing a macro that opens a static folder and then copies specific data
from all the files in that folder to manipulate. I can write the code to
open a file by specifying the exact filename. I need to learn how to open a
file (all the files in a particular folder) one by one without knowing:

1)the filenames
2)how many files are in the folder

ahead of time. Please help

Try here:-

http://www.rondebruin.nl/tips.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"need_some_help" <[email protected]> wrote in message
news:[email protected]...
> I'm writing a macro that opens a static folder and then copies specific
> data
> from all the files in that folder to manipulate. I can write the code to
> open a file by specifying the exact filename. I need to learn how to open
> a
> file (all the files in a particular folder) one by one without knowing:
>
> 1)the filenames
> 2)how many files are in the folder
>
> ahead of time. Please help

Cool This really makes sense. As I modify this to look at multiple workbooks
(not just worksheets within the same workbook) it gets a little tricky. Any
suggestions on how to set up the array to read in multiple filenames (i.e.
some variation of this line For Each sh In Sheets(Array("Sheet1", "Sheet3")))
Thanks much

"Ken Wright" wrote:

> Try here:-
>
> http://www.rondebruin.nl/tips.htm
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
> "need_some_help" <[email protected]> wrote in message
> news:[email protected]...
> > I'm writing a macro that opens a static folder and then copies specific
> > data
> > from all the files in that folder to manipulate. I can write the code to
> > open a file by specifying the exact filename. I need to learn how to open
> > a
> > file (all the files in a particular folder) one by one without knowing:
> >
> > 1)the filenames
> > 2)how many files are in the folder
> >
> > ahead of time. Please help
>
>
>

See this for workbooks
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"need_some_help" <[email protected]> wrote in message
news:[email protected]...
> Cool This really makes sense. As I modify this to look at multiple workbooks
> (not just worksheets within the same workbook) it gets a little tricky. Any
> suggestions on how to set up the array to read in multiple filenames (i.e.
> some variation of this line For Each sh In Sheets(Array("Sheet1", "Sheet3")))
> Thanks much
>
> "Ken Wright" wrote:
>
>> Try here:-
>>
>> http://www.rondebruin.nl/tips.htm
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>> "need_some_help" <[email protected]> wrote in message
>> news:[email protected]...
>> > I'm writing a macro that opens a static folder and then copies specific
>> > data
>> > from all the files in that folder to manipulate. I can write the code to
>> > open a file by specifying the exact filename. I need to learn how to open
>> > a
>> > file (all the files in a particular folder) one by one without knowing:
>> >
>> > 1)the filenames
>> > 2)how many files are in the folder
>> >
>> > ahead of time. Please help
>>
>>
>>

Do you really need multiple filenames or are you just looking to pull all
files within a folder as per your original post. If so then try a variant
of this, though if you look through Ron's examples, this may actually have
come from there anyway:-

Sub CopyAllSheetsToOneFile()
Dim i As Integer
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim ws As Worksheet

Application.ScreenUpdating = False

Set wbDest = ThisWorkbook
' or alternativwly Set wbDest = Workbooks.Add

With Application.FileSearch
.NewSearch
.LookIn = "C:\TEST"
.FileType = msoFileTypeExcelWorkbooks
.Execute

For i = 1 To .FoundFiles.Count
Set wbSource = Workbooks.Open(.FoundFiles(i))
For Each ws In wbSource.Worksheets
ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
Next ws
wbSource.Close
Next i

End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"need_some_help" <[email protected]> wrote in message
news:[email protected]...
> Cool This really makes sense. As I modify this to look at multiple
> workbooks
> (not just worksheets within the same workbook) it gets a little tricky.
> Any
> suggestions on how to set up the array to read in multiple filenames (i.e.
> some variation of this line For Each sh In Sheets(Array("Sheet1",
> "Sheet3")))
> Thanks much
>
> "Ken Wright" wrote:
>
>> Try here:-
>>
>> http://www.rondebruin.nl/tips.htm
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>> "need_some_help" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > I'm writing a macro that opens a static folder and then copies specific
>> > data
>> > from all the files in that folder to manipulate. I can write the code
>> > to
>> > open a file by specifying the exact filename. I need to learn how to
>> > open
>> > a
>> > file (all the files in a particular folder) one by one without knowing:
>> >
>> > 1)the filenames
>> > 2)how many files are in the folder
>> >
>> > ahead of time. Please help
>>
>>
>>

Hey Ron - Good morning :-) (00:45 here in the UK)

Regards
Ken.......................

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> See this for workbooks
> http://www.rondebruin.nl/copy3.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "need_some_help" <[email protected]> wrote in message
> news:[email protected]...
>> Cool This really makes sense. As I modify this to look at multiple
>> workbooks
>> (not just worksheets within the same workbook) it gets a little tricky.
>> Any
>> suggestions on how to set up the array to read in multiple filenames
>> (i.e.
>> some variation of this line For Each sh In Sheets(Array("Sheet1",
>> "Sheet3")))
>> Thanks much
>>
>> "Ken Wright" wrote:
>>
>>> Try here:-
>>>
>>> http://www.rondebruin.nl/tips.htm
>>>
>>> --
>>> Regards
>>> Ken....................... Microsoft MVP - Excel
>>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>>
>>> ------------------------------*------------------------------*----------------
>>> It's easier to beg forgiveness than ask permission :-)
>>> ------------------------------*------------------------------*----------------
>>>
>>> "need_some_help" <[email protected]> wrote in
>>> message
>>> news:[email protected]...
>>> > I'm writing a macro that opens a static folder and then copies
>>> > specific
>>> > data
>>> > from all the files in that folder to manipulate. I can write the code
>>> > to
>>> > open a file by specifying the exact filename. I need to learn how to
>>> > open
>>> > a
>>> > file (all the files in a particular folder) one by one without
>>> > knowing:
>>> >
>>> > 1)the filenames
>>> > 2)how many files are in the folder
>>> >
>>> > ahead of time. Please help
>>>
>>>
>>>
>
>

Hi Ken

For me 1 hour later on that moment (much to late)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ken Wright" <[email protected]> wrote in message news:%[email protected]...
> Hey Ron - Good morning :-) (00:45 here in the UK)
>
> Regards
> Ken.......................
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> See this for workbooks
>> http://www.rondebruin.nl/copy3.htm
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "need_some_help" <[email protected]> wrote in message
>> news:[email protected]...
>>> Cool This really makes sense. As I modify this to look at multiple workbooks
>>> (not just worksheets within the same workbook) it gets a little tricky. Any
>>> suggestions on how to set up the array to read in multiple filenames (i.e.
>>> some variation of this line For Each sh In Sheets(Array("Sheet1", "Sheet3")))
>>> Thanks much
>>>
>>> "Ken Wright" wrote:
>>>
>>>> Try here:-
>>>>
>>>> http://www.rondebruin.nl/tips.htm
>>>>
>>>> --
>>>> Regards
>>>> Ken....................... Microsoft MVP - Excel
>>>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>>>
>>>> ------------------------------*------------------------------*----------------
>>>> It's easier to beg forgiveness than ask permission :-)
>>>> ------------------------------*------------------------------*----------------
>>>>
>>>> "need_some_help" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>> > I'm writing a macro that opens a static folder and then copies specific
>>>> > data
>>>> > from all the files in that folder to manipulate. I can write the code to
>>>> > open a file by specifying the exact filename. I need to learn how to open
>>>> > a
>>>> > file (all the files in a particular folder) one by one without knowing:
>>>> >
>>>> > 1)the filenames
>>>> > 2)how many files are in the folder
>>>> >
>>>> > ahead of time. Please help
>>>>
>>>>
>>>>
>>
>>
>
>

The workbook code Ron sent worked beautifully (Thanks guy). I've got a
client who wants the macro to work "in whatever directory the macro is opened
in". This will give the flexibility when it is not known in advanced where
the list we're reading from is located (maybe on hard drive maybe on network
etc) I think I'll use some sort of current directory function to tell the
macro to perform the code "in the same directory where the macro is opened"

Thanks for your intelligent input

"Ken Wright" wrote:

> Do you really need multiple filenames or are you just looking to pull all
> files within a folder as per your original post. If so then try a variant
> of this, though if you look through Ron's examples, this may actually have
> come from there anyway:-
>
> Sub CopyAllSheetsToOneFile()
> Dim i As Integer
> Dim wbDest As Workbook
> Dim wbSource As Workbook
> Dim ws As Worksheet
>
> Application.ScreenUpdating = False
>
> Set wbDest = ThisWorkbook
> ' or alternativwly Set wbDest = Workbooks.Add
>
> With Application.FileSearch
> .NewSearch
> .LookIn = "C:\TEST"
> .FileType = msoFileTypeExcelWorkbooks
> .Execute
>
> For i = 1 To .FoundFiles.Count
> Set wbSource = Workbooks.Open(.FoundFiles(i))
> For Each ws In wbSource.Worksheets
> ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
> Next ws
> wbSource.Close
> Next i
>
> End With
> Application.ScreenUpdating = True
>
> End Sub
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
> "need_some_help" <[email protected]> wrote in message
> news:[email protected]...
> > Cool This really makes sense. As I modify this to look at multiple
> > workbooks
> > (not just worksheets within the same workbook) it gets a little tricky.
> > Any
> > suggestions on how to set up the array to read in multiple filenames (i.e.
> > some variation of this line For Each sh In Sheets(Array("Sheet1",
> > "Sheet3")))
> > Thanks much
> >
> > "Ken Wright" wrote:
> >
> >> Try here:-
> >>
> >> http://www.rondebruin.nl/tips.htm
> >>
> >> --
> >> Regards
> >> Ken....................... Microsoft MVP - Excel
> >> Sys Spec - Win XP Pro / XL 97/00/02/03
> >>
> >> ------------------------------Â*------------------------------Â*----------------
> >> It's easier to beg forgiveness than ask permission :-)
> >> ------------------------------Â*------------------------------Â*----------------
> >>
> >> "need_some_help" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> > I'm writing a macro that opens a static folder and then copies specific
> >> > data
> >> > from all the files in that folder to manipulate. I can write the code
> >> > to
> >> > open a file by specifying the exact filename. I need to learn how to
> >> > open
> >> > a
> >> > file (all the files in a particular folder) one by one without knowing:
> >> >
> >> > 1)the filenames
> >> > 2)how many files are in the folder
> >> >
> >> > ahead of time. Please help
> >>
> >>
> >>
>
>
>

How about the following then:-

Dump this all into a module, then set a reference to the Scripting runTime
library as per the notes in the code and then run 'DoStuffToAllFiles'.

====================================================

Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, F As Object
Set SA = CreateObject("Shell.application")
Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not F Is Nothing) Then
PickFolder = F.items.Item.path
End If
Set F = Nothing
Set SA = Nothing
End Function

Sub DoStuffToAllFiles()
'This uses the Microsoft Scripting Runtime library, so you need to set a
'reference to that (Tools>References)

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objSubfolder As Scripting.Folder
Dim objFile As Scripting.File
Dim Sh As Worksheet

Application.ScreenUpdating = False

Set objFSO = CreateObject("Scripting.FileSystemObject")
objfoldpath = PickFolder(strStartDir)
Set objFolder = objFSO.GetFolder(objfoldpath)
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.path & "\" & objFile.Name

With ActiveWorkbook
.Names.Add Name:="NewYear", RefersToR1C1:="=Data!R2C6"
For Each Sh In .Worksheets
Cells.Replace what:="Total 2004", _
Replacement:="=""Total ""&NewYear", _
lookat:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Cells.Replace what:="2004 YTD", _
Replacement:="=NewYear&"" YTD""", _
lookat:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Next Sh
.Close SaveChanges:=True
End With

End If
Next

Application.ScreenUpdating = True
End Sub

================================================

This then allows you to pick a folder to work with via the normal browse
type dialog box, stores the folder path as a variable and then carries on as
normal.

Marry up your code plus what we gave you earlier with this and it should
hopefully do everything you want.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"need_some_help" <[email protected]> wrote in message
news:[email protected]...
> The workbook code Ron sent worked beautifully (Thanks guy). I've got a
> client who wants the macro to work "in whatever directory the macro is
> opened
> in". This will give the flexibility when it is not known in advanced
> where
> the list we're reading from is located (maybe on hard drive maybe on
> network
> etc) I think I'll use some sort of current directory function to tell the
> macro to perform the code "in the same directory where the macro is
> opened"
>
> Thanks for your intelligent input
>
> "Ken Wright" wrote:
>
>> Do you really need multiple filenames or are you just looking to pull all
>> files within a folder as per your original post. If so then try a
>> variant
>> of this, though if you look through Ron's examples, this may actually
>> have
>> come from there anyway:-
>>
>> Sub CopyAllSheetsToOneFile()
>> Dim i As Integer
>> Dim wbDest As Workbook
>> Dim wbSource As Workbook
>> Dim ws As Worksheet
>>
>> Application.ScreenUpdating = False
>>
>> Set wbDest = ThisWorkbook
>> ' or alternativwly Set wbDest = Workbooks.Add
>>
>> With Application.FileSearch
>> .NewSearch
>> .LookIn = "C:\TEST"
>> .FileType = msoFileTypeExcelWorkbooks
>> .Execute
>>
>> For i = 1 To .FoundFiles.Count
>> Set wbSource = Workbooks.Open(.FoundFiles(i))
>> For Each ws In wbSource.Worksheets
>> ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
>> Next ws
>> wbSource.Close
>> Next i
>>
>> End With
>> Application.ScreenUpdating = True
>>
>> End Sub
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>> "need_some_help" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > Cool This really makes sense. As I modify this to look at multiple
>> > workbooks
>> > (not just worksheets within the same workbook) it gets a little tricky.
>> > Any
>> > suggestions on how to set up the array to read in multiple filenames
>> > (i.e.
>> > some variation of this line For Each sh In Sheets(Array("Sheet1",
>> > "Sheet3")))
>> > Thanks much
>> >
>> > "Ken Wright" wrote:
>> >
>> >> Try here:-
>> >>
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >> --
>> >> Regards
>> >> Ken....................... Microsoft MVP - Excel
>> >> Sys Spec - Win XP Pro / XL 97/00/02/03
>> >>
>> >> ------------------------------*------------------------------*----------------
>> >> It's easier to beg forgiveness than ask permission :-)
>> >> ------------------------------*------------------------------*----------------
>> >>
>> >> "need_some_help" <[email protected]> wrote in
>> >> message
>> >> news:[email protected]...
>> >> > I'm writing a macro that opens a static folder and then copies
>> >> > specific
>> >> > data
>> >> > from all the files in that folder to manipulate. I can write the
>> >> > code
>> >> > to
>> >> > open a file by specifying the exact filename. I need to learn how
>> >> > to
>> >> > open
>> >> > a
>> >> > file (all the files in a particular folder) one by one without
>> >> > knowing:
>> >> >
>> >> > 1)the filenames
>> >> > 2)how many files are in the folder
>> >> >
>> >> > ahead of time. Please help
>> >>
>> >>
>> >>
>>
>>
>>