VBA reference worksheet on another instance or Excel

Question

So I am trying to open an "Update".xlsb in a new instance of Excel and then identify a specific "Report" worksheet to make some changes and then open VB to remove a module and insert a new module.  Normally this would not be so difficult because I would just open it in the same instance of Excel but the "Update".xlsb has the VB protected, I am the creator and owner of the workbook, and need to open it in a new instance so that other coding in the "Master".xlsb can unprotect VB to do the module and update work.

So with all that being said I am having trouble getting the "Master".xlsb instance of Excel set a specific worksheet in the "Update".xlsb.  It also need to run a worksheet unprotect macro to changes can be made and then remove and insert a module.  I am not going to provide the VB unlock coding here but could use help with this coding.

Dim MasterWB As Workbook

Set MasterWB = ActiveWorkbook

Dim xlApp As Object

Dim UpdateWB As Workbook

Set xlApp = CreateObject("Excel.Application")

'Coding to open the file in the new instance and unlock VB.

'Identify the worksheets that need to be updated

        Dim wsUpdateNeeded As Worksheet

        xlApp.Select

        Set wsUpdateNeeded = xlApp.UpdateWB.Worksheets(Sheet301)

    'Run the Unprotect Macro for the UpdateWB

        UpdateWB.Activate

        xlApp.UpdateWB.Run Name & "Unprotect_sheets"

    'Update the cell information on the Report tab

        wsUpdateNeeded.Select

        wsUpdateNeeded.Range("M12").Select

        ActiveCell.FormulaR1C1 = "Wait List"

'Remove the module and insert the new module

'close the xlApp

Answer 1

Hi,

We highly suggest that you post your query on Microsoft TechNet that focuses more on advanced technical discussions about Microsoft products that may help you get accurate methods regarding Excel coding concerns.

Regards.