Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmaticaly test more then 1000 excel files for "opening Errors"
Ref. Windows 2000 proff.
Excel 97, Excel 2K I would like to programmaticaly test more than 1000 excel files for "opening Errors" like ... 1)This file is not in a recognizable format .... or 2)'File Name.xls' cannot be accessed .... or Whatever opening error I think the macro shoul work like this. for each Excel file in c:/documents open the file, ( and without the user prompt ... ) if there is an excel error message ... log file name in bad c:\files.txt else log file name in good c:\files.txt end if next file Any Help ? Thanks. Best Regards. Robert. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmaticaly test more then 1000 excel files for "opening Errors"
some thing like below /
tim Sub CheckAll() 'adjust your path to suit Const sPath As String = "C:\Analysis\test\" Dim d As Worksheet Dim wb As Workbook, i As Integer Set d = ThisWorkbook.Sheets("Progress log") With d .Cells.Clear 'Set up Column Headers .Cells(1, 1) = "Path" .Cells(1, 2) = "State" End With With Application.FileSearch .NewSearch .LookIn = sPath .SearchSubFolders = True .Filename = "*.xls" If .Execute() Then For i = 1 To .FoundFiles.Count d.Cells(i + 1, 1).Value = .FoundFiles(i) Set wb = Nothing On Error Resume Next Set wb = Workbooks.Open(.FoundFiles(i)) On Error GoTo 0 If wb Is Nothing Then d.Cells(i + 1, 1).Value = Err.Description Err.Clear Else d.Cells(i + 1, 2).Value = "OK" wb.Close False End If Next i End If End With End Sub "Robert" wrote in message ... Ref. Windows 2000 proff. Excel 97, Excel 2K I would like to programmaticaly test more than 1000 excel files for "opening Errors" like ... 1)This file is not in a recognizable format .... or 2)'File Name.xls' cannot be accessed .... or Whatever opening error I think the macro shoul work like this. for each Excel file in c:/documents open the file, ( and without the user prompt ... ) if there is an excel error message ... log file name in bad c:\files.txt else log file name in good c:\files.txt end if next file Any Help ? Thanks. Best Regards. Robert. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmaticaly test more then 1000 excel files for "opening Errors"
It works.
Thank You. Regards, Robert. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel files opening in "read only" | Excel Discussion (Misc queries) | |||
On opening Excel, 4 non ".xls" files open that I cannot delete | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Solution: How to programmaticaly leave "edit mode" | Excel Programming | |||
Looking for VB code to test for "RING" , "BUSY" disconnects or other signals | Excel Programming |