Existing File Not Found

[Len]

Hi,

I'm not sure why the following vba code does not work as intended if
the existing excel file is found and activate but it keeps prompting
the error message that the file is not found

Dim Wbk As Workbook
For Each Wbk In Workbooks
If Wbk.Name = "GL.xls" Then
Windows("GL.xls").Activate
Else
MsgBox " The required file is not found - process end"
Exit Sub
End If
Next Wbk

Please help, thanks in advance

Regards
Len

 

[Tim Williams]

Your code is giving the "not found" for each open workbook which isn't the
one you want.

Try this instead

'*********************************
Dim wb as Excel.Workbook

on error resume next
set wb=workbooks("GL.xls")
on error goto 0

if not wb is nothing then
wb.activate
else
msgbox "Required workbook not found!"
end if
'********************************



Tim

 

[Len]

Hi Tim,

Thanks for your reply and your codes

It works great !


I have another question on how to set vba code in such way that when
anyone of subrotines has prompted "Exit Sub" will exit the main
program

E.g

Sub main()
.....
.....
.....
Call abc
End Sub

Sub abc()
.....
.....
Exit Sub
.......
End Sub

Sub def()
.....
.....
Exit Sub
.......
End Sub

Thanks again

Regards
Len

 

[Chip Pearson]

I have another question on how to set vba code in such way that when

anyone of subrotines has prompted "Exit Sub" will exit the main
program

You should write the subordinate procedures as functions that return
either True or False, indicating whether further processing should
take place. E.g.,

Sub MainProc()
Dim B As Boolean
' some code
B = AAA()
If B = False Then
Exit Sub
End If
' more code
End Sub

Function AAA() As Boolean
' some code
AAA = True ' or False
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[AB]

Or, as per your other thread on the same thing (and my response in
there) - just use 'End' and it will stop all routines.

 

[Chip Pearson]

just use 'End' and it will stop all routines.

That is generally not a good idea. The End statement will cause all
public variables, including those used by custom menus or command
buttons, to be dumped from memory. Moreover, objects are dumped from
memory without going through their normal teardown code. This can lead
to any number of problems. Using End is a brute force method where a
more graceful shutdown is a better way to go.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[AB]

I couldn't agree more with you Chip, especially bearing in mind that
i've learned all my stuff pretty much reading your fantastic site back
and forth.
It's just from what i read in the initial requests i recognized an
issue that i was trying to solve a few years back and 'End' was
exactly what i was after back then and this sounded like the same as
with simple setups/routines that's all you need.

 

[Rick Rothstein]

It's just from what i read in the initial requests i recognized an

issue that i was trying to solve a few years back and 'End' was
exactly what i was after back then and this sounded like the same as
with simple setups/routines that's all you need.

In my opinion, End is **never** the correct statement to ever use in active
code (for the very reasons Chip posted). Back in my volunteering days for
the compiled version of Visual Basic (the End statement is just as verboten
there as well), I explained it this way...

"Suffice it to say that the End statement stops your
program in the same way running into a brick wall
stops your car... immediately. You don't get a chance
to coast to a stop and turn your key to the off position,
open the door and exit the vehicle. The same thing
happens with the End statement... BOOM!, everything
stops dead in its tracks right then and there and the
program ends."

The following is from Remarks section of the Help Files for the End
Statement and it expands on this concept...

"When executed, the End statement resets all module-level
variables and all static local variables in all modules. To
preserve the value of these variables, use the Stop
statement instead. You can then resume execution while
preserving the value of those variables.

Note: The End statement stops code execution abruptly,
without invoking the Unload, QueryUnload, or Terminate
event, or any other Visual Basic code. Code you have
placed in the Unload, QueryUnload, and Terminate events
of forms and class modules is not executed. Objects
created from class modules are destroyed, files opened
using the Open statement are closed, and memory used
by your program is freed. Object references held by other
programs are invalidated.

The End statement provides a way to force your program
to halt. For normal termination of a Visual Basic program,
you should unload all forms. Your program closes as soon
as there are no other programs holding references to
objects created from your public class modules and no
code executing."

I note that this description references the compiled VB form events "Unload"
and "QueryUnload" (the Help Files are shared between the compiled VB and VBA
worlds and sometimes descriptions from one leak over into the other)...
Excel UserForms do not have these two events available, it only has the
QueryClose event in their place, but the warning about the End statement
applies to this event in the same way it applies the the compiled VB events.

 

[AB]

I guess we all made assumptions here - I assumed that Len might want
to halt/drop every running procedure/form when a specific event
happens (that leads to the Exit Sub) while everyone else assumed that
he doesn't.
All I'm saying that 'never' is too strong a word for my liking and
despite the fact that 'End' is "crashing into a wall" - sometimes that
suffices - and the 'End' there is for a reason.
Now, having said that, it's not recommended to use when you don't know
what you're doing but while all is under control and the procedure is
simply calling sub after sub and a fatal error (predefined event)
solicits getting out immediately from anything - I, as a business user
of my own codes, find nothing wrong with it.
I'm sure not that many would agree and I'm happy to back down - just
expressed my opinion.

And my apologies to Len if my suggestion has been misleading.

 

[Len]

Hi All,

Sorry for delay in replying my post as I was away for the while
weekend

Thanks for your great helps! and your suggestions

There is nothing to be blamed, AB

Sorry, my post did not state clearly at the first place and it should
drop every sub procedure when a specific event
happens that leads to Exit Sub

Anyway, your comments and suggestions would help me to deeply
understand how it should work in different situations

Thanks and Regards
Len