Help opening a folder and making it the active window

[furstukin]

I am trying to make a simple macro where that will open a folder where you can then search for a customer's order.

I have figured out how to open the folder, but it opens it in teh background and I know the person that will use this spreadsheet will be confused by that. SO is there a way to make an open folder the active window?

Something similar to Windows("Book1").Activate?

Here is what I have and as always any help is much appreciated.

Sub OpenFolderRequest()
MsgBox ("Please enter the name of the customer in the search field in the upper right")
Shell ("G:\Windows\explorer.exe G:\JGM (MBA)\Customer Orders")
End Sub

 

[shg]

Have you looked at Application.GetOpenFilename?

 

[excelgirl94110]

You could try replacing the shell command line with

intShell = Shell("G:\Windows\explorer.exe G:\JGM (MBA)\Customer Orders", vbNormalFocus)

 

[furstukin]

Have you looked at Application.GetOpenFilename?

I did but it was the wierdest thing. It opened the folder and made it the active window but I was not able to open any spreadsheets from it. I would double click a saved order and nothing right click hit open nothing.

 

[furstukin]

You could try replacing the shell command line with

intShell = Shell("G:\Windows\explorer.exe G:\JGM (MBA)\Customer Orders", vbNormalFocus)

This worked perfectly. Thank you very much.

 

[shg]

GetOpenFilename doesn't open anything; it returns a filename string so that you can open the file in code, or do anything else with it you want.

This is covered in Help.

 

[Kenneth Hobson]

You get the filename. Then use WorkBooks(thefilename).Open.

Sub Test()
    Dim filespec As Variant, i As Integer
    'filespec = "c:\myfiles\test.xls"
    On Error GoTo EndNow
    filespec = Application.GetOpenFileName(FileFilter:="Kens Files (*.xls), Ken*.xls", Title:="Get File", MultiSelect:=True)
      'MsgBox GetFileName(filespec)
    For i = 1 To UBound(filespec)
      MsgBox filespec(i), , GetFileName(CStr(filespec(i)))
    Next i
EndNow:
End Sub

' MsgBox GetFileName(filespec(0))
Function GetFileName(filespec As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetFileName = fso.GetFileName(filespec)
End Function

 

[furstukin]

You get the filename. Then use WorkBooks(thefilename).Open.

Sub Test()
    Dim filespec As Variant, i As Integer
    'filespec = "c:\myfiles\test.xls"
    On Error GoTo EndNow
    filespec = Application.GetOpenFileName(FileFilter:="Kens Files (*.xls), Ken*.xls", Title:="Get File", MultiSelect:=True)
      'MsgBox GetFileName(filespec)
    For i = 1 To UBound(filespec)
      MsgBox filespec(i), , GetFileName(CStr(filespec(i)))
    Next i
EndNow:
End Sub
 
' MsgBox GetFileName(filespec(0))
Function GetFileName(filespec As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetFileName = fso.GetFileName(filespec)
End Function

Yeah I was not looking to open a file from the macro. I want the user to just have a short cut to the existing orders folder from within the spreadsheet they take orders on. This way they can easily search for orders from the customer they might be helping.

On another note quoting "shg"

GetOpenFilename doesn't open anything; it returns a filename string so that you can open the file in code, or do anything else with it you want.

If I enter the lines of code

ChDrive "G:\"
ChDir "G:\JGM (MBA)\Customer Orders\"
Application.GetOpenFilename

and it does open that folder but as I said it will not allow me to open a spreadsheet within the folder.

Thanks again to everyone for the help.

 

[shg]

    Dim sFile As String
    
    sFile = Application.GetOpenFilename
    If sFile = "False" Then Exit Sub
    
    Workbooks.Open sFile