Excel 2010+ Read Custom Properties from another instance of Excel

[JasonEnsor]

Hi Guys,

I am creating a worksheet based for to help alleviate some issues with a current process and have hit a slight snag and thought someone might be able to spot my error.

I have a button on my custom ribbon in excel which allows the user to Approve the form, this then opens a new instance of excel in the background, opens a workbook and should set a custom property to "Macro". The newly opened sheet should then unlock, update a value then close, my form then uses that newly updated value. The reason I use a custom property is so that if the CRX_Num file is opened by a user it is fully locked and they can not amend the value in the document as the property is set to "hand". So my code finds the property, updates its value and then amends the data.

I write a lot of my code in modular segments that I then amend and build upon so that I can test as I try out new concepts so the code below may be slightly tweaked to make it reusable in the future, as it stands I have the following

Public Sub ApproveForm()

'1. Get CRX Number
 Dim newXL As New Excel.Application
 Dim CrxNumWB As Workbook
 Dim CRXNumSheet As Worksheet
 Dim propertyName
 Dim CRX_Num As String
 Dim docProp As DocumentProperty
 
 propertyName = "openedBy"
 
 Set CrxNumWB = newXL.Workbooks.Open("C:\Users\J\Desktop\CRX_Num.xlsm")

    For Each docProp In CrxNumWB.CustomDocumentProperties
        If (propertyName = docProp.Name) Then
            docProp.Value = "Macro"
            Set CRXNumSheet = CrxNumWB.Sheets("CRX_Num")
            CRX_Num = CRXNumSheet.Range("B2")
            Exit For
        End If
    Next

' Test if CRX_Num populates
 MsgBox CRX_Num

End Sub

When I run this I get a 'run time error 13' Type Mismatch on the line

 For Each docProp In CrxNumWB.CustomDocumentProperties

if I remove the for loop for the custom property and just read in the value from the other spreadsheet it works, so I know that it is finding the file ok.

When I use the following function to check for the current excel workbooks properties it works ok

Function PropertyExists(propertyName As String) As Boolean
    Dim docProp As DocumentProperty
    For Each docProp In ActiveWorkbook.CustomDocumentProperties
        If (propertyName = docProp.Name) Then
            PropertyExists = True
            Exit For
        End If
    Next
End Function

Any ideas on what I could be missing? Many Thanks in advance

Regards

J.

 

[SkipVought]

Hi,

Why do you need a NEW Instance of Excel? This Instance can't manipulate That Instance!

 

[JasonEnsor]

Hi Skip,

The main reason I was using a new instance was so that I could close the new instance completely once I was finished with it, otherwise I am left with an empty grey excel application window open as well as my form. I am trying to make it so that my user doesn't see anything that is not necessary on screen. Im not really needed to manipulate the new instance of excel (apart from updating the Custom Property). The on open method of the workbook opened in the new instance holds all of the code to do the updating.

I'm guessing it's back to the drawing board for me then..

Thanks Skip.



Regards

J.