opening unopened workbbok via code

[freekrill]

Hi,

I posted this question a while ago, but the responses I got wer
unfortunately beyond my comprehension as a self-taught VBA'er!

My problem was that I wanted to check if a file was open and if no
open it before working on it.

I developed the following code which seems to work OK.

On error Goto Line1
Windows("a").Activate
Goto Line2
Line 1:
Workbooks.Open Filename:="C:\A.xls"
Line2:
.....

The problem I have is that if I need to check on more than one workboo
in one sub (eg "B.xls"), I get an error message "subscript out o
range" for the line, Windows....Activate

Any help would be appreciated

Thanks
fre

 

[John Green]

I suggest you set up a separate function procedure to test for a workbook
being open. It is a simple extension of the principals you are already
using.

Sub Brute()
If IsWorkBookOpen("a.xls") = False Then Workbooks.Open "C:\a.xls"
If IsWorkBookOpen("b.xls") = False Then Workbooks.Open "C:\b.xls"
If IsWorkBookOpen("c.xls") = False Then Workbooks.Open "C:\c.xls"
End Sub

Function IsWorkBookOpen(sWB)
On Error GoTo NotOpen
Workbooks(sWB).Activate
IsWorkBookOpen = True
Exit Function
NotOpen:
IsWorkBookOpen = False
End Function

John Green

 

[Soo Cheon Jheong]

Hi,

Option Explicit
Sub TEST()

Const P As String = "C:\"

Dim WBK As Workbook
Dim F(1 To 3) As String
Dim i As Integer
Dim is_open As Boolean

F(1) = "a.xls"
F(2) = "b.xls"
F(3) = "c.xls"

Application.ScreenUpdating = False

For i = 1 To 3
is_open = False
For Each WBK In Workbooks
If UCase(WBK.Name) = UCase(F(i)) Then
is_open = True
End If
Next
If is_open = False Then
If Dir(P & F(i), vbNormal) <> "" Then
Workbooks.Open P & F(i)
Else
MsgBox P & F(i) & " does not exist"
End If
End If
Next

ThisWorkbook.Activate
Application.ScreenUpdating = True

End Sub


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[freekrill]

Thanks guys,

That was exactly what I wanted.

fre