locked
Shell Query RRS feed

  • Question

  • I use two versions of Excel at work. Excel 2010 is installed locally, and Excel 2003 is install on a server.

    From a local Excel file, I need to open an instance of the server's Excel 2003 and control it as I would normally control Excel via VBA.

    Below, after the third line executes, the server's Excel opens, but the forth line fails with a 438 error.

    I'm fairly new to Shell objects through VBA, but I feel like the solution is very simple.

    Any help would be appreciated.

    Thanks

        Dim xlapp As Object
        Set xlapp = CreateObject("Shell.application")
        xlapp.ShellExecute sPathToServer & "\Microsoft Office\OFFICE11\excel.exe"
    
        xlapp.ActiveSheet.Range("A1").Value = "Test"

    Tuesday, May 8, 2012 11:31 PM

All replies

  • I can't test this because I don't have a setup to emulate your setup. However, xlApp is set to the Shell.Application; not Excel application.

    Try the following (Untested)

        Dim ShellApp As Object
        Dim xlApp As Object
        Dim wb As Workbook

        Set ShellApp = CreateObject("Shell.application")

        Set xlApp = ShellApp.ShellExecute(sPathToServer & "\Microsoft Office\OFFICE11\excel.exe")

        Set wb = Workbooks.Open("MyWorkBook.xlsx")     'May not be needed if referencing Default "Book1" with open

        'Set wb = ActiveWorkbook    'Alternative if not using previous line

        wb.ActiveSheet.Range("A1").Value = "Test"

    Added with Edit: You may not need the xlApp like the following:

        Dim sPathToServer As String
        Dim ShellApp As Object
        Dim wb As Workbook
       
        Set ShellApp = CreateObject("Shell.application")
       
        ShellApp.ShellExecute (sPathToServer & "\Microsoft Office\OFFICE11\excel.exe")
       
        Set wb = Workbooks.Open("MyWorkBook.xlsx")   'May not be needed if using Default "Book1" with open
       
        'Set wb = ActiveWorkbook 'Alternatove if not using previous line
       
        wb.ActiveSheet.Range("A1").Value = "Test"


    Regards, OssieMac


    • Edited by OssieMac Wednesday, May 9, 2012 1:41 AM
    Wednesday, May 9, 2012 1:34 AM
  • I suspect (again without testing that the code is:

    Dim ShellApp As Object
    Dim xlApp As Object
    Dim wb As Workbook

        Set ShellApp = CreateObject("Shell.application")
        Set xlApp = ShellApp.ShellExecute(sPathToServer & "\Microsoft Office\OFFICE11\excel.exe")

        Set wb = xlApp.Workbooks.Open("MyWorkBook.xlsx")     'May not be needed if referencing Default "Book1" with open
        wb.ActiveSheet.Range("A1").Value = "Test"


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 9, 2012 2:06 AM
  • Thank you both for your reply. Unfortuately, neither worked. I probably should have mentioned that the version of excel I need to control is in a citrix environment. Excel will open in the citrix window, but I get an error when trying to set wb as a workbook. It seems the code doesn't recognise that xlApp is indeed an Excel application and therefore doesn't have access to its object library.

    Any other suggestions?

    Thanks

    Wednesday, May 9, 2012 6:26 PM