+ Reply to Thread
Results 1 to 4 of 4

Macro to find filepath and open file

Hybrid View

  1. #1
    Registered User
    Join Date
    07-09-2014
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    10

    Macro to find filepath and open file

    I'm working on making a macro that will allow users to input an invoice number in a cell (A1). The cell next to it (B1) contains a formula that returns the vendor that invoice came from.

    With that, the purpose of the macro is to take the vendor name, use it as a string to locate the folder name, and then use the invoice number as a string to locate the specific file in the folder, and open it. The way our file system is set up is that we have an "invoice folder" which contains vendor folders. Each vendor folder contains invoices named by job date, vendor, and invoice number, so the file names look like this: 2014.04 - Vendor Name - 1234.pdf

    Sub Invoice_Search()
    
    'Searches N Drive for specified invoice and opens .pdf
    
        Dim y As Variant, x As Variant, invnumlen As Integer, vendnamelen As Integer
        Dim myShell As Object
        Dim foldernamefull As String, filenamefull As String
        Dim objFS As Variant
        Dim objFolder As Variant
        Dim objFile As Variant
        Const Filepath = "N:\Storage\Projects\Billing\Billing Final\Invoices\"
        
        
        y = Sheets("Sheet4").Range("A1")
        x = Sheets("Sheet4").Range("B1")
        invnumlen = Len(Sheets("Sheet4").Range("A1"))
        vendnamelen = Len(Sheets("Sheet4").Range("B1"))
        
        Set objFS = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFS.getfolder(Filepath)
        For Each objFile In objFolder.Files
        If Left(objFile.Name, vendnamelen) = x Then
        foldernamefull = objFile.Name
        Set myShell = CreateObject("WScript.Shell")
        myShell.Run ("Filepath" & "foldernamefull")
        Exit For
    
        Else
        
        End If
        
        Next
    
    
    End Sub
    My code isn't complete - I don't have the second half to locate the specific file written, but this should be enough for you to see the logic of what I've coded so far. Since I'm fairly new to VBA, it's cobbled together from different "how-to"s i've found online, so parts may be redundant.

    From what I have so far, i expected that the macro, when run, would open up the vendor folder. It's not working so far.

    Can anyone help me out?
    Last edited by JBeaucaire; 07-14-2014 at 11:20 AM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

  2. #2
    Registered User
    Join Date
    07-09-2014
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    10

    Re: Macro to find filepath and open file

    I've simplified my approach and my code, and have what I think should be a simple problem to solve, however I don't know enough about VBA syntax to figure out how to word the code I'm looking for.

    Sub open_Pdf()
    Dim FileName As String
    
    FileName = Sheets("Sheet4").Range("B7").Value
    ActiveWorkbook.FollowHyperlink ((Sheets("Sheet4").Range("B4").Value) & FileName)
    
    End Sub
    What I have in Cell B7 is a partial file name. Each file in the directory that I'm searching is formatted the same, with an invoice number as the last part of the name.
    What I have in Cell B4 is the file path of the folder containing the file referred to in B7.
    What I'm trying to do is have excel open up the file based on the partial file name given in B7. I know that * can be used as a wildcard but I haven't figured out how to use it to return the file I'm looking for.

    My question is, where in the code above should I place the * in order for the macro to open the first file in the folder (referred to in B4) using the last part of the file name (which is contained in B7)?


    I hope my question is clear and if not, I can provide more detail or parts of the workbook.

    Any help will be much appreciated.

  3. #3
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Macro to find filepath and open file

    Hi..

    Maybe something like:
    Private Sub CommandButton1_Click()
        Dim x
        x = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & Cells(4, 2) & "*" & Cells(7, 2) & "*.pdf"" /s/b").stdout.readall, vbCrLf)
        If UBound(x) > -1 Then
            Workbooks.Open (x(0))
        Else: MsgBox "File Not Found!"
        End If
    End Sub

  4. #4
    Registered User
    Join Date
    07-09-2014
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    10

    Re: Macro to find filepath and open file

    apo,

    With a slight modification, that worked! I really appreciate the feedback, and will mark the thread as solved. Thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to Open Group of Workbooks Based on Name and Filepath
    By Jason Carlos in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-20-2014, 02:41 PM
  2. [SOLVED] Macro to select and open filepath stored at worksheet
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-01-2014, 04:32 AM
  3. [SOLVED] Trying to get the filepath on "Open", but get filepath of a temporary file instead
    By ianpwilliams in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-22-2013, 12:27 AM
  4. Macro to open word doc, then open another file in word and then excute a find replace
    By Uber-Smee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2010, 09:43 AM

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