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?
 
Bill PrewCommented:
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
 
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 PrewCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.