Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for existing worksheet in all open excel files
Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any
open excel file. I would also like my macro to copy this worksheet if it isn't in the current workbook that the macro was run from. This is the code I found in the newsgroups so far which is a start. Thanks in advance! Public Function WSExist(wsname As String) As Boolean 'returns true if worksheet exists in the active workbook Dim objWorksheet As Object On Error Resume Next WSExist = False Set objWorksheet = ActiveWorkbook.Sheets(wsname) If Err = 0 Then WSExist = True End Function Sub TestMatrix_Chk() If WSExist("Test Matrix") = False Then MsgBox ("No Test Matrix sheet found!") End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for existing worksheet in all open excel files
Seth,
Try: Sub findTestMatrix() Dim wb As Workbook Dim wSht As Worksheet For Each wb In Workbooks Debug.Print "Searching: "; wb.Name If wb.Name < ThisWorkbook.Name Then For Each wSht In wb.Worksheets Debug.Print "Found: "; wSht.Name If wSht.Name = "TestMatrix" Then wSht.Cells.Copy Destination:=ThisWorkbook.Sheets(1).Range("A1") End If Next wSht End If Next wb Set wb = Nothing Set wSht = Nothing End Sub The 'debug.print' are just there so you can see what's going on if you step through it. You may want to change the 'Destination' sheet. HTH Tim "Seth" wrote in message ... Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any open excel file. I would also like my macro to copy this worksheet if it isn't in the current workbook that the macro was run from. This is the code I found in the newsgroups so far which is a start. Thanks in advance! Public Function WSExist(wsname As String) As Boolean 'returns true if worksheet exists in the active workbook Dim objWorksheet As Object On Error Resume Next WSExist = False Set objWorksheet = ActiveWorkbook.Sheets(wsname) If Err = 0 Then WSExist = True End Function Sub TestMatrix_Chk() If WSExist("Test Matrix") = False Then MsgBox ("No Test Matrix sheet found!") End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for existing worksheet in all open excel files
Public Function WSExist(wsname As String) As Boolean
'returns true if worksheet exists in the active workbook Dim objWorksheet As Worksheet On Error Resume Next Set objWorksheet = ActiveWorkbook.Worksheets(wsname) On Error GoTo 0 WSExists = Not objWorksheet Is Nothing End Function Sub TestMatrix_Chk() If WSExist("Test Matrix") = False Then MsgBox ("No Test Matrix sheet found!") End If End Sub HTH -- AP "Seth" a écrit dans le message de ... Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any open excel file. I would also like my macro to copy this worksheet if it isn't in the current workbook that the macro was run from. This is the code I found in the newsgroups so far which is a start. Thanks in advance! Public Function WSExist(wsname As String) As Boolean 'returns true if worksheet exists in the active workbook Dim objWorksheet As Object On Error Resume Next WSExist = False Set objWorksheet = ActiveWorkbook.Sheets(wsname) If Err = 0 Then WSExist = True End Function Sub TestMatrix_Chk() If WSExist("Test Matrix") = False Then MsgBox ("No Test Matrix sheet found!") End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for existing worksheet in all open excel files
Thanks Tim. I tried the code you posted and got a syntax error for
Destination:=ThisWorkbook.Sheets(1).Range("A1") Any clue why? "Tim Barlow" wrote: Seth, Try: Sub findTestMatrix() Dim wb As Workbook Dim wSht As Worksheet For Each wb In Workbooks Debug.Print "Searching: "; wb.Name If wb.Name < ThisWorkbook.Name Then For Each wSht In wb.Worksheets Debug.Print "Found: "; wSht.Name If wSht.Name = "TestMatrix" Then wSht.Cells.Copy Destination:=ThisWorkbook.Sheets(1).Range("A1") End If Next wSht End If Next wb Set wb = Nothing Set wSht = Nothing End Sub The 'debug.print' are just there so you can see what's going on if you step through it. You may want to change the 'Destination' sheet. HTH Tim "Seth" wrote in message ... Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any open excel file. I would also like my macro to copy this worksheet if it isn't in the current workbook that the macro was run from. This is the code I found in the newsgroups so far which is a start. Thanks in advance! Public Function WSExist(wsname As String) As Boolean 'returns true if worksheet exists in the active workbook Dim objWorksheet As Object On Error Resume Next WSExist = False Set objWorksheet = ActiveWorkbook.Sheets(wsname) If Err = 0 Then WSExist = True End Function Sub TestMatrix_Chk() If WSExist("Test Matrix") = False Then MsgBox ("No Test Matrix sheet found!") End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for existing worksheet in all open excel files
An unfortunate line break:
wSht.Cells.Copy _ Destination:=ThisWorkbook.Sheets(1).Range("A1") (notice the spacebar, underscore in the line above) Seth wrote: Thanks Tim. I tried the code you posted and got a syntax error for Destination:=ThisWorkbook.Sheets(1).Range("A1") Any clue why? "Tim Barlow" wrote: Seth, Try: Sub findTestMatrix() Dim wb As Workbook Dim wSht As Worksheet For Each wb In Workbooks Debug.Print "Searching: "; wb.Name If wb.Name < ThisWorkbook.Name Then For Each wSht In wb.Worksheets Debug.Print "Found: "; wSht.Name If wSht.Name = "TestMatrix" Then wSht.Cells.Copy Destination:=ThisWorkbook.Sheets(1).Range("A1") End If Next wSht End If Next wb Set wb = Nothing Set wSht = Nothing End Sub The 'debug.print' are just there so you can see what's going on if you step through it. You may want to change the 'Destination' sheet. HTH Tim "Seth" wrote in message ... Hi, I'm trying to check to see if a sheet named "Test Matrix" exists in any open excel file. I would also like my macro to copy this worksheet if it isn't in the current workbook that the macro was run from. This is the code I found in the newsgroups so far which is a start. Thanks in advance! Public Function WSExist(wsname As String) As Boolean 'returns true if worksheet exists in the active workbook Dim objWorksheet As Object On Error Resume Next WSExist = False Set objWorksheet = ActiveWorkbook.Sheets(wsname) If Err = 0 Then WSExist = True End Function Sub TestMatrix_Chk() If WSExist("Test Matrix") = False Then MsgBox ("No Test Matrix sheet found!") End If End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't open existing files by double-clicking. | Excel Discussion (Misc queries) | |||
It takes two full minutes to open existing files in Office 2003 | Excel Discussion (Misc queries) | |||
When I open a an existing excel worksheet, it duplicates itself a. | Excel Discussion (Misc queries) | |||
Open only existing Excel Files in a given Range | Excel Programming | |||
Open only existing Excel Files in a given Range | Excel Programming |