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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Not Found Msg Because Sheet is Missing ?
Hi Dan
I reply in your other thread with this You can test it with a Excel4 macro like I do here http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Thompson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Not Found Msg Because Sheet is Missing ?
Yes I see that Ron and I thank you but I went to the link and the code seems
to long and also I need to check for the existance of a worksheet with in a workbook without ever opening the workbook I am checking. I have resolved this issue now with a much shorter code. Here is the solution I came up with. Function CheckSheetExist(Pth As String, Wb As String, Sh As String) As Boolean On Error GoTo NBG If Application.ExecuteExcel4Macro("'" & Pth & "[" & Wb & "]" & Sh & "'!R1C1") "" Then CheckSheetExist = True Else NBG: CheckSheetExist = False Err.Clear End If On Error GoTo 0 End Function Sub CheckSheet() 'Checks for the existance of a Worksheet with in a closed workbook Dim check As Boolean check = CheckSheetExist("C:\MyPath\", "MyWorkbook.xls", "MySheet") MsgBox check 'Returns True or False End Sub Dan Thompson. "Ron de Bruin" wrote: Hi Dan I reply in your other thread with this You can test it with a Excel4 macro like I do here http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Thompson" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Not Found Msg Because Sheet is Missing ?
I see that you not see my code in the macro
On Error Resume Next SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1)) If Err.Number < 0 Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Thompson" wrote in message ... Yes I see that Ron and I thank you but I went to the link and the code seems to long and also I need to check for the existance of a worksheet with in a workbook without ever opening the workbook I am checking. I have resolved this issue now with a much shorter code. Here is the solution I came up with. Function CheckSheetExist(Pth As String, Wb As String, Sh As String) As Boolean On Error GoTo NBG If Application.ExecuteExcel4Macro("'" & Pth & "[" & Wb & "]" & Sh & "'!R1C1") "" Then CheckSheetExist = True Else NBG: CheckSheetExist = False Err.Clear End If On Error GoTo 0 End Function Sub CheckSheet() 'Checks for the existance of a Worksheet with in a closed workbook Dim check As Boolean check = CheckSheetExist("C:\MyPath\", "MyWorkbook.xls", "MySheet") MsgBox check 'Returns True or False End Sub Dan Thompson. "Ron de Bruin" wrote: Hi Dan I reply in your other thread with this You can test it with a Excel4 macro like I do here http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Thompson" wrote in message ... 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 . |
Reply |
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) |