Create a reference to a workbook which is already open
 

I am looking to create a reference to a workbook which is already
open. I have used the code below but this throws the error '32813':
name conflicts with existing module, project or object library'. The
project that I am trying to reference has a unique name.

Sub Create_Reference()
Dim Path1 As String
Path1 = ThisWorkbook.Worksheets("Timesheet").Range("T2").T ext &
"Data File NEW.xls"
Application.VBE.ActiveVBProject.References.AddFrom File Path1
End Sub



What I am really trying to do is have the workbook that I am
referencing opened as a Read Only copy.


Any help on this would be appreciated

Create a reference to a workbook which is already open
 

Why not just open it readonly?

Dim Wkbk as workbook
dim myFilename as string
myfilename = ThisWorkbook.Worksheets("Timesheet").Range("T2").T ext _
& "Data File NEW.xls"

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )



wrote:

I am looking to create a reference to a workbook which is already
open. I have used the code below but this throws the error '32813':
name conflicts with existing module, project or object library'. The
project that I am trying to reference has a unique name.

Sub Create_Reference()
Dim Path1 As String
Path1 = ThisWorkbook.Worksheets("Timesheet").Range("T2").T ext &
"Data File NEW.xls"
Application.VBE.ActiveVBProject.References.AddFrom File Path1
End Sub

What I am really trying to do is have the workbook that I am
referencing opened as a Read Only copy.

Any help on this would be appreciated

--

Dave Peterson

Create a reference to a workbook which is already open
 

Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.

Create a reference to a workbook which is already open
 

Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message
oups.com...
Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.

Create a reference to a workbook which is already open
 

Unfortunately Dave's solution doesn't do quite what I am wanting ...
perhaps i didnt explain myself properly

Just to clarify what i wish to do is:
a) have wrkbk1 open
b) from wrkbk1 VBA code create a reference to another workbook
'wrkbk2' which must be opened as a Read Only item.
I want to create a refence .. which you could manually
create by
Tools References Browse (from the VB editor)

The way I was going about it was first, open a read only version of
wrkbk2 (as Dave's code does) and then try to create the reference.


A further note: my original code seems to work when i debug it ... it
throws an error but then works. But when it comes to run-time it just
throws the error.



On Apr 19, 2:48 pm, "NickHK" wrote:
Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message

oups.com...



Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.- Hide quoted text -

- Show quoted text -

Create a reference to a workbook which is already open
 

This skinnied down version worked ok for me when I ran it:

Option Explicit
Sub testme()
Dim myFileName As String
Dim wkbk As Workbook
myFileName = "C:\my documents\excel\book1.xls"
Set wkbk = Workbooks.Open(Filename:=myFileName, ReadOnly:=True)
ThisWorkbook.VBProject.References.AddFromFile Filename:=myFileName
End Sub

Book1.xls had a project name of Book1 (changed from VBAProject).
Book1.xls was not marked readonly (in windows), but opened readonly.

And I had Tools|Macro|security|Trusted Publishers tab
Trust access to Visual Basic Project checked

If you can't get it working, you may want to share more information--what is the
error you receive when you run the code.

wrote:

Unfortunately Dave's solution doesn't do quite what I am wanting ...
perhaps i didnt explain myself properly

Just to clarify what i wish to do is:
a) have wrkbk1 open
b) from wrkbk1 VBA code create a reference to another workbook
'wrkbk2' which must be opened as a Read Only item.
I want to create a refence .. which you could manually
create by
Tools References Browse (from the VB editor)

The way I was going about it was first, open a read only version of
wrkbk2 (as Dave's code does) and then try to create the reference.

A further note: my original code seems to work when i debug it ... it
throws an error but then works. But when it comes to run-time it just
throws the error.

On Apr 19, 2:48 pm, "NickHK" wrote:
Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message

oups.com...



Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Create a reference to a workbook which is already open
 

Thanks a lot for your help.

For some reason using
ThisWorkbook.VBProject.References.AddFromFile
Filename:=myFileName
to create the reference doesnt throw any errors in runtime. Wheras my
code which instead used the line
Application.VBE.ActiveVBProject.References.AddFrom File Path1
throws the error
'32813: name conflicts with existing module, project or object
library
even though no other code has changed.


Thanks again



On Apr 20, 1:11 am, Dave Peterson wrote:
This skinnied down version worked ok for me when I ran it:

Option Explicit
Sub testme()
Dim myFileName As String
Dim wkbk As Workbook
myFileName = "C:\my documents\excel\book1.xls"
Set wkbk = Workbooks.Open(Filename:=myFileName, ReadOnly:=True)
ThisWorkbook.VBProject.References.AddFromFile Filename:=myFileName
End Sub

Book1.xls had a project name of Book1 (changed from VBAProject).
Book1.xls was not marked readonly (in windows), but opened readonly.

And I had Tools|Macro|security|Trusted Publishers tab
Trust access to Visual Basic Project checked

If you can't get it working, you may want to share more information--what is the
error you receive when you run the code.





wrote:

Unfortunately Dave's solution doesn't do quite what I am wanting ...
perhaps i didnt explain myself properly

Just to clarify what i wish to do is:
a) have wrkbk1 open
b) from wrkbk1 VBA code create a reference to another workbook
'wrkbk2' which must be opened as a Read Only item.
I want to create a refence .. which you could manually
create by
Tools References Browse (from the VB editor)

The way I was going about it was first, open a read only version of
wrkbk2 (as Dave's code does) and then try to create the reference.

A further note: my original code seems to work when i debug it ... it
throws an error but then works. But when it comes to run-time it just
throws the error.

On Apr 19, 2:48 pm, "NickHK" wrote:
Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message

roups.com...

Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Create a reference to a workbook which is already open
 

I got that error in my simple testing, too. I figured that it was bad test
workbooks (multiple workbooks having the same project name). I didn't change
the code to overcome the error. I just figured I'd use the project that I
wanted.

Does this mean that you have it working?

wrote:

Thanks a lot for your help.

For some reason using
ThisWorkbook.VBProject.References.AddFromFile
Filename:=myFileName
to create the reference doesnt throw any errors in runtime. Wheras my
code which instead used the line
Application.VBE.ActiveVBProject.References.AddFrom File Path1
throws the error
'32813: name conflicts with existing module, project or object
library
even though no other code has changed.

Thanks again

On Apr 20, 1:11 am, Dave Peterson wrote:
This skinnied down version worked ok for me when I ran it:

Option Explicit
Sub testme()
Dim myFileName As String
Dim wkbk As Workbook
myFileName = "C:\my documents\excel\book1.xls"
Set wkbk = Workbooks.Open(Filename:=myFileName, ReadOnly:=True)
ThisWorkbook.VBProject.References.AddFromFile Filename:=myFileName
End Sub

Book1.xls had a project name of Book1 (changed from VBAProject).
Book1.xls was not marked readonly (in windows), but opened readonly.

And I had Tools|Macro|security|Trusted Publishers tab
Trust access to Visual Basic Project checked

If you can't get it working, you may want to share more information--what is the
error you receive when you run the code.





wrote:

Unfortunately Dave's solution doesn't do quite what I am wanting ...
perhaps i didnt explain myself properly

Just to clarify what i wish to do is:
a) have wrkbk1 open
b) from wrkbk1 VBA code create a reference to another workbook
'wrkbk2' which must be opened as a Read Only item.
I want to create a refence .. which you could manually
create by
Tools References Browse (from the VB editor)

The way I was going about it was first, open a read only version of
wrkbk2 (as Dave's code does) and then try to create the reference.

A further note: my original code seems to work when i debug it ... it
throws an error but then works. But when it comes to run-time it just
throws the error.

On Apr 19, 2:48 pm, "NickHK" wrote:
Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message

roups.com...

Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Create a reference to a workbook which is already open
 

yea i do.

thanks for your help

On Apr 20, 9:59 am, Dave Peterson wrote:
I got that error in my simple testing, too. I figured that it was bad test
workbooks (multiple workbooks having the same project name). I didn't change
the code to overcome the error. I just figured I'd use the project that I
wanted.

Does this mean that you have it working?





wrote:

Thanks a lot for your help.

For some reason using
ThisWorkbook.VBProject.References.AddFromFile
Filename:=myFileName
to create the reference doesnt throw any errors in runtime. Wheras my
code which instead used the line
Application.VBE.ActiveVBProject.References.AddFrom File Path1
throws the error
'32813: name conflicts with existing module, project or object
library
even though no other code has changed.

Thanks again

On Apr 20, 1:11 am, Dave Peterson wrote:
This skinnied down version worked ok for me when I ran it:

Option Explicit
Sub testme()
Dim myFileName As String
Dim wkbk As Workbook
myFileName = "C:\my documents\excel\book1.xls"
Set wkbk = Workbooks.Open(Filename:=myFileName, ReadOnly:=True)
ThisWorkbook.VBProject.References.AddFromFile Filename:=myFileName
End Sub

Book1.xls had a project name of Book1 (changed from VBAProject).
Book1.xls was not marked readonly (in windows), but opened readonly.

And I had Tools|Macro|security|Trusted Publishers tab
Trust access to Visual Basic Project checked

If you can't get it working, you may want to share more information--what is the
error you receive when you run the code.

wrote:

Unfortunately Dave's solution doesn't do quite what I am wanting ...
perhaps i didnt explain myself properly

Just to clarify what i wish to do is:
a) have wrkbk1 open
b) from wrkbk1 VBA code create a reference to another workbook
'wrkbk2' which must be opened as a Read Only item.
I want to create a refence .. which you could manually
create by
Tools References Browse (from the VB editor)

The way I was going about it was first, open a read only version of
wrkbk2 (as Dave's code does) and then try to create the reference.

A further note: my original code seems to work when i debug it ... it
throws an error but then works. But when it comes to run-time it just
throws the error.

On Apr 19, 2:48 pm, "NickHK" wrote:
Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message

roups.com...

Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Create a reference to a workbook which is already open
 

Good!

wrote:

yea i do.

thanks for your help

On Apr 20, 9:59 am, Dave Peterson wrote:
I got that error in my simple testing, too. I figured that it was bad test
workbooks (multiple workbooks having the same project name). I didn't change
the code to overcome the error. I just figured I'd use the project that I
wanted.

Does this mean that you have it working?





wrote:

Thanks a lot for your help.

For some reason using
ThisWorkbook.VBProject.References.AddFromFile
Filename:=myFileName
to create the reference doesnt throw any errors in runtime. Wheras my
code which instead used the line
Application.VBE.ActiveVBProject.References.AddFrom File Path1
throws the error
'32813: name conflicts with existing module, project or object
library
even though no other code has changed.

Thanks again

On Apr 20, 1:11 am, Dave Peterson wrote:
This skinnied down version worked ok for me when I ran it:

Option Explicit
Sub testme()
Dim myFileName As String
Dim wkbk As Workbook
myFileName = "C:\my documents\excel\book1.xls"
Set wkbk = Workbooks.Open(Filename:=myFileName, ReadOnly:=True)
ThisWorkbook.VBProject.References.AddFromFile Filename:=myFileName
End Sub

Book1.xls had a project name of Book1 (changed from VBAProject).
Book1.xls was not marked readonly (in windows), but opened readonly.

And I had Tools|Macro|security|Trusted Publishers tab
Trust access to Visual Basic Project checked

If you can't get it working, you may want to share more information--what is the
error you receive when you run the code.

wrote:

Unfortunately Dave's solution doesn't do quite what I am wanting ...
perhaps i didnt explain myself properly

Just to clarify what i wish to do is:
a) have wrkbk1 open
b) from wrkbk1 VBA code create a reference to another workbook
'wrkbk2' which must be opened as a Read Only item.
I want to create a refence .. which you could manually
create by
Tools References Browse (from the VB editor)

The way I was going about it was first, open a read only version of
wrkbk2 (as Dave's code does) and then try to create the reference.

A further note: my original code seems to work when i debug it ... it
throws an error but then works. But when it comes to run-time it just
throws the error.

On Apr 19, 2:48 pm, "NickHK" wrote:
Doesn't Dave's solution work ? You set the reference when you open it
read-only, not before or after.

set wkbk = workbooks.open(filename:=myfilename,readonly:=true )

NickHK

wrote in message

roups.com...

Yea I open the workbook as a Read Only document, then try to set the
reference to it. But because it is already open it throws the error.

If I set the reference to it while it is still closed then it
automatically opens it in read/write format ... but i need it to be in
read only.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson