Create a reference to a workbook which is already open

[a.yearsley]

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]

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)

 

[a.yearsley]

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.

 

[NickHK]

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.yearsley]

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.

 

[Dave Peterson]

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.yearsley]

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

 

[Dave Peterson]

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.yearsley]

yea i do.

thanks for your help

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?










--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

Good!

yea i do.

thanks for your help