Assistance with macro to determine if a specific worksheet is pres

[Guest]

I have a list of workbooks and need to determine if a specific worksheet (say
Sheet3) is present in that workbook. I have the following:

Column A: Y:\BLAH\BLAH\Blah
Column G: filename.xls

The workbook is in the following location
Y:\BLAH\BLAH\Blah\filename.xls

The data starts on row 2 and I can determine the last row of data. I'd
like to have something written to column H if Sheet3 is present in the
workbook.

Thanks in advance,
Barb Reinhardt

 

[Bob Phillips]

For i = 2 To Cells(Rows.Count,"A").End(xlUp).Row
Set oWB = Workbooks.open Filename:= _
cells(i,"A").Value & "\" & Cells(i,"G").Value
Set sh = Nothing
on Error Resume Next
Set sh = oWB.Worksheets("Sheet3")
On Error Goto 0
If Not sh Is Nothing Then Cells(i,"H").Value = "yes"
oWB.Close SaveChanges:=False
Next i

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Patrick Molloy]

you can create a simple procedure for this purpose. The code below simply
checks each cell in A. If there's text we assume its a path , concatenate it
to for the full name and use the DIR() function to see if it finds the file.
If it does, then open the file, and check if th esheet is there.

SUB CheckFile()
DIM FN As String
DIM RW as Long
for RW = 2 to 1000
if CELLS(rw,1)<>"" then
FN = DIR( cells((RW,1) & "\" & cells(rw,"G") )
if FN <>" then
cells(rw,"H") = SheetExists(cells((RW,1) & "\" &
cells(rw,"G"),"Sheet3")
end if
end if
next
END SUB
FUNCTION SheetExists(wbname as string,sheetname as string) as boolean
DIM WB As Workbook
DIM WS As Worksheet
SET WB = Workbooks.Open(wbname)
on error resume next
Set WS = WB.Worksheets(sheetname)
IF Err.Number = 0 then
SheetExists=TRUE
Else
Err.Clear
End If
WB.Close FALSE
END FUNCTION

 

[Guest]

Bob,

I have a bit of a glitch that I'm sure you can help with. There are some
documents that when opened, display a message asking if I want to update
links. I don't. What needs to be changed in the following code?

Thanks,
Barb

 

[Bob Phillips]

Hi Barb,

This is what I think you want

Set oWB = Workbooks.open _
Filename:= cells(i,"A").Value & "\" & Cells(i,"G").Value, _
UpdateLinks:= 0


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Another glitch (due to moving of servers)

This works if I have the location of the file as Y:\folder\filename.xls
What might need to be modified if I can access the file using this path
http://folder/filename.xls

Thanks,
Barb Reinhardt

 

[Guest]

The server issue has been resolved, but I'm getting the following message
periodically:

This workbook contains one or more links that connot be updated.

* To change the source of links, or attempt to update values again, click
Edit Links.
* To open the workbook as is, click Continue

This is the code I'm using
Sub SheetPresent()
Dim oWB As Workbook
Dim aWB As Workbook
Dim aWS As Worksheet
Dim sName As String

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet
sName = "Title"
Range("H1").Select
ActiveCell.FormulaR1C1 = sName

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Set oWB = Workbooks.Open(Cells(i, "B"), UpdateLinks = 0)
Set sh = Nothing
On Error Resume Next
Set sh = oWB.Worksheets(sName)
Debug.Print i, "After set sh", sh;
On Error GoTo 0
If Not sh Is Nothing Then
Debug.Print "sh is not nothing"
aWB.Activate
aWS.Activate
Cells(i, "H").Value = "1"
Name = ActiveSheet.Name
Debug.Print Name
Else
Cells(i, "H").Value = "0"
End If
oWB.Close SaveChanges:=False
Next i

End Sub