Open or switch to a file
 

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

Suggest you open a new post:

How to Close a Toolbar from VBA


--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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


"Leanne M (Aussie)" wrote:

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
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200782


"Leanne M (Aussie)" wrote:

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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

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
'<<==========



---
Regards.
Norman


"Leanne M (Aussie)" wrote in
message ...
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

--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

Open or switch to a file
 

Hi Leanne,

Importantly, I omitted the switching line!

Replace:

End If

End Sub

with:

Else
WB.Activate
End If

End Sub


---
Regards.
Norman

Open or switch to a file
 

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
'<<==========


---
Regards.
Norman

Open or switch to a file
 

Thanks Norman, it is always best to have alternatives.
--
Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)


"Norman Jones" wrote:

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
'<<==========


---
Regards.
Norman