Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is pres
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is pres
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) "Barb Reinhardt" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is pres
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 "Barb Reinhardt" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is
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" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is
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) "Barb Reinhardt" wrote in message ... 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" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is
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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with macro to determine if a specific worksheet is
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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a macro to be non worksheet specific? | Excel Worksheet Functions | |||
Avoiding the worksheet specific name in a macro | Excel Discussion (Misc queries) | |||
How to run specific macro on selected worksheet? | Excel Discussion (Misc queries) | |||
make a macro 'worksheet specific' | Excel Discussion (Misc queries) | |||
Help with a macro to open to a specific worksheet | Excel Worksheet Functions |