Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2002 Shell to Windows 7

    I have a process in Access 2002 that creates a text file from a database query and then opens MS Word using the Shell command to find MS Word:

    strLetter = "c:\program files\microsoft office\Office10\WINWORD.EXE " & LetterDir & "VisitorLetter-CS-Local.doc"

    RetVal = Shell(strLetter, 1)


    However, I now have one computer that uses Window7 and it has the WINWORD.EXE in the program files (x86) folder so I need to have this in the code for that one computer.

    strLetter = "c:\program files (x86)\microsoft office\Office10\WINWORD.EXE " & LetterDir & "VisitorLetter-CS-Local.doc"
    RetVal = Shell(strLetter, 1)


    Is there a way for Access to discover what operating system the computer is using so I can set up a procedure for the computer to use the proper Shell command? Or perhaps there's a better way of invoking MS Word.

    Thanks for the help,



  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    jstegman,

    You can check for the Environment Variable ProgramFiles(x86) It it returns a value you're running Win 7/ Vista 64 Bit OS. If it comes back blank you've got XP or eariler.
    Code:
    ?environ("ProgramFiles(x86)")
    C:\Program Files (x86)
    ?environ("Test")
    There is a more stringent way to check the OS version but it entails calls to OS functions. I'll hunt that up and edit the post to include.

    See this link: VBA OS Version

    Here's the actual code I use, place it all in a single module in VBA:
    Code:
    Type OSVERSIONINFO   '  148 Bytes
            dwOSVersionInfoSize As Long
            dwMajorVersion As Long
            dwMinorVersion As Long
            dwBuildNumber As Long
            dwPlatformId As Long
            szCSDVersion As String * 128
    End Type
    
    '                     +-----------------------------+             +----------+
    '---------------------|Windows Function Declarations|-------------| 08/11/05 |
    '                     +-----------------------------+             +----------+
    Declare Function GetVersionEx& Lib "kernel32" _
    Alias "GetVersionExA" (lpVersionInformation As OSVERSIONINFO)
    
    
    '                        +----------------------+                +----------+
    '------------------------| zFindWindowsVersion()|----------------| 03/24/10 |
    '                        +----------------------+                +----------+
    ' Returns a string indicating the Windows Version
    
    Function zFindWindowsVersion() As String
    
       Dim tOSVer As OSVERSIONINFO
       Dim bNT    As Boolean    '*** Is this a version of NT? ***
       Dim zOsVer As String
       
       bNT = IIf(InStr(Application.OperatingSystem, "NT") = 0, False, True)
       
       tOSVer.dwOSVersionInfoSize = 148
       GetVersionEx& tOSVer
       
       With tOSVer
       
           If bNT Then
             Select Case .dwMajorVersion
                 Case 4: zOsVer = "Windows NT4"
                 Case 5: zOsVer = _
                      IIf(.dwMinorVersion = 1, "Windows XP", "Windows 2000")
                 Case 6: zOsVer = _
                      IIf(.dwMinorVersion = 1, "Windows 7", "Vista")
             End Select
             
           Else
           
             Select Case .dwMajorVersion
                 Case 4:
                     Select Case .dwMinorVersion
                           Case 0: zOsVer = "Windows 95"
                           Case 10: zOsVer = "Windows 98"
                           Case 90: zOsVer = "Windows ME"
                     End Select
                 Case 5: zOsVer = _
                      IIf(.dwMinorVersion = 1, "Windows XP", "Windows 2000")
             End Select
             
           End If
           
       End With      'tOSVer
       
       zFindWindowsVersion = zOsVer
       
    End Function             'zFindWindowsVersion
    Last edited by RetiredGeek; 2011-07-26 at 10:07.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The other option would be, of course, to use Office Automation. Should work with any Office version, regardless of the used installation folder.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts

    Using Office Automation

    Code:
     Set objWord = New Word.Application
        
     objWord.Documents.Open (LetterDir & "VisitorLetter-CS-Local.doc")
     objWord.Visible = True
    All you need to do besides this is to add a reference (Tools -> Add Reference menu, while viewing your code) to the Microsoft Word Object Library. The library will have a version number, the value of which I don't have present for that Office version. It will show in the available references list, though.

Posting Permissions

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