I know this topic is 6 months old but I thought I'd add something....
The following registry key can be accessed from and written to by VBA code.
HKEY_CURRENT_USER\Software\VB and VBA Program Settings\
I have many ME macros which open an Excel Workbook and run a specific Excel macro utilising this registry key to pass data.
To run a specific Excel Macro when a workbook is opened:-
1. Add the following code to the Excel Workbook
Private Sub Workbook_Open()
Dim sMacroName As String
'Run the Macro in Registry RunExcelMacro, Clear Registry value
sMacroName = GetSetting("MyProject", "RunExcelMacro")
SaveSetting "MyProject", "RunExcelMacro", ""
If sMacroName <> "" Then
Application.Run sMacroName
End If
End Sub
2. Write a registry string containing the name of the Excel Macro, E.G. HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyProject\RunExcelMacro
3. Launch your Excel workbook and the Excel Macro will run.
Hope this helps someone, Pete