excel - GetOpenFileName opens in background

Question

I have a macro that prompts the user to select a comma separated values file, however, whenever the macro runs, it opens the window behind all the other open windows. The macro is never called from excel, but only by other scripts. I've tried messing with Application.DisplayAlerts and Application.ActiveWindow but to no avail. Any suggestions are greatly appreciated.

Function folderselection()
    Dim fnameandpath As Variant
    Dim path As String

    Dim objshell As Object
    Set objshell = CreateObject("wscript.shell")
    objshell.AppActivate "excel"

    Application.DisplayAlerts = False
    path = ActiveWorkbook.path
    ChDrive (path)
    ChDir (path)

    Application.ActiveWindow.Visible = True
    Application.WindowState = xlMaximized

    fnameandpath = Application.GetOpenFilename(FileFilter:=("BOM CSV/RPT (*.CSV;*.RPT), *.CSV; *.RPT"), Title:="Select The BOM File To Copy Values From")
    Application.WindowState = xlMinimized

    If fnameandpath = False Then Exit Function
    Workbooks.Open Filename:=fnameandpath, ReadOnly:=True

    Application.DisplayAlerts = True
    folderselection = CStr(fnameandpath)
    ActiveWorkbook.Close
End Function

Answer 1

Looking at some sections of your code:

Function folderselection()
Dim fnameandpath As Variant
...
fnameandpath = Application.GetOpenFilename(...
...
folderselection = CStr(fnameandpath)

These lines are redundant. This accomplishes the same thing with half the code (and is therefore simpler):

Function folderSelection() as String
...
folderSelection = Application.GetOpenFilename(...

---

Dim path As String
path = ActiveWorkbook.path
ChDrive (path)
ChDir (path)

These lines accomplish nothing. The GetOpenFilename dialog defaults to the same folder as ActiveWorkbook.Path.

---

Dim objshell As Object
Set objshell = CreateObject("wscript.shell")
objshell.AppActivate "excel"

These lines don't do anything either. I think you might be trying to activate the existing Excel window? If so, you need to read the documentation for these commands.

I guarantee you don't have a Title Bar called excel. You might have one called "Book1.xlsm - Excel", but that's irrelevant because you don't need to activate the current window unless you were using another application in the 0.01 seconds since you [I assume] manually executed this procedure.

Furthermore, objects need to be handled certain ways, such as freeing up the memory when you're finished with them (ie, Set ... Nothing; see "crashes" below) otherwise, some processes will just remain in memory, taking up space, until you reboot.

It's important to understand that some commands should be at least partially understood before arbitrarily using them, since you could have unexpected results. In this case the Windows Script Host (wscript), as well as calling "outside" command-line programs (shell.exe) can/will impact other applications and/or cause crashes.

---

Application.DisplayAlerts = False
Application.DisplayAlerts = True

This isn't accomplishing anything related to what you're trying to do (and certain setting should be used sparingly or not at all -- like disabling warnings or security alerts in code that isn't functioning properly to begin with. Just leave those lines out.

---

Application.ActiveWindow.Visible = True

The Active Window is, by definition, Visible already. This line does nothing.

Application.WindowState = xlMaximized 
Application.WindowState = xlMinimized 

Seriously? Obviously these "cancel each other out", not to mention that the "last" one leaves the window minimized. Isn't "not being able to see the window" your main issue?

fNameAndPath = Application.GetOpenFilename(FileFilter:=("BOM CSV/RPT (*.CSV;*.RPT), *.CSV; *.RPT"), Title:="Select The BOM File To Copy Values From")

Ironically, the command that you figured is the problem, is actually the only line that was functioning properly (regardless of minor syntax and organization issues). Little things like spacing and using "exact documented syntax" canm end up having an an impact on the success of your code, and are especially important while still in the troubleshooting stage.

Matching the command's documentation, plus changing the destination as mentioned above:

folderSelection = Application.GetOpenFilename("Comma Separated Files (*.csv),*.csv,Report Files (*.rpt),*.rpt.", 1, "Select the Source BOM File")

---

If fNameAndPath = False Then Exit Function

Nothing wrong with that line! Personally, I would use:

 If Not fNameAndPath Then Exit Function

...but the result is the same.

---

 Workbooks.Open Filename:=fNameAndPath, ReadOnly:=True

Following the documentation, a better way to phrase that line would be:

Workbooks.Open Workbooks.Open fNameAndPath, , True, 2

The 2 specifies comma delimiting. Since you specified that the file is comma-separated, I will assume that the other option you specified (an ".RPT" file) is also a text-based, comma-separated file.

That line probably would have functioned okay as it was, which is good since it's a key part of your subroutine.

Except that, 0.01 seconds later, the very last command closes the file that you just opened:

ActiveWorkbook.Close

---

With VBA and/or Excel there are often (usually?) multiple ways to accomplish the same task, adding to flexibility and ease-of-use that have made Excel common-place on almost "every desk and in every home." [Anyone else catch that reference?!]

Unfortunately the flip side (very common around here) is users over-complicating tasks unnecessarily; even [unknowingly] attempting to build functionality from scratch - that's already built-in to Excel and the rest of the Office Suite.