Hi,

I have a folder "C:\CCAPPS\ttlview\TMP" and I want to open all the excel
files in this automatically. ( there are around 20 to 25 files normally in
this folder)

Once opened I will be running macros which will be collating data from these
files and doing some further operations. (I learned those macros from the
help of Newsgroups only).

Please tell me if this is possible.

Regards,
Hari
India

Hi Hari

Sub OpenWorkbooksInLocation()
Application.ScreenUpdating = False
Dim i as integer
With Application.FileSearch
..NewSearch
..LookIn = "C:\CCAPPS\ttlview\TMP"
..SearchSubFolders = False
..FileName = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
'Macro code for each workbook here
wb.Save
wb.Close
Next i
End With
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William



"Hari" wrote in message
...
| Hi,
|
| I have a folder "C:\CCAPPS\ttlview\TMP" and I want to open all the excel
| files in this automatically. ( there are around 20 to 25 files normally in
| this folder)
|
| Once opened I will be running macros which will be collating data from
these
| files and doing some further operations. (I learned those macros from the
| help of Newsgroups only).
|
| Please tell me if this is possible.
|
| Regards,
| Hari
| India
|
|

Hi William,

Thanx a lot for the code.

When I run it my excel hangs and even after waiting for 5 or 6 minutes there
is no result. I pressed Ctrl+break to get out of the code. Im using Excel
2002 and Win 2000.

When I enter the code after stopping it the line "For i = 1 To
..FoundFiles.Count" is highlighted in yellow.

Please tell me how to overcome the same.

Regards,
Hari
India


"William" wrote in message
...
Hi Hari

Sub OpenWorkbooksInLocation()
Application.ScreenUpdating = False
Dim i as integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\CCAPPS\ttlview\TMP"
.SearchSubFolders = False
.FileName = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
'Macro code for each workbook here
wb.Save
wb.Close
Next i
End With
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William



"Hari" wrote in message
...
| Hi,
|
| I have a folder "C:\CCAPPS\ttlview\TMP" and I want to open all the excel
| files in this automatically. ( there are around 20 to 25 files normally
in
| this folder)
|
| Once opened I will be running macros which will be collating data from
these
| files and doing some further operations. (I learned those macros from
the
| help of Newsgroups only).
|
| Please tell me if this is possible.
|
| Regards,
| Hari
| India
|
|

Hi Hari

Which line of the code does Excel hang on?

Are you sure theres is a folder called "C:\CCAPPS\ttlview\TMP"?

--
XL2002
Regards

William



"Hari" wrote in message
...
| Hi William,
|
| Thanx a lot for the code.
|
| When I run it my excel hangs and even after waiting for 5 or 6 minutes
there
| is no result. I pressed Ctrl+break to get out of the code. Im using Excel
| 2002 and Win 2000.
|
| When I enter the code after stopping it the line "For i = 1 To
| .FoundFiles.Count" is highlighted in yellow.
|
| Please tell me how to overcome the same.
|
| Regards,
| Hari
| India
|
|
| "William" wrote in message
| ...
| Hi Hari
|
| Sub OpenWorkbooksInLocation()
| Application.ScreenUpdating = False
| Dim i as integer
| With Application.FileSearch
| .NewSearch
| .LookIn = "C:\CCAPPS\ttlview\TMP"
| .SearchSubFolders = False
| .FileName = "*.xls"
| .Execute
| For i = 1 To .FoundFiles.Count
| Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
| 'Macro code for each workbook here
| wb.Save
| wb.Close
| Next i
| End With
| Application.ScreenUpdating = True
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "Hari" wrote in message
| ...
| | Hi,
| |
| | I have a folder "C:\CCAPPS\ttlview\TMP" and I want to open all the
excel
| | files in this automatically. ( there are around 20 to 25 files
normally
| in
| | this folder)
| |
| | Once opened I will be running macros which will be collating data from
| these
| | files and doing some further operations. (I learned those macros from
| the
| | help of Newsgroups only).
| |
| | Please tell me if this is possible.
| |
| | Regards,
| | Hari
| | India
| |
| |
|
|
|
|

Hi William,

In ur code excel hangs and when I press the desktop bar for excel it doesnt
respond even after 8 or 9 mintues ( Presently in that folder I have only 5
or so files but very soon I will have 20 or so files). Yes, the folder
exists because Im referencing this folder for some of my other macros and
they work perfectly.

I then pressed Ctrl + Break ( Its strange that I have to press Ctrl + Break
many times before the program displays the following message). I get a
message similar to program has been interrupted and has three or four
buttons -- debug, end and help ( Im writing from memory). I chose debug and
when I enter VB editor I see that the line "For i = 1 To .FoundFiles.Count"
is highlighted in yellow.

I then went to help ( Im very new to VB, just had a primer in C++ years
back) and typed filesearch and somehow made very slight modifications to
your orignal code which is given below and when I executed this it worked
fine.

If possible, please tell me why ur original code wasnt working. This would
help me in better understanding of VB

The lines I changes was to replace ur ".FileName = "*.xls"" with the
following line -- " .FileType = msoFileTypeExcelWorkbooks"

Though I dont know why the modified form works, I have one more doubt (
which I have posted in a new post and in a different subject about
"Displayalerts and screenupdating" ). The query is if I set
..SearchSubFolders = True, then one of the subfolders had a excel file of the
same name as in the root
folder. Inspite of this excel didnt display the message that 2 files with
same name cannot be opened. If possible, Please tell me why this was
happening.


Sub OpenWorkbooksInLocation()

Application.ScreenUpdating = False
Dim i As Integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\CCAPPS\ttlview\TMP"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(filename:=.FoundFiles(i))
'Macro code for each workbook here
'wb.Save
'wb.Close
Next i
End With
Application.ScreenUpdating = True

End Sub


"William" wrote in message
...
Hi Hari

Which line of the code does Excel hang on?

Are you sure theres is a folder called "C:\CCAPPS\ttlview\TMP"?

--
XL2002
Regards

William



"Hari" wrote in message
...
| Hi William,
|
| Thanx a lot for the code.
|
| When I run it my excel hangs and even after waiting for 5 or 6 minutes
there
| is no result. I pressed Ctrl+break to get out of the code. Im using
Excel
| 2002 and Win 2000.
|
| When I enter the code after stopping it the line "For i = 1 To
| .FoundFiles.Count" is highlighted in yellow.
|
| Please tell me how to overcome the same.
|
| Regards,
| Hari
| India
|
|
| "William" wrote in message
| ...
| Hi Hari
|
| Sub OpenWorkbooksInLocation()
| Application.ScreenUpdating = False
| Dim i as integer
| With Application.FileSearch
| .NewSearch
| .LookIn = "C:\CCAPPS\ttlview\TMP"
| .SearchSubFolders = False
| .FileName = "*.xls"
| .Execute
| For i = 1 To .FoundFiles.Count
| Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
| 'Macro code for each workbook here
| wb.Save
| wb.Close
| Next i
| End With
| Application.ScreenUpdating = True
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "Hari" wrote in message
| ...
| | Hi,
| |
| | I have a folder "C:\CCAPPS\ttlview\TMP" and I want to open all the
excel
| | files in this automatically. ( there are around 20 to 25 files
normally
| in
| | this folder)
| |
| | Once opened I will be running macros which will be collating data
from
| these
| | files and doing some further operations. (I learned those macros
from
| the
| | help of Newsgroups only).
| |
| | Please tell me if this is possible.
| |
| | Regards,
| | Hari
| | India
| |
| |
|
|
|
|

Hari

Perhaps the code failed on the line below because you changed "
..SearchSubFolders = False" to ".SearchSubFolders = True" and Excel attempted
to open 2 files with the same name - otherwise I cannot help further.
"For i = 1 To .FoundFiles.Count"

As a general rule its more efficient to open 1 file at a time, execute any
code, save and close the workbook and then go on to the next workbook. If
you do not follow this route and try and open a file with the same name as a
file that is already open you will run into problems.

I do not know why the line " .FileName = "*.xls" " didn't work - I assume
the files are all standard Excel (".xls") files.

Sorry I cannot help further - perhaps others following this post can.
--
XL2002
Regards

William



"Hari" wrote in message
...
| Hi William,
|
| In ur code excel hangs and when I press the desktop bar for excel it
doesnt
| respond even after 8 or 9 mintues ( Presently in that folder I have only 5
| or so files but very soon I will have 20 or so files). Yes, the folder
| exists because Im referencing this folder for some of my other macros and
| they work perfectly.
|
| I then pressed Ctrl + Break ( Its strange that I have to press Ctrl +
Break
| many times before the program displays the following message). I get a
| message similar to program has been interrupted and has three or four
| buttons -- debug, end and help ( Im writing from memory). I chose debug
and
| when I enter VB editor I see that the line "For i = 1 To
..FoundFiles.Count"
| is highlighted in yellow.
|
| I then went to help ( Im very new to VB, just had a primer in C++ years
| back) and typed filesearch and somehow made very slight modifications to
| your orignal code which is given below and when I executed this it worked
| fine.
|
| If possible, please tell me why ur original code wasnt working. This would
| help me in better understanding of VB
|
| The lines I changes was to replace ur ".FileName = "*.xls"" with the
| following line -- " .FileType = msoFileTypeExcelWorkbooks"
|
| Though I dont know why the modified form works, I have one more doubt (
| which I have posted in a new post and in a different subject about
| "Displayalerts and screenupdating" ). The query is if I set
| .SearchSubFolders = True, then one of the subfolders had a excel file of
the
| same name as in the root
| folder. Inspite of this excel didnt display the message that 2 files with
| same name cannot be opened. If possible, Please tell me why this was
| happening.
|
|
| Sub OpenWorkbooksInLocation()
|
| Application.ScreenUpdating = False
| Dim i As Integer
| With Application.FileSearch
| .NewSearch
| .LookIn = "C:\CCAPPS\ttlview\TMP"
| .SearchSubFolders = False
| .FileType = msoFileTypeExcelWorkbooks
| .Execute
| For i = 1 To .FoundFiles.Count
| Set wb = Workbooks.Open(filename:=.FoundFiles(i))
| 'Macro code for each workbook here
| 'wb.Save
| 'wb.Close
| Next i
| End With
| Application.ScreenUpdating = True
|
| End Sub
|
|
| "William" wrote in message
| ...
| Hi Hari
|
| Which line of the code does Excel hang on?
|
| Are you sure theres is a folder called "C:\CCAPPS\ttlview\TMP"?
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "Hari" wrote in message
| ...
| | Hi William,
| |
| | Thanx a lot for the code.
| |
| | When I run it my excel hangs and even after waiting for 5 or 6 minutes
| there
| | is no result. I pressed Ctrl+break to get out of the code. Im using
| Excel
| | 2002 and Win 2000.
| |
| | When I enter the code after stopping it the line "For i = 1 To
| | .FoundFiles.Count" is highlighted in yellow.
| |
| | Please tell me how to overcome the same.
| |
| | Regards,
| | Hari
| | India
| |
| |
| | "William" wrote in message
| | ...
| | Hi Hari
| |
| | Sub OpenWorkbooksInLocation()
| | Application.ScreenUpdating = False
| | Dim i as integer
| | With Application.FileSearch
| | .NewSearch
| | .LookIn = "C:\CCAPPS\ttlview\TMP"
| | .SearchSubFolders = False
| | .FileName = "*.xls"
| | .Execute
| | For i = 1 To .FoundFiles.Count
| | Set wb = Workbooks.Open(FileName:=.FoundFiles(i))
| | 'Macro code for each workbook here
| | wb.Save
| | wb.Close
| | Next i
| | End With
| | Application.ScreenUpdating = True
| | End Sub
| |
| |
| | --
| | XL2002
| | Regards
| |
| | William
| |
| |
| |
| | "Hari" wrote in message
| | ...
| | | Hi,
| | |
| | | I have a folder "C:\CCAPPS\ttlview\TMP" and I want to open all the
| excel
| | | files in this automatically. ( there are around 20 to 25 files
| normally
| | in
| | | this folder)
| | |
| | | Once opened I will be running macros which will be collating data
| from
| | these
| | | files and doing some further operations. (I learned those macros
| from
| | the
| | | help of Newsgroups only).
| | |
| | | Please tell me if this is possible.
| | |
| | | Regards,
| | | Hari
| | | India
| | |
| | |
| |
| |
| |
| |
|
|
|
|

Hi William,

Thanx a lot for all the help and support u provided me.

Im very very grateful to u.

Regards,
Hari
India