In VBA, fileC.xlsm opens fileX.xlsm, but when I ask fileX.xlsm to objectivise fileC, I am warned: Warning: "File is locked"

In xlControl.xlsm, I used vba to open excel file xlThinkX.xlsm in a separate xlApp:
In excel 2016:

Sub getXlFileObjs()
        Dim xlApp As Object
        Dim wbX As Excel.Workbook
        Dim str As String    'gives full pathfor xlThinkX.xlsm  
     
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        'open workbook X  
        Set wbX = xlApp.Workbooks.Open(Filename:=str)  
End Sub

I need Workbook X to objectivise xlcontrol.xlsm (as Public wb_xlControlFile As Object),
so Workbook X can later write to xlcontrol.sheets, and do other interactions there.
The code below in a standard module gives an error at marker: **** (below) and this message:

'File in use   File locked for editing by another user. U have 3 options:
1) View read-only copy. 2) Save and edit copy of the file. 3) receive notification when server file is available (which it was sometime later..)

The defective code:

Option Explicit
Public wb_xlControlFile As Object

Sub getXlControlFileObj()
        Dim str As String     'gives full pathfor xlControl.xlsm  
   
        SetUp.Select
        str = Range("B2").Value & Range("B1").Value
        Set wb_xlControlFile = GetObject(str) '**** defective code
End Sub

I would like File xlThinkX to objective xlControl via xlThinkX.Workbook_open event (ie straight away).
Thanks
Kelvin
Kelvin4Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
You should be able to get a handle to the Control workbook using it's Name, something like:

Set wb_xlControlFile = Application.Workbooks("xlControl.xlsm")

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin4Author Commented:
Thanks, Bill
The 'File in Use' Msg did not appear, but the code:
Set wb_xlControlFile = Application.Workbooks("xlControl.xlsm") resulted in the msg:

'runtime error 9; Subscript out of range'

This code hi-lit in yellow with this tooltip:
Application.Workbooks("xlControl.xlsm") = <Subscript out of range>

When I entered ?Application.Workbooks.Count in the immediate window, the value returned = 1


When I entered ?Application.Workbooks.Item(1).Name, in the immediate window, the value returned = xlThinkConcepts.xlsm

I need to objectivise the other open xl file: xlControl.xlsm

thanks
Kelvin
0
Bill PrewIT / Software Engineering ConsultantCommented:
That sounds like you only had the one workbook open (think).  I thought think got opened from control, is that not true?  Doesn't seem to be the case based on what you described here, but did sound like it in the original question.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.