0

I have a question on how I should approach this code that I need. I have been searching for the past 3 hours and couldnt find a way to mesh the codes together to perform what I desired. Here is the outline of what I would like to do:

The User runs the macro and the InputBox pops up which tells them to Input a Folder Name. From there, I want the Application.GetOpenFile (or which ever application or dir function) to initiate a search in a specified directory for a folder and NOT a file (as there may be several files in the folder with similar names), but take the user to the folder and then the User selects the right file to open. Then once the file is selected, it is opened in the excel worksheet.

Phiter
  • 14,570
  • 14
  • 50
  • 84
Eddie
  • 17
  • 5

1 Answers1

0

Try using shell. Here's a function from user bburns.km on this question Capture output value from a shell command in VBA?

This combined with a shell command to only list folders and directories should get you where you need.

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string'

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command'
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object'
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function
Community
  • 1
  • 1
TenderShortGoldenRetriever
  • 339
  • 2
  • 11
  • Thanks @DevinTrowbridge for your suggestion, I will give this a shot, This may seem like a crazy question, but how do I use shells in VBA, I never done something like this before? – Eddie May 27 '16 at 18:30
  • Check out the documentation on Microsoft's website (https://msdn.microsoft.com/en-us/library/xe736fyk%28v=vs.90%29.aspx). You can just call Shell "YourCommandAsString" – TenderShortGoldenRetriever May 27 '16 at 18:51
  • Thanks @Devin, I will take a look at that – Eddie May 31 '16 at 13:13