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
(e-mail address removed)
| 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
|
|
| | > 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
| >
| > (e-mail address removed)
| >
| > | > | 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
| > |
| > |
| > | | > | > 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
| > | >
| > | > (e-mail address removed)
| > | >
| > | > | > | > | 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
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
