VBA to Open Another Instant of Excel and Run Macro

[Ceeyee]

Hi,

Is it possible in VBA to open another Excel file in another Excel instant (process) and more importantly, call a macro built in it?

The workbook.open method opens the file in the same Excel instant, which is not what we want.

Thanks.

 

[Ceeyee]

Thank you very much.

 

[cwild]

I am very close to getting this to work but I'm not quite there. I'm simply trying to open an excel file and run a specific macro. So far I have:

PathAndFilename = "C:\Users\Administrator\Desktop\One.xlsm"
Macroname = "MyMacro"
Shell ("C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe " & PathAndFilename & Macroname), 3

I believe it may simply be an issue of syntax. On the other hand is this even possible?

 

[GTO]

...I'm simply trying to open an excel file and run a specific macro. So far I have:...

Greetings cwild,

What application is calling Shell? For instance, if you are running this from Excel, why not simply use Workbooks.Open?

Mark

 

[cwild]

Hi Mark,

I am running this from Excel. I can not do it in any of the simple ways because I need the code to continue to run and launch other instances with other files and other macros without waiting. For example below is one way that works except that it must wait until the first macro is finished before moving on:


AppPathAndFileName = "C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe " & "C:\MyFile1.xlsm"
Shell (AppPathAndFileName), 3
Set Otherinstance = GetObject("C:\MyFile1.xlsm")
Otherinstance.Application.Run "MyMacro1"

AppPathAndFileName = "C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe " & "C:\MyFile2.xlsm"
Shell (AppPathAndFileName), 3
Set Otherinstance = GetObject("C:\MyFile2.xlsm")
Otherinstance.Application.Run "MyMacro2"


I'm also aware that the workbook_open event would work but that is also not an option for me.

Thanks for helping out.

 

[GTO]

As far as I know, Shell will get the workbook opened after starting a new instance of the application, based on the command line string we provide. But I do not believe Shell can get a macro to run as well.

Anyways, lightly tested...

In the called workbook, in a Standard Module named Module1:

Option Explicit
  
Public Sub Test1()
  MsgBox "Hi there!"
End Sub

And in a Junk Copy of your workbook, try:

Option Explicit
  
Private Declare Function FindWindowEx Lib "User32" _
                  Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
                                         ByVal lpsz1 As String, ByVal lpsz2 As String _
                                         ) As Long
  
Private Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Long, ByRef lpiid As GUID) As Long
  
Private Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hWnd As Long, _
                                                                  ByVal dwId As Long, _
                                                                  ByRef riid As GUID, _
                                                                  ByRef ppvObject As Object _
                                                                  ) As Long
  
Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type
  
Private Const RETURN_OK As Long = &H0
Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0
   
Sub example2()
Dim objExcelApp As Object
Dim WB As Workbook
Dim hWndMAIN As Long
  
Dim dblID As Double
Dim sFullName As String
Dim sExeFullName As String
  
  'Full path to .exe and FullName to file.
  sExeFullName = "C:\Microsoft Office 2010\Office14\EXCEL.EXE"
  sFullName = "C:\Users\stumpm\Desktop\vbax 04-03-2016\Book3.xls"
  
  dblID = Shell("""" & sExeFullName & """ """ & sFullName & """", vbMaximizedFocus)
  
  Do
    DoEvents
  Loop While dblID = 0
  
  AppActivate dblID, False
  
  'Return a handle to an available instance of Excel (which as we are running in,
  'cannot fail.
  hWndMAIN = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
  
  Do While hWndMAIN > 0
    
    If GetReferenceToXLApp(hWndMAIN, objExcelApp) Then
      
      'See if the workbook exists in this instance (i.e. - see if we found the
      'instance that Shell started
      On Error Resume Next
      Set WB = objExcelApp.Workbooks("Book3.xls")
      On Error GoTo 0
      
      'If we fail, there must be severak instances running
      If Not WB Is Nothing Then
        WB.Application.Run "'" & WB.Name & "'!Module1.Test1"
        WB.Close False
        Set WB = Nothing
        objExcelApp.Quit
        Set objExcelApp = Nothing
        Exit Sub
      End If
      
    End If
    
    'Find Next (notice that we pass our current handle for the second parameter
    hWndMAIN = FindWindowEx(0&, hWndMAIN, "XLMAIN", vbNullString)
  Loop
  
End Sub

Hope that helps,

Mark

 

[GTO]

Sorry, I missed copying the function:

 'FROM: http://www.ozgrid.com/forum/showthread.php?t=182853
 '// Returns a reference to a specific instance of Excel.
 '// The Instance is defined by the Handle (hWndXL) passed
 '// by the calling procedure
Function GetReferenceToXLApp(hWndXL As Long, oXLApp As Object) As Boolean
     
    Dim hWinDesk As Long
    Dim hWin7 As Long
     
    Dim obj As Object
    Dim iID As GUID
     
     '// Rather than explaining, go read
     '// http://msdn.microsoft.com/en-us/library/windows/desktop/ms687262(v=vs.85).aspx
    Call IIDFromString(StrPtr(IID_IDispatch), iID)
     
     '// We have the XL App (Class name XLMAIN)
     '// This window has a child called 'XLDESK' (which I presume to mean 'XL desktop')
     '// XLDesk is the container for all XL child windows....
    hWinDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
     
     '// EXCEL7 is the class name for a Workbook window (and probably others, as well)
     '// This is used to check there is actually a workbook open in this instance.
    hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)
     
     '// Deep API... read up on it if interested.
     '// http://msdn.microsoft.com/en-us/library/windows/desktop/dd317978(v=vs.85).aspx
    If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
        Set oXLApp = obj.Application
        GetReferenceToXLApp = True
    End If
     
End Function

 

[cwild]

I guess you are saying that you don't think it can be done, however this is an attempt that 'might' work. I will take a look. Thanks.

 

[WolfLarsen85]

Hello,
I am trying to create a form in excel and I am dropping values from the form into an excel cell. The problem I am having is that I can't get the values that drop into excel to format as numbers. Here is the code I have thus far. I set "txtBody" as an integer, but when I fill out the txtBody text box it still formats as general. Please help and let me know if anything else is needed.

Private Sub cmdEnter_Click()
Dim ws As Worksheet
Set ws = Worksheets("Dungeonslayers")
' Check user input

' Enter Data into Cell
Dim txtBody As Integer
ws.Cells(7, 3).Value = txtPlayer
ws.Cells(9, 3).Value = CmboRace
ws.Cells(11, 3).Value = txtAbilities
ws.Cells(8, 4).Value = cboLevel
ws.Cells(7, 7).Value = txtCharacter
ws.Cells(11, 7).Value = cboClass
ws.Cells(8, 5).Value = txtPP
ws.Cells(8, 6).Value = txtTP
ws.Cells(11, 5).Value = txtExperience
ws.Cells(11, 11).Value = cboHero
ws.Cells(13, 7).Value = txtMind
ws.Cells(15, 7).Value = txtIntellect
ws.Cells(17, 7).Value = txtAura
ws.Cells(13, 3).Value = txtBody
ws.Cells(15, 3).Value = txtStrength
ws.Cells(17, 3).Value = txtConstitution
ws.Cells(10, 9).Value = txtExperience
ws.Cells(13, 5).Value = txtMobility
ws.Cells(15, 5).Value = txtAgility
ws.Cells(17, 5).Value = txtDexterity
End Sub

Private Sub cmdExit_Click()
End
End Sub

 

[Jaafar Tribak]

Hi Mark,

I am running this from Excel. I can not do it in any of the simple ways because I need the code to continue to run and launch other instances with other files and other macros without waiting. For example below is one way that works except that it must wait until the first macro is finished before moving on:


AppPathAndFileName = "C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe " & "C:\MyFile1.xlsm"
Shell (AppPathAndFileName), 3
Set Otherinstance = GetObject("C:\MyFile1.xlsm")
Otherinstance.Application.Run "MyMacro1"

AppPathAndFileName = "C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe " & "C:\MyFile2.xlsm"
Shell (AppPathAndFileName), 3
Set Otherinstance = GetObject("C:\MyFile2.xlsm")
Otherinstance.Application.Run "MyMacro2"


I'm also aware that the workbook_open event would work but that is also not an option for me.

Thanks for helping out.

The ideal way would be to call the Shell function from another thread .... I have some code that does that but it is not stable

Alternatively, you could build a small vbs file on the fly from your vba code @ runtime, start the vbs file, make the vbs shell the workbooks and run their respective macros out of process ... This should allow all your other remaining code to continue running simultaniously without hanging/waiting

Do you have any restrictions on running script ? if not then I can post some code for you

 

[cwild]

That would be great, please post. Thank you.