+ Reply to Thread
Results 1 to 6 of 6

Open File problem using Shell command

  1. #1
    Registered User
    Join Date
    07-13-2010
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    5

    Open File problem using Shell command

    Hi,
    I succesfully use a VBA-program for starting up other applications,. i.e. other spreadsheets.
    Example:

    Sub ShellProg ()
    Dim ResStart As Variant
    Dim strPath, strFile, strShell, strName As String

    strPath = Worksheets("Standards").Cells(2, 2).Value
    strFile = Worksheets("Standards").Cells(9, 2).Value
    strShell = strPath + " " + strFile
    strName = "UserSheet1.xls"

    ResStart = Shell(strShell, 1)

    strPath = Worksheets("Standards").Cells(2, 2).Value
    strFile = Worksheets("Standards").Cells(10, 2).Value
    strShell = strPath + " " + strFile
    strName = "UserSheet2.xls"

    ResStart = Shell(strShell, 1)

    etc.
    This works beautifully.
    Now, I want to check whether a certain user file (UserSheet2.xls) has already been openend and I include before the Shell command:
    strName = "UserSheet2"
    If bFileOpen((strName)) Then
    Etc.

    Function bFileOpen(wbName As String) As Boolean
    Dim wb As Workbook
    'check each open workbook's name, in lower case, and set the function
    'to true if a match is found. If not match the function defaults to False
    For Each wb In Workbooks
    If LCase(wb.Name) = LCase(wbName) Then
    bFileOpen = True
    Exit Function
    End If
    Next
    End Function
    This check is unsuccessfull, even if UserSheet2.xls is definitively open. However, should I have started up manually my second worksheet UserSheet2.xls, not via ShellProg, then the same test is successful.

    So, there seems to be a difference between spreadsheets having been started up through a shell and the same spreadsheets having been started up manually by double clicking on the file name.
    Also, after my spreadsheets have been started up through the ProgShell program, I could delete ProgShell itself, but the same difference exists: the spreadsheets having been started by ProgShell act differently from the same spreadsheets having been started by hand, even though the starter program ProgShell has already gone.

    Can somebody explain this, please?

    Thanks in advance,

    Roel
    Last edited by roelvdh; 07-13-2010 at 04:02 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Open File problem using Shell command

    Hello roelvdh,

    The problem is when you "Shell" a workbook, you are opening the workbook in a separate instance of Excel. The Workbooks collection only holds the workbooks that have been opened in the same instance of Excel.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-13-2010
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Open File problem using Shell command

    Quote Originally Posted by Leith Ross View Post
    Hello roelvdh,

    The problem is when you "Shell" a workbook, you are opening the workbook in a separate instance of Excel. The Workbooks collection only holds the workbooks that have been opened in the same instance of Excel.
    Thank you, Leith,

    I had already thought something like this but you expressed it very accurately.
    So, if I start up 2 spreadsheets in one shell program ProgShell, I actually start 3 instances of Excel (ProgShell and the two startups), right? And these 3 instances do not know of each other's existence? While, when manually starting up 2 spreadsheets, I only use one instance of Excel and these spreadsheets know about the other one's existence because they are part of the same instance. Is that correct?
    I assume there's not much we can do to resolve the problem, but this is a sure weak point of the Shell command in my view. Glad to understand it better now. I just joined this forum and I'm already inmpressed by the quality here. Thanks a lot.

    Roel

  4. #4
    Registered User
    Join Date
    07-13-2010
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Open File problem using Shell command

    Quote Originally Posted by roelvdh View Post
    Thank you, Leith,

    I had already thought something like this but you expressed it very accurately.
    So, if I start up 2 spreadsheets in one shell program ProgShell, I actually start 3 instances of Excel (ProgShell and the two startups), right? And these 3 instances do not know of each other's existence? While, when manually starting up 2 spreadsheets, I only use one instance of Excel and these spreadsheets know about the other one's existence because they are part of the same instance. Is that correct?
    I assume there's not much we can do to resolve the problem, but this is a sure weak point of the Shell command in my view. Glad to understand it better now. I just joined this forum and I'm already inmpressed by the quality here. Thanks a lot.

    Roel
    Just to take it one step further.
    I had already tried to generate a *.cmd-file from within Excel and then subsequently Shell this *.cmd-file. That didn't work as the Shell command seemingly does not understand *.cmd files. Then I made an outside *.cmd-file, starting up various spreadsheets. These sheets didn't know of the others being open, which is explained by your answer: each line in the *.cmd file is a new instance of Excel, and thus a new, autonomous programming environment.
    So, the challenge remains to make something we can Shell (necessarily an *exe-file, I suppose) and where we can interactively add elements without starting up a new Excel instance. It's certainly above my current skills but it keeps nagging.

    Roel

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Open File problem using Shell command

    Hello Roel,

    If you want to open other workbooks in the same instance of Excel then use the command below:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-13-2010
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Open File problem using Shell command

    Quote Originally Posted by Leith Ross View Post
    Hello Roel,

    If you want to open other workbooks in the same instance of Excel then use the command below:
    Please Login or Register  to view this content.
    Thanks again, Leith. I had intentionally simplified my question untill it apparently lost its meaning. In reality I do not only start Excel files but various programs. Great help, much appreciated Only the questions should improve :-).

    Roel

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1