File Not Found Msg Because Sheet is Missing ?

[Dan Thompson]

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

 

[Ron de Bruin]

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

 

[Dan Thompson]

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.

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

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

.

 

[Ron de Bruin]

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

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.

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

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

.