wimgielis.com

Open your Excel files quickly

• Apr. 4, 2021

	Example files with this article:
	Userform for data input

Get quick access to your Excel files

If you work in a similar way than me, you would typically need a bunch of files almost every day, or multiple times a day. You could store them in 1 folder, or have 1 folder containing shortcuts to a list of files scattered around in your File explorer folders.

With a macro we can automate the access to and opening of, several Excel files at once:

The idea is that we give a letter or number to each file to which we need quick access. Be it files for the company you work for, files for personal usage, etc. Then, in a small userform, you can indicate the ones you want to open. Let me show the code and explain down below, it won't be very hard to understand even though it might look lengthy: The VBA code is not difficult at all.

Dim sInput                 As String

Sub Open_My_Files()
    Set fso = CreateObject("scripting.filesystemobject")

    'the root folder
    p = Environ("HO") & "\Wim\"

    'ask for input
    sInput = ""
    Input_To_Open_Files

    sInput = Replace(LCase(sInput), " ", "")

    For i = 1 To Len(sInput)

        sCharacter = Mid(sInput, i, 1)
        Select Case 

        Case "a"                                 'the company KPI dashboard - with password
            c0 = p & "Finance\KPI_dashboard.xlsm"
            If fso.FileExists(c0) Then Workbooks.Open c0, , , , sCharacter

        Case "1", "&", "c"                       'the finance department calendar
            c0 = p & "Finance\Department calendar.xlsx"
            If fso.FileExists(c0) Then Workbooks.Open c0

        Case "2", "é", "d"                       'the contact details of your coworkers
            c0 = p & "Admin\Contact.xlsx"

        Case "3", """", "p"                      'the product catalog of your company
            c0 = p & "Admin\Product catalog.xlsx"
            If fso.FileExists(c0) Then Workbooks.Open c0

        Case "4", "'", "g"                       'the grocery shopping list
            c0 = p & "Personal\Shopping.xlsx"
            If fso.FileExists(c0) Then Workbooks.Open c0

        End Select

    Next

End Sub

Private Sub Input_To_Open_Files()
    With New ufPassword
        .Caption = "Enter password or file numbers"
        .Show
        If Not .UserCancel Then sInput = .Password
        Unload ufPassword
    End With

End Sub

We require a userform to do the input of what files we want to open. For instance, it could be a24. This means, following our logic, that you want to open 3 files:

• a stands for the password for the first file (the company KPI dashboard)
• 2 stands for the number of the file you want to open next (the contact details of your coworkers)
• 4 stands for the number of the file you want to open lastly (the grocery shopping list)

My userform can be downloaded from the top of the page. The other code will need a copy/paste. Typically, I do this in my Personal.xlsb personal macro file: then I always have immediate access to the macro and can execute it anytime Excel is opened.

Let's now look at the code for a while. First, I define the path p which is a folder called "Wim" within a certain root folder. I could have used "D:\OneDrive" for example, but as I moved computers more than once in the past, I opt for a twist here. I use a Windows environmental variable called "HO" (short for HOme), which refers to "D:\OneDrive". Therefore, a lot of my (custom) applications and tools use that name "HO" rather than the path "D:\OneDrive". It might seem redundant for now, but you'll be happy when you need to move all your files and application to a different PC where you don't have D and/or OneDrive is not used or stored somewhere else. Only changing the contents of the "HO" environmental variable suffices !

Next up, call the userform and allow the user to do the input. That's pretty straightforward I would say. The input is captured in the module-level variable called "sInput". We take away spaces and use lower case letters.

Then we will loop over all characters that the user entered in the string "sInput". That's our For... Next loop. Each character will be evaluated to match a couple of possibilities:

• if it's an "a", the user wants to open the main file for the company. "a" is the Excel password for that file too.
• if it's a "1" or an ampersand or a "c", the user wants to open the first file.
• if it's a "2" or an "é" or a "d", the user wants to open the second file.
• and so on.

Now what are those other strange characters ? I do this to allow for different keyboards. The number 1 on my (Azerty) keyboard will match the character & on a Querty keyboard. The character "c" stands for "calendar" ;-) Likewise the other combinations that are allowed for. Please feel free to adapt the logic to what you prefer.

For any matching character we will define the path and the name of the file to be opened. There is a final check to see if the file exists and if it does, Excel VBA will attempt to open it. Any character that the user enters and does not match a condition, is skipped. No error messages are shown but you can do that.

That's all there is ! Now in a whim you can open 12 files without all the hassle of finding your files :-) Good luck !

• « Previous Excel article
• Next Excel article »