PDA

View Full Version : Solved: if sheet not found end sub, if found goto



mperrah
11-18-2007, 04:32 AM
I am trying to open a workbook to import data from several sheets.
In some of older versions the sheet names were different.
I need to scan the newly openned book for a specific sheet.
If the sheet is found, proceed with the code.
If the sheet is not present, show msgbox and hault code.

I've searched and tried, but this is stumping me..

This is a test (not working):
Sub ImportSummary()
Dim wbRecent As String
Dim wbCurrent As String

wbRecent = Worksheets("library").[N26] ' refers to cell with open workbook name
wbCurrent = Worksheets("library").[N28]

Dim sht As Worksheet
Dim ws As Worksheet
ws = Worksheets("Summary")
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
If Not IsError(Application.Match(sht.Name, ws, 0)) Then
GoTo yesSummary
ElseIf IsError(Application.Match(sht.Name, ws, 0)) Then
MsgBox "Data not compatible with update" & vbCrLf _
& "Contact RAT Modesto to update manually", , "Update Problem Encountered"
GoTo noSummary
End If
Next sht
yesSummary:
Windows(wbRecent).Activate
Sheets("Summary").Select
Range("B2:C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(wbCurrent).Activate
Sheets("Summary").Select
Range("B2").Select
ActiveSheet.Paste
noSummary:
End Sub

part of an error that occurs is named ranges.
I have named ranges that are on both old and new sheets,
Excel asks if I wan't the old reference or the new sheets name, I always want the new sheet, and do not want any references to other books.

Thank you for any help.

Mark

Bob Phillips
11-18-2007, 04:59 AM
Not really sure I understand all of this, but you can check if the sheet exists with



On Error Resume Next
Set sh = ThisWorkbook.Worksheets(sht.Name)
On Error Goto 0
If Not sh Is Nothing

mperrah
11-18-2007, 05:34 AM
thank you again Bob,
you make it look so easy
I imagine 7204 posts probably taught you alot by teaching too?
you are very appreciated.
Mark

mperrah
11-18-2007, 05:50 AM
This what I came up with.
I get the msgbox errore, but the couls imports?
Do you see what I'm doing wrong?

Sub ImportWPRInspector()
Dim wbRecent As String
Dim wbCurrent As String
Dim sh As Worksheet

wbRecent = Worksheets("library").[N26]
wbCurrent = Worksheets("library").[N28]

Application.DisplayAlerts = False
On Error Resume Next
Set sh = Windows(wbRecent).Worksheets("WPR")
On Error GoTo 0
If sh Is Nothing Then
MsgBox "Data not compatible with update" & vbCrLf _
& "Contact RAT Modesto to update manually", , "Problem Encountered - Importing WPR Inspector"
GoTo noWPR
Else
GoTo yesWPR
End If
yesWPR:
Windows(wbRecent).Activate
Sheets("WPR").Select
Range("D7").Select
Selection.Copy
Windows(wbCurrent).Activate
Sheets("WPR").Select
Range("D7").Select
ActiveSheet.Paste
noWPR:
End Sub

Bob Phillips
11-18-2007, 06:09 AM
Nothing jumps out at me, but it is difficult to fully analyse when I cannot see what is in N26 and N28 etc.

Can you post the workbooks?

mperrah
11-18-2007, 12:11 PM
Here is my latest.
the module modVersion has most of the code in play...

I test if not found show error msgbox and skip sub.
Else proceed through sub without message
(at least that is what I'm expecting)
Thanks for your patience

cell n26 is the resulting file name (string) of the file the user chose to update from
cell n28 is the name of this file (string)
They are both open at this part of the code, so I didn't use path / file name.
It was running fine till I tried importing an old version with no "library" sheet.
So I am trying to build an error catcher for this...

Mark

mperrah
11-18-2007, 12:17 PM
note
If the old file openned has no library sheet, most of the data will be in the wrong columns and rows (and sometimes not existent), so I prompt the user to contact me to manually transfer the data for them in a msgbox
(I only have 18 users with varying degrees of compitence and/or patience, so I'm trying to make it user input limited)
Mark

Bob Phillips
11-18-2007, 12:34 PM
So let me make sure I get this right.

You are saying that if there is no library sheet, you get the warning message, but it still goes to YesWPR and does it stuff, it doesn.t go to noWPR? Works fine for me.

Or is the caller that carries on?

If the latter, change ImportWPRInspector to a function



Public Function ImportWPRInspector() As Boolean
Dim wbRecent As String
Dim wbCurrent As String
Dim sh As Worksheet

ImportWPRInspector = True

wbRecent = Worksheets("library").[N26]
wbCurrent = Worksheets("library").[N28]

Application.DisplayAlerts = False
On Error Resume Next
Set sh = Windows(wbRecent).Worksheets("WPR")
On Error GoTo 0
If sh Is Nothing Then
MsgBox "Data not compatible with update" & vbCrLf _
& "Contact RAT Modesto to update manually", , "Problem Encountered - Importing WPR Inspector"
ImportWPRInspector = False
GoTo noWPR
Else
GoTo yesWPR
End If
yesWPR:
Windows(wbRecent).Activate
Sheets("WPR").Select
Range("D7").Select
Selection.Copy
Windows(wbCurrent).Activate
Sheets("WPR").Select
Range("D7").Select
ActiveSheet.Paste
noWPR:
End Sub


and change the caller



Sub MasterUpdate()
Dim current As String
Dim recent As String
Dim wbOld As Workbook
Dim wbToClose As Workbook

recent = ThisWorkbook.Worksheets("library").[N26]
current = ThisWorkbook.Worksheets("library").[N28]

For Each wbOld In Workbooks
If wbOld.Name <> ThisWorkbook.Name Then
Set wbToClose = wbOld
End If
Next wbOld

Application.DisplayAlerts = False

Call ImportChartMA
Call ImportSummaryMA
Call ImportQCDetail
Call ImportSummary
Call ImportDataInspector
If ImportWPRInspector Then

Windows(current).Activate
Sheets("library").Select
[N30].Value = Now

Windows(recent).Activate
ActiveWindow.Close

Call CreateMenu ' updates version number

Sheets("library").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
Range("J12").Select

MsgBox "Data import complete", , "Version Update"
End If

With Application
.DisplayAlerts = True
.Calculation = xlCalculationManual
End With

End Sub

mperrah
11-18-2007, 03:22 PM
I like your way of thinking.
I admit, mine was done at 430 AM after a long day of honey-do lists.
How can I make your function test for any specified worksheet?
To test for any name of a sheet I run through it in a variable?
There a three pages that I test for being present,
depending on the sheet to be updated,
as well as some data in a specific cell on two other pages...
How to make the worksheet name a variable or string to pass to the function and then run, or continue a sub if result is true?
and show message and stop sub if false...
I think this might be the most efficient, look forward to your ideas.

Mark

Bob Phillips
11-18-2007, 03:28 PM
Public Function ImportWPRInspector(ByVal LookFor As String) As Boolean
Dim wbRecent As String
Dim wbCurrent As String
Dim sh As Worksheet

ImportWPRInspector = True

wbRecent = Worksheets("library").[N26]
wbCurrent = Worksheets("library").[N28]

Application.DisplayAlerts = False
On Error Resume Next
Set sh = Windows(wbRecent).Worksheets(LookFor)
On Error Goto 0
If sh Is Nothing Then
MsgBox "Data not compatible with update" & vbCrLf _
& "Contact RAT Modesto to update manually", , "Problem Encountered - Importing WPR Inspector"
ImportWPRInspector = False
Goto noWPR
Else
Goto yesWPR
End If
yesWPR:
Windows(wbRecent).Activate
Sheets("WPR").Select
Range("D7").Select
Selection.Copy
Windows(wbCurrent).Activate
Sheets("WPR").Select
Range("D7").Select
ActiveSheet.Paste
noWPR:
End Sub


And then call like



If ImportWPRInspector(LookFor:="WPR") Then

mperrah
11-18-2007, 03:35 PM
Something like this - incomplete, not tested
Public Function TestForSheet() As Boolean
Dim wbOld As String
Dim wbNew As String
Dim sh As Worksheet
Dim testSh As Worksheet ' passed from sub - Possible?
Dim subPass As String ' name of sub to return to if sheet found
Dim subFail As String ' name of sub to use if sheet fail - can be end sub
Dim RunSub As Boolean

RunSub = True

wbOld = Worksheets("library").[N26] ' cell with string of open old worksheet name *.xls
wbNew = Worksheets("library").[N28] ' cell with string of this worksheet name

Application.DisplayAlerts = False
On Error Resume Next
Set sh = Windows(wbOld).Worksheets(testSh) ' passed from sub?
On Error GoTo 0
If sh Is Nothing Then
MsgBox "File structure not compatible with update" & vbCrLf _
& "Please contact RAT Modesto to update manually", , "Problem Encountered Importing Old File"
ImportWPRInspector = False
GoTo subPass
Else
GoTo subFail
End If
subPass:
'return to sub(testSh)
subFail:
' hault update
End Function

I was thinking something used like this
the letter here for the onkey event is passed to the sub

Will it work with a sub and/or function
Sub action_()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("")
End Sub
Sub insert_letter(vletter As String)
If ActiveSheet.Name = "Data" Then
' if not inside target - on target sheet
If ActiveCell.Column < 18 _
Or ActiveCell.Column > 44 _
Or ActiveCell.Row > 1000 Then
SendKeys "{F2}"
If vletter = "p" Then
vletter = "1"

mperrah
11-18-2007, 04:02 PM
If you have several subs,
each with unique (or not unique) goto marks,
how do you goto a sub and start at the goto mark?
Like going to an anchor in html.
Does vba have something like that?
Or can you rig it so that you,
from a sub (#1) call the function to test for a sheet,
in the sub (#1) have a boolean set to false,
with an if statement to direct movement if true and for false
based on the boolen result of the function..
if true goto true:
if false enditall:

am I close?

Bob Phillips
11-18-2007, 04:07 PM
That is exactly what I showed you in post #8, but without those nasty GoTos.

mperrah
11-18-2007, 04:51 PM
I'm sorry,
can you show me how the code moves through the checking.
The call to the subs are before the Function if call
and the Then of the if had no subs after it?

I guess if "library" is missing it should all just hault,
but I would like to know how this works (still testing)

post #8 errors on the If ImportWPRInspector Then ,
of the caller

Bob Phillips
11-18-2007, 04:59 PM
In MasterUpdate the initial calls are made then

If ImportWPRMaster Then

passes control over to that function, which does its stuff and returns True or False, and the code follows Then path if that functions returns True, else it skips over it all until the End If.

mperrah
11-18-2007, 10:31 PM
Shows false no matter what I input (existing or not)
changed If sh Is Nothing Then
to If Not sh is nothing then
and it says true to everything (existing or not)
in a worksheet i use


=SheetTester("library")

I also let the function inserter find mine (user defined)
and shows true or false, as noted above

Public Function SheetTester(ByVal LookFor As String) As Boolean
Dim wbOld As String
Dim wbNew As String
Dim sh As Worksheet

SheetTester = False

wbOld = ThisWorkbook.Worksheets("library").[N26]
wbNew = ThisWorkbook.Worksheets("library").[N28]

With Application
.Calculate
End With

On Error Resume Next
Set sh = Windows(wbOld).Worksheets(LookFor)
On Error GoTo 0
If sh Is Nothing Then
MsgBox "Format not compatible with update" & vbCrLf _
& "Contact RAT Modesto to update manually", , "Problem Encountered During Update"

SheetTester = False
Else
MsgBox "It works"
SheetTester = True
End If
End Function

mperrah
11-18-2007, 11:22 PM
slight change
Moved the test workbook name from the sheet to code as variable
to avoid referencing a sheet that may be hidden

Public Function SheetTester(ByVal LookFor As String) As Boolean
Dim wbOld As String
Dim wbNew As String
Dim sh As Worksheet
Dim w As Workbook

SheetTester = False
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
wbOld = w.Name
wbNew = ThisWorkbook.Name
End If
Next w
On Error Resume Next
Set sh = Windows(wbOld).Worksheets(LookFor)
On Error GoTo 0
If Not sh Is Nothing Then
MsgBox "Format not compatible with update" & vbCrLf _
& "Contact RAT Modesto to update manually", , "Problem Encountered During Update"
SheetTester = False
Else
MsgBox "It works"
SheetTester = True
End If
End Function
Sub testSheet()
If SheetTester(LookFor:="test") Then
ThisWorkbook.Worksheets("library").Range("h26").Value = "true"
Else
ThisWorkbook.Worksheets("library").Range("h26").Value = "false"
End If
End Sub

This still only gives one result.
If the function is worded as If Not sh Is Nothing Then, it shows true
if sh is nothing then shows false, both regardless of what is in the (lookfor:="xxx")

mperrah
11-19-2007, 11:49 AM
Modified to this for testing. This Works correctly
But without the workbook reference.
How can I add in a reference to a different workbook for this function?
Public Function ThisWBSheetTest(ByVal LookFor As String) As Boolean
Dim wbOld As String
Dim wbNew As String
Dim sh As Worksheet
Dim w As Workbook

ThisWBSheetTest = False

On Error Resume Next
Set sh = Worksheets(LookFor) ' workbook(other_open).worksheets(LookFor)
On Error GoTo 0
If sh Is Nothing Then
MsgBox "sh is nothing"
ThisWBSheetTest = False
Else
MsgBox "sh Not is nothing"
ThisWBSheetTest = True
End If
End Function

mperrah
11-19-2007, 11:58 AM
I got it (thanks to xld ... again)

Public Function SheetTester(ByVal LookFor As String) As Boolean
Dim wbOld As String
Dim wbNew As String
Dim sh As Worksheet
Dim w As Workbook

SheetTester = False
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
wbOld = w.Name
wbNew = ThisWorkbook.Name
End If
Next w
On Error Resume Next
Set sh = Workbooks(wbOld).Worksheets(LookFor)
On Error GoTo 0
If sh Is Nothing Then
MsgBox "sheet not found"
' MsgBox "Format not compatible with update" & vbCrLf _
' & "Contact RAT Modesto to update manually", , "Problem Encountered During Update"
SheetTester = False
Else
MsgBox "sheet found"
SheetTester = True
End If
End Function
I was calling to the other workbook with windows(wbOld)
which switches the active window.
Just putting workbooks(wbOld) got it to work.
I guess the active window did not need to be switched for the function.
Very cool.
So, if anyone needs a function to test if a worksheet is present,
in the book or another, look no further...
Thanks xld,

Mark