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 he-

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" wrote in message
...
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 he-

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" wrote in message
...
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" wrote in message
...
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 he-

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" wrote in message
...
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" wrote in message
...
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 he-

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" wrote in
message
...
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" wrote in message
...
See this for workbooks
http://www.rondebruin.nl/copy3.htm


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


"need_some_help" wrote in message
...
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 he-

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" wrote in
message
...
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" wrote in message ...
Hey Ron - Good morning :-) (00:45 here in the UK)

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

"Ron de Bruin" wrote in message ...
See this for workbooks
http://www.rondebruin.nl/copy3.htm


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


"need_some_help" wrote in message
...
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 he-

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" wrote in message
...
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" wrote in message
...
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 he-

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" wrote in
message
...
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 (ToolsReferences)

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" wrote in message
...
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" wrote in
message
...
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 he-

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" wrote in
message
...
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