How to Create folders with hyperlink from Excel Macro

I've previously shared how to automatically create folders from an Excel spreadsheet by using a macro. I recently had a reader request an additional feature: to automatically add a hyperlink from the Excel sheet to the newly created folders. I not only added this ability to my code, I also improved the overall code some as well.

The steps to use this macro to create folders is as follows:

1. User selects range of cells they want to turn into folders
2. Run macro
3. Select location where folders will be created
4. Macro automatically creates folders and hyperlinks selected cell to newly created folder

Here is a screen capture video of the VBA in action:


You can download the exact same spreadsheet template I use in the video here.

Or you can copy and paste the code below into your own Excel file. I use comments to explain what us happening on each line of code.

Sub Create_Folders()

'remember to select the cells you want to turn into folders before running the macro

'Default location where to select folder

Dim OpenAt As String

OpenAt = "My computer:\"

'Dialog box to select folder creation location

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)

On Error Resume Next

BrowseForFolder = ShellApp.Self.Path

'get the range of cells that were selected before the macro was run

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

'---loop through all cells within selected range---

For c = 1 To maxCols

   r = 1

   Do While r <= maxRows

  

       'if the selected cell does not contain nothing, then create a folder

       If Rng(r, c) <> "" Then

           'create hyperlink in Excel file to newly created folder

           Dim cnf

           Set cnf = CreateObject("Scripting.FileSystemObject")

               'If folder already exists in this location, then just create hyperlink in Excel

               If (cnf.FolderExists(BrowseForFolder & "\" & Rng(r, c))) Then

       

               'MsgBox "folder does  already exist"

              ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, c), Address:=BrowseForFolder & "\" & Rng(r, c)

   

               'if folder does not previously exist, then we need to create it and add hyperlink

               Else

               'MsgBox "need to create folder"

               cnf.CreateFolder (BrowseForFolder & "\" & Rng(r, c))

              ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, c), Address:=BrowseForFolder & "\" & Rng(r, c)

               End If

           On Error Resume Next

   

       'if the selected cell contains nothing, then do nothing and go to the next cell

       End If

      

       r = r + 1

       Loop

   Next c

End Sub

Follow my Excel Help playlist for more how to videos in the future.

How To Get The Best Out of The Concatenate Function

The following is a guest post from Puneet at Excel Champs:

Text functions averagely cover  10%-15% of our Excel usage on a day to day basis. Text functions help us to present a numeric value in a simple way. If you check the below example, a single sentence is describing the performance for the September month. But we can't say there is something wrong in presenting results in tabular form. It is just a matter of requirement. Excel has lots of useful functions in this Text Function family (Upper Function, Lower Function, Proper Function etc....).

CONCATENATE Function

A function to combine text, a function to put the text together. Yes, this is the CONCATENATE Function. A Function which exactly do the same for text as SUM Function does with numbers (Giving results in a single number).

How Does It Work?

It takes the text one by one in arguments & convert them into a single string.

Syntax: CONCATENATE(text1, text2, ...text255) 

text1 is the first item to join in concatenation which is also a required argument. 

text2 ... is the additional text which you can add. You can add up to 255 items & up to 8192 characters in a single function(for Excel 2007 & Later). Each entry must be separated by a comma.

If we concatenate two strings like "Hello" & "World", then it would be like =CONCATENATE("Hello ","World"). Now if you notice we had a space between the two strings, which is quite useful while converting them into a single string. The above example is drawn by using absolute input for arguments, you can also use cell reference to add arguments.  =CONCATENATE(A1," ",B1).

How Can It Be Useful?

It can help you at a number of times.

• Writing a conclusion for a tabular data, which will update with the changes in your data. So that, you don't have to write it again & again.
• Converting two or more strings into a single string. So that it can make a meaning to you.

Example

Here we have an example where we are capturing the population of the major cities in the world in a string and function will be.

HURDLES

Yes, we have. But you don't have to worry.

• Using commas to differentiate one string from another is confusing sometimes. If you skip putting a comma between the arguments, it can lead to a different result. =CONCATENATE("Excel""Champs") results in Excel"Champs. You can also put ampersand(&) instead of Comma(,).
• The space between string is also a point to take care. You can enter space within the string "Hello " or by inserting it separately "Hello"," ".
• If you use concatenation to join a numeric value, that value will always show in general format. That means if you have a number with 10 decimals, you will get it in the same decimal format in your string(Total growth in this month is 0.0523652). But this problem is not long lasting, you can kill it with TEXT Function & make your number just like you want.

What else to do?

Sometimes using a function like CONCATENATE is time-consuming, if you have to put it one by one different string to create a new one. You can also use a shorthand trick to get results like CONCATENATE, just ampersand(&) to join the text as do in CONCATENATE. It will give you a simple & a fast hand to join text.

Download a sample file here to see examples:

Sample File

Thanks to Puneet for providing us with this explanation of concatenate.