Open or switch to a file

[Leanne M (Aussie)]

Hi,

I have code to open a file in a command button which works fine.

What I would like to do is allow this button to also switch to this file if
it is already open.

I do not know how to write this so detailed help would be apprecitated.

This is the code I have for opening the file.

Private Sub CommandButton3_Click()
ChDir "C:\My Documents\Pest Control Management System"
Workbooks.Open Filename:= _
"C:\My Documents\Pest Control Management System\Pest Control
Reporting Tool.xls"
End Sub

 

[Gary''s Student]

Hi Leanne:

Hyperlink will open the file if it is not open and activate the file if it
is open:

Sub button_it()
s = "file:///c:\Documents and Settings\Owner\Desktop\sample.xls"
ActiveWorkbook.FollowHyperlink Address:=s
End Sub

 

[Leanne M (Aussie)]

Hi Gary,

Thanks that does just what I want.

I can live with it but every time I use it the Web toolbar is activated.
Any ideas why?

Leanne

 

[Gary''s Student]

I am not sure why this happens. Sometimes it is convenient to use the large
hyperlink arrows to move back and forth between workbooks ??

 

[Leanne M (Aussie)]

Oh well, I am sure some of the users will look at it and think something
major has happened but they will just have to live with it.

Thanks again

 

[Gary''s Student]

Suggest you open a new post:

How to Close a Toolbar from VBA

 

[Gary''s Student]

Actually, the Recorder worked this time:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/30/2008 by James Ravenswood
'

'
Application.CommandBars("Web").Visible = False
End Sub

 

[Leanne M (Aussie)]

I was just about to say that I recorded a macro and gave that a try but I see
you have done the exact same.

Thanks

 

[Norman Jones]

Hi Leanne,

As an alternative to the suggestion of
Gary's Student, try:

'==========>>
Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim myFolder As String
Dim myDrive As String
Dim sStr As String
Const sFolder As String = _
"C:\My Documents\Pest Control Management System"
Const sFile As String = "Pest Control Reporting Tool.xls"

On Error Resume Next
Set WB = Workbooks(sFile)
On Error GoTo 0

If WB Is Nothing Then
myFolder = CurDir
myDrive = CurDir
Set WB = Workbooks.Open(Filename:=sFolder _
& Application.PathSeparator _
& sFile)
ChDrive sFolder
ChDir sFolder
End If

End Sub
'<<==========

 

[Norman Jones]

Hi Leanne,

Importantly, I omitted the switching line!

Replace:

End If

End Sub

with:

Else
WB.Activate
End If

End Sub

 

[Norman Jones]

Hi Leanne,

Correcting the path and folder assignments,
the code should have read:

'==========>>
Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim myFolder As String
Dim myDrive As String
Dim sStr As String
Const sFolder As String = _
"C:\My Documents\Pest Control Management System"
Const sFile As String = "Pest Control Reporting Tool.xls"

On Error Resume Next
Set WB = Workbooks(sFile)
On Error GoTo 0

If WB Is Nothing Then
myFolder = CurDir
myDrive = CurDir

ChDrive sFolder
ChDir sFolder
Set WB = Workbooks.Open( _
Filename:=sFolder _
& Application.PathSeparator _
& sFile)
ChDrive myFolder
ChDir myFolder
Else
WB.Activate
End If
End Sub
'<<==========

 

[Leanne M (Aussie)]

Thanks Norman, it is always best to have alternatives.