If Workbook not Open Then Open It

S

scain2004

How do check to see if a workbook's open, and if it's not already open
then open it?

I've looked in the forum, but nobody seems to be looking for the abov
condition.

I've got this already:

jbl = "Production Schedule 040512.xls"
checkForBook = CBool(Len(Workbooks(jbl).name))

If Not checkForBook Then

'What goes here?'

End I
 
T

Tushar Mehta

Umm...in addition to Wolf's suggestion, you also need an error trap.
Try something along the lines of

Sub testIt()
Dim JBL As String, JBLWB As Workbook
JBL = "Production Schedule 040512.xls"
On Error Resume Next
Set JBLWB = Workbooks(JBL)
On Error GoTo 0
If JBLWB Is Nothing Then
Set JBLWB = Workbooks.Open(JBL)
End If
End Sub

You still may have problems opening the workbook if the path is not
correct, but that is another story.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
S

scain2004

Ok, that worked, except I need the focus to remain on the callin
workbook. How should I do that?


Workbooks("Production Job List - 040516.xls").Activate
 
S

scain2004

Ok, this is what I did and it works great:

cwb = "Workbook 1"
jbl = ThisWorkbook.Name

On Error Resume Next
Set wb = Workbooks(cwb)
On Error GoTo 0
If wb is Nothing Then
Set wb = Workbooks.Open(cwb)
End If

Application.Workbooks(jbl).Activate



Thanks!
 
Top