+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
Coding Thread, Find a file in excel VBA in Coding and Web Development; Is there a way to find and return the full path to a file on the PC? For example I ...
  1. #1

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46

    Find a file in excel VBA

    Is there a way to find and return the full path to a file on the PC?
    For example I want to find Word on my PC and have it return c:\program files\microsoft office\Office14\Winword.exe or at least the directory path.

    I can probably fudge it using some long winded IF statements and checking to see if the file exists but I was wondering for s slightly cleaner way.

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,815
    Thank Post
    364
    Thanked 541 Times in 505 Posts
    Rep Power
    184
    Quote Originally Posted by Stuart_C View Post
    Is there a way to find and return the full path to a file on the PC?
    For example I want to find Word on my PC and have it return c:\program files\microsoft office\Office14\Winword.exe or at least the directory path.

    I can probably fudge it using some long winded IF statements and checking to see if the file exists but I was wondering for s slightly cleaner way.
    Can you just clarify, you want to find a file from a fileopen dialog box within excel ?

    Steve

  3. #3

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,405
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    Are you trying to do some sort of audit to see which computers have Word (or other applications) installed?

    If so there are plenty of free solutions out there that can do this sort of thing...

  4. #4

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    The full scenario is this,
    I am dropping a load of data from our MIS into Excel, manipulating it a little in excel then opening a mail merge document which will produce the notices I need. For this to happen automatically I need open the Word document which I am doing using Shell command to execute Word with the appropiate file name. However to do that I need to know the exact location of Winword.exe

    At the minute I could just use nested if statements to find the version of word on my PC or the secretary who is most likely going to be using this (OfficeVal is then passed to the shell command)

    Code:
        Dim OfficeVal As String
        Dim Office2010 As String
        Dim Office2007 As String
        
        Office2010 = "c:\Program Files\Microsoft Office\Office14\EXCEL.EXE"
        Office2007 = "c:\Program Files\Microsoft Office\Office12\EXCEL.EXE"
        
        If Dir(Office2010) <> "" Then
            OfficeVal = Office2010
        Else
            If Dir(Office2007) <> "" Then
                OfficeVal = Office2007
            Else
                MsgBox "No Idea about Office"
            End If
        End If
    The problem here is that it won't work on 64bit machine as Office will be in the Program files (x86) dir and it doesn't future proof the file (for when Office 2013 is released ).
    So yes I could put in more IF's but I was looking for a cleaner way.

    In theory this would be by the macro searching the c:\drive for winword.exe and returning the full path to the exe

    Hope that makse sense.

  5. #5

    Join Date
    Apr 2007
    Location
    Birmingham
    Posts
    146
    Thank Post
    7
    Thanked 45 Times in 39 Posts
    Rep Power
    23
    Do you need to use Shell?

    Else this should work

    Code:
        Set wordapp = CreateObject("word.Application")
    
        wordapp.documents.Open "C:\...........\worddocument.doc"
    
        wordapp.Visible = True

  6. Thanks to andy_b from:

    Stuart_C (6th May 2011)

  7. #6

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,815
    Thank Post
    364
    Thanked 541 Times in 505 Posts
    Rep Power
    184
    Quote Originally Posted by Stuart_C View Post
    In theory this would be by the macro searching the c:\drive for winword.exe and returning the full path to the exe
    A slighty longer, but "cleaner" way to do it would be cheating, and looking up the program designed to open the .doc (aka what's set to system default for the .doc? .docx etc)

    (Note, Original code stolen and modified a bit)

    Code:
    Private Declare Function FindExecutable Lib "shell32" _
       Alias "FindExecutableA" _
      (ByVal lpFile As String, _
       ByVal lpDirectory As String, _
       ByVal sResult As String) As Long
    
    Private Const MAX_PATH As Long = 260
    Private Const ERROR_FILE_NO_ASSOCIATION As Long = 31
    Private Const ERROR_FILE_NOT_FOUND As Long = 2
    Private Const ERROR_PATH_NOT_FOUND As Long = 3
    Private Const ERROR_FILE_SUCCESS As Long = 32
    Private Const ERROR_BAD_FORMAT As Long = 11
    
    
    Private Sub Command1_Click()
    
       Dim success As Long
       Dim pos As Long
       Dim sResult As String
       Dim msg As String
       
       sResult = Space$(MAX_PATH)
    
       Dim sFile$
       sFile = "\\sdrew\Documents\Stuff\test.docx"
       success = FindExecutable(sFile, "", sResult)
    
          
       Select Case success
          Case ERROR_FILE_NO_ASSOCIATION: msg = "no association"
          Case ERROR_FILE_NOT_FOUND: msg = "file not found"
          Case ERROR_PATH_NOT_FOUND: msg = "path not found"
          Case ERROR_BAD_FORMAT:     msg = "bad format"
          
          Case Is >= ERROR_FILE_SUCCESS:
             
             pos = InStr(sResult, Chr$(0))
             
             If pos Then
                msg = Left$(sResult, pos - 1)
             End If
             
       End Select
       
       MsgBox msg
       
    End Sub
    Then just remove the msgbox msg and run "msg" as a shell instead etc etc.

    Any use? or prefer simpler way?

    Will return any exe that is set to default to open the file type (just change filename to whatever you're using)

    Steve

  8. #7

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,405
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    I do this the way that @andy_b suggested...

  9. #8

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    10,044
    Thank Post
    3,579
    Thanked 1,119 Times in 1,024 Posts
    Rep Power
    377
    Quote Originally Posted by Stuart_C View Post
    The full scenario is this,
    I am dropping a load of data from our MIS into Excel, manipulating it a little in excel then opening a mail merge document which will produce the notices I need. For this to happen automatically I need open the Word document which I am doing using Shell command to execute Word with the appropiate file name. However to do that I need to know the exact location of Winword.exe

    At the minute I could just use nested if statements to find the version of word on my PC or the secretary who is most likely going to be using this (OfficeVal is then passed to the shell command)

    Code:
        Dim OfficeVal As String
        Dim Office2010 As String
        Dim Office2007 As String
        
        Office2010 = "c:\Program Files\Microsoft Office\Office14\EXCEL.EXE"
        Office2007 = "c:\Program Files\Microsoft Office\Office12\EXCEL.EXE"
        
        If Dir(Office2010) <> "" Then
            OfficeVal = Office2010
        Else
            If Dir(Office2007) <> "" Then
                OfficeVal = Office2007
            Else
                MsgBox "No Idea about Office"
            End If
        End If
    The problem here is that it won't work on 64bit machine as Office will be in the Program files (x86) dir and it doesn't future proof the file (for when Office 2013 is released ).
    So yes I could put in more IF's but I was looking for a cleaner way.

    In theory this would be by the macro searching the c:\drive for winword.exe and returning the full path to the exe

    Hope that makse sense.
    you can enumerate the reg keys for the diff versions of office which will return office 10, office 11 etc which translates to different office versions and go from there as you can enumerate through the relevant sub keys of the registry key

  10. #9

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    Quote Originally Posted by andy_b View Post
    Do you need to use Shell?

    Else this should work

    Code:
        Set wordapp = CreateObject("word.Application")
    
        wordapp.documents.Open "C:\...........\worddocument.doc"
    
        wordapp.Visible = True
    I thouhgt I did
    That seems to work. Cheers.

  11. #10

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    Hmm.. Slight update here. It appears that I can't use Andy_b's way.

    The document I am trying to open is a mail merge template. When I open the document using that method it looses the merge source information. If I open it using the shell command I am prompted to merge the data in as normal.

    Why would that be?

  12. #11

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,815
    Thank Post
    364
    Thanked 541 Times in 505 Posts
    Rep Power
    184
    Quote Originally Posted by Stuart_C View Post
    Hmm.. Slight update here. It appears that I can't use Andy_b's way.

    The document I am trying to open is a mail merge template. When I open the document using that method it looses the merge source information. If I open it using the shell command I am prompted to merge the data in as normal.

    Why would that be?
    Have you actually opened the mailmerge source in the code?

    Steve

  13. #12

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    Yes and no. The code in Excel opens the source file, copys the data in then saves and closes the mail merge source then opens the mail merge file. There is then a macro in the mail merge file that runs on AutoOpen which simply runs a mail merge. This fails if I run it using the documents.open method but works if I run the Shell method of opening the document.

    At the risk of exposing my ignorance here is all my code

    In Excel
    Code:
        Dim DETFileName As String
        Dim SelRange As String
        Dim FindEOR As String
        
        Range("A2:F150").Select
        Selection.Copy
        Workbooks.Open Filename:="T:\Training_Materials\SIMSRep\HMC_Totals.xlsx"
        Range("A2:F150").Select
        Application.DisplayAlerts = False
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        Calculate
        
        FindEOR = Application.Match("<EOR>", Range("I2:I40"), 0)
        SelRange = "A1:I" & FindEOR
        
        Sheets("Sheet2").Select
        Cells.Select
        Selection.Delete Shift:=xlUp
        Sheets("Sheet1").Select
        Range(SelRange).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Sheets("Sheet1").Select
        ActiveWorkbook.Save
        ActiveWindow.Close
        DETFileName = """T:\Training_Materials\SIMSRep\HMC List.docm"""
        Shell "c:\Program Files\Microsoft Office\Office14\WINWORD.EXE " & DETFileName, vbNormalFocus
    Rem    Set wordapp = CreateObject("word.Application")
    Rem    wordapp.documents.Open "T:\Training_Materials\SIMSRep\HMC List.docm"
    Rem    wordapp.Visible = True
    In Word
    Code:
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
        Windows("HMC List").Activate
        ActiveWindow.Close savechanges:=wdDoNotSaveChanges
    I know I've not named my worksheets in excel. Just to clarify here is my process.

    Data exported from SIMS. data copied into mail merge source document. Data copied from sheet1 (with formulas) to sheet2 (no formula's data only - so mail merge works). Save and close. Open word document. Word documents opens, perfomrs merge and closes source template. Thus the end user is just left with report then need.

  14. #13

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,405
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169

    This what I use to open a template and do the merge...

    Code:
    Private Sub loadTemplate(myType As String)
    
        Dim oApp As New Word.Application
        Dim oDoc As Word.Document
        Dim fPath As String
        
        Call export_XLS
        
        fPath = "H:\OfficeShare\labels\"
    
        Select Case myType
    
            Case "Book"
                Set oDoc = oApp.Documents.Open(fPath & "Labels_Book.doc")
       
        End Select
                
        oDoc.MailMerge.OpenDataSource Name:= _
            "\\webserver\inetpub\db\labeldata.xls", ConfirmConversions:=False, _
            ReadOnly:=True, LinkToSource:=True, AddToRecentFiles:=False, _
            PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
            WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
            Connection:= _
            "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=\\webserver\inetpub\db\labeldata.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
            , SQLStatement:="SELECT * FROM `labelsource`", SQLStatement1:="", SubType _
            :=wdMergeSubTypeAccess
    
        oDoc.MailMerge.Destination = wdSendToNewDocument
        oDoc.MailMerge.Execute
        
        oDoc.Close SaveChanges:=wdDoNotSaveChanges
        oApp.Application.Visible = True

  15. #14

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    *Puts thinking cap on*
    OK. That sort of makes sense. I'll have a bit of a play and see how I get on, cheers.

    I hate programming, it's a shame you can do so many cool things with it

  16. #15

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    Cheers for that. Think I've got it working now. Possibly... well until it goes wrong anyway.



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Excel VBA Issues suppress propmt
    By Stuart_C in forum Coding
    Replies: 2
    Last Post: 4th May 2011, 10:26 AM
  2. Excel VBA Issues
    By dataoverride in forum Coding
    Replies: 5
    Last Post: 23rd September 2009, 03:16 PM
  3. Using VBA to find a date from a table.
    By garethedmondson in forum Office Software
    Replies: 0
    Last Post: 30th January 2009, 03:19 PM
  4. Excel VBA problems
    By cursong in forum Coding
    Replies: 1
    Last Post: 13th October 2008, 12:28 PM
  5. Excel VBA Loop Question
    By vunsev in forum Coding
    Replies: 5
    Last Post: 11th April 2008, 09:08 AM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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