Opening an Excel workbook using VBA

[pincivma]

Hi There

I hope that someone out there can help me solve this VBA problem. I only show the code lines that are giving me undue stress.

Here is what I want the code to do.

If the workbook is already open I want to ignore the line

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

and go the the line

Windows("Salem's Point 10 1.xls").Activate

If however, the workbook is not open then I want to go to the line

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

and ignore the line

Windows("Salem's Point 10 1.xls").Activate


Thanks,

Mario

 

[seenfresh]

Try this site:

http://www.ozgrid.com/VBA/IsWorkbookOpen.htm#ONE

Covers many workbook situations.

 

[pincivma]

I think that you have misunderstood my question. I don't want a pop up scren to tell me if a workbook is opened or not. I have a workbook named master.xls. This workbook has many rows. I have created a macro that copies one line from the master.xls workbook to a workbook named Salem's Point 10 1.xls. The code to do this is as follows:

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

and the macro then continues to copy the line from the master.xls workbook to th workbook named Salem's Point 10 1.xls

Now when I execute the macro once again the above code does not work. I have to use the following code.

Windows("Salem's Point 10 1.xls").Activate

How do I combine the above two lines of code to do the following:

If the workbook Salem's Point 10 1.xls is not opened then execute the line

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

And if the worbook is opened then execute the line

Windows("Salem's Point 10 1.xls").Activate

I hope that you now understand my question better.

Thanks again.

Mario

 

[seenfresh]

If the workbook Salem's Point 10 1.xls is not opened then execute the line

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

And if the worbook is opened then execute the line

Windows("Salem's Point 10 1.xls").Activate

I hope that you now understand my question better.

Thanks again.

Mario

Hi Mario, the link gives you an example of how to determine if your workbook is open or not...disregard the msgbox and input your desired code in place of it. For example:

Dim wBook As Workbook



	On Error Resume Next


	Set wBook = Workbooks("Salem's Point 10 1.xls")



		If wBook Is Nothing Then 'Not open

			Workbooks.Open Filename:="C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

			On Error GoTo 0

		Else 'It is open
Windows("Salem's Point 10 1.xls").Activate

			On Error GoTo 0

		End If

I'm pretty sure you do not need the "Windows("Salem's Point 10 1.xls").Activate" line instead you would just carryout the rest of your code and when you want to reference the "Salem's..." workbook just use wBook, for example:

wBook.Range("A1").copy

However, not sure if Windows.Activate is needed when two separate instances of Excel are open.

 

[John Davis]

Hi There

I hope that someone out there can help me solve this VBA problem. I only show the code lines that are giving me undue stress.

Here is what I want the code to do.

If the workbook is already open I want to ignore the line

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

and go the the line

Windows("Salem's Point 10 1.xls").Activate

If however, the workbook is not open then I want to go to the line

Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"

and ignore the line

Windows("Salem's Point 10 1.xls").Activate


Thanks,

Mario

Does this help in any way?

Sub pinciva()

If IsFileOpen(FileName:="C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls") = True Then GoTo 0
If IsFileOpen(FileName:="C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls") = False Then
Workbooks.Open FileName:= _
"C:\Documents and Settings\Mario\My Documents\Luvian Homes\Salem's Point 10 1.xls"
End If

0
Windows("Salem's Point 10 1.xls").Activate

End Sub
Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0

    Select Case iErr
        Case 0:    IsFileOpen = False
        Case 70:   IsFileOpen = True
        Case Else: Error iErr
    End Select
    
End Function

 

[pincivma]

SOLVED - Opening an Excel workbook using VBA

Thanks for all your help John and seenfresh. The code both of you gave me works fine.

Mario

 

[John Davis]

Re: SOLVED - Opening an Excel workbook using VBA

Thanks for all your help John and seenfresh. The code both of you gave me works fine.

Mario

Your welcome, Mario. And thanks for the feedback.