Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a reference to a workbook which is already open
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a workbook to open on a cell reference every time | Excel Discussion (Misc queries) | |||
Activating an Open Workbook without using the whole filename as a reference | Excel Programming | |||
Circular reference in an open workbook | Excel Programming | |||
Create and open a workbook in VB.net ? | Excel Discussion (Misc queries) | |||
Open an existing workbook and reference it | Excel Programming |