Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Not Found Msg Because Sheet is Missing ?
Sub ListWorkbooks()
Dim Directory As String Dim FileName As String Dim rw As Long Dim LastUpdateDate As Date 'Change the directory below as needed calls shell app function so user 'can browse for the dir where the excel files are located Directory = BrowseForFolder If Left(Directory, 1) < "\" Then Directory = Directory & "\" End If rw = 2 FileName = Dir(Directory & "*.xls") Do While FileName < "" 'Verifies file name string pattern to only use file names that match the 'pattern of the 4th char in the excel file name being a space and the '6th char in the excel file name being a Underscore any excel file 'names that do not have that pattern in the file name string are ignored. Workbooks(FileName).Worksheets("PointCount").Cells (2, 5).Value If Mid(FileName, 4, 1) = " " And Mid(FileName, 6, 1) = "_" Then Workbooks("Master.xls").Worksheets("UpdatedList"). Cells(rw, 1).Value = Left(FileName, 3) 'THE FOLLOWING CODE IS WHERE MY PROBLEM STARTS 'The issue is when the following code adds the formula to a cell in the 'Master.xls file which links to another Excel workbook that does not 'contain a worksheet named "PointCount" I get a pop up window that 'says "File Not Found" and the pop up window expects me to browse for 'a file that contains a worksheet named "PointCount" 'I do not want this "File Not Found" pop up window to appear. If the 'formula links to a workbook that is missing the "PointCount" 'Worksheet I simply want my macro to skip adding that formula to my 'Master.xls workbook and move on to the next file. Workbooks("Master.xls").Worksheets("UpdatedList"). Cells(rw, 2).FormulaR1C1 = "='[" & FileName & "]PointCount'!R2C5" End If rw = rw + 1 FileName = Dir Loop End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete values in sheet 2 that arre found in sheet 1 | Excel Discussion (Misc queries) | |||
colum is not found thats missing a column B how to find it | Excel Worksheet Functions | |||
open new microsoft excel sheet and found my last same file all ti. | Excel Worksheet Functions | |||
FOUND LOST FILE BUT COPY IS MISSING | Excel Worksheet Functions | |||
How do I reference external data from a file, file name found in . | Excel Discussion (Misc queries) |