Existing File Not Found
in [Programming]
|
From: Len on 24 Mar 2010 23:51 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
From: Tim Williams on 25 Mar 2010 00:09 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" <ltong2000mal(a)yahoo.co.uk> wrote in message news:e7e6be7d-0b85-4b65-a2be-d45a8907304f(a)l11g2000pro.googlegroups.com... > 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
From: Len on 25 Mar 2010 03:46 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
From: Chip Pearson on 25 Mar 2010 08:48 >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 On Thu, 25 Mar 2010 00:46:03 -0700 (PDT), Len <ltong2000mal(a)yahoo.co.uk> wrote: >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
From: AB on 25 Mar 2010 09:23
Or, as per your other thread on the same thing (and my response in there) - just use 'End' and it will stop all routines. |