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").Text &
"Data File NEW.xls"
Application.VBE.ActiveVBProject.References.AddFromFile 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

Why not just open it readonly?

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

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



(E-Mail Removed) 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").Text &
> "Data File NEW.xls"
> Application.VBE.ActiveVBProject.References.AddFromFile 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

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.

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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" <TungChe...@Invalid.com> 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
>
> <a.years...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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 -

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.

(E-Mail Removed) 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" <TungChe...@Invalid.com> 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
> >
> > <a.years...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> >
> >
> > > 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

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.AddFromFile 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 <peter...@verizonXSPAM.net> 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.
>
>
>
>
>
> a.years...@gmail.com 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" <TungChe...@Invalid.com> 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
>
> > > <a.years...@gmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > 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 -

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?

(E-Mail Removed) 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.AddFromFile 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 <peter...@verizonXSPAM.net> 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.
> >
> >
> >
> >
> >
> > a.years...@gmail.com 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" <TungChe...@Invalid.com> 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
> >
> > > > <a.years...@gmail.com> wrote in message
> >
> > > >news:(E-Mail Removed)...
> >
> > > > > 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

yea i do.

thanks for your help

On Apr 20, 9:59 am, Dave Peterson <peter...@verizonXSPAM.net> 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?
>
>
>
>
>
> a.years...@gmail.com 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.AddFromFile 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 <peter...@verizonXSPAM.net> 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.
>
> > > a.years...@gmail.com 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" <TungChe...@Invalid.com> 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
>
> > > > > <a.years...@gmail.com> wrote in message
>
> > > > >news:(E-Mail Removed)...
>
> > > > > > 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 -

Good!

(E-Mail Removed) wrote:
>
> yea i do.
>
> thanks for your help
>
> On Apr 20, 9:59 am, Dave Peterson <peter...@verizonXSPAM.net> 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?
> >
> >
> >
> >
> >
> > a.years...@gmail.com 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.AddFromFile 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 <peter...@verizonXSPAM.net> 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.
> >
> > > > a.years...@gmail.com 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" <TungChe...@Invalid.com> 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
> >
> > > > > > <a.years...@gmail.com> wrote in message
> >
> > > > > >news:(E-Mail Removed)...
> >
> > > > > > > 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