Help Hyperlink to Folder With VBA Code

[mkoziol2]

I have done numerous searches and attempted different code myself I can't seem to get it to work though and I am hoping someone can help me out.

What I want is that when the code is executed it creates a folder for every value in column A and doesn't make repeats. Then Once this folder is created I want the value it came from to become a hyperlink to the new folder.

I have the code working just fine to create the folder and not make repeats. I can't figure out how to make it hyperlink itself though. Any help would be greatly appreciated.

 

[p45cal]

Please post your code - we'll tweak!

 

[mkoziol2]

Here is the code I have that works it creates my folders just as I want. What I cant figure out is how to automatically make it a hyperlink to that new folder.


-- removed inline image ---

 

[mkoziol2]

Private Sub CommandButton1_Click()
Dim cnf
Dim dir As String
Dim fnsh As Long
Dim i As Long
Set cnf = CreateObject("Scripting.FileSystemObject")
fnsh = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 9 To fnsh
dir = "P:\Drawings\ECRN's\2012 ECRN\" & Range("A" & i).Value
If Not cnf.FolderExists(dir) Then
cnf.CreateFolder (dir)
End If
Next
Application.ScreenUpdating = True
End Sub

 

[p45cal]

Private Sub CommandButton1_Click()
Dim cnf
Dim dir As String
Dim fnsh As Long
Dim i As Long
Set cnf = CreateObject("Scripting.FileSystemObject")
fnsh = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 9 To fnsh
  dir = "C:\Drawings\ECRN's\2012 ECRN\" & Range("A" & i).Value
  If Not cnf.FolderExists(dir) Then
    cnf.CreateFolder (dir)
  End If
  'Range("A" & i).Hyperlinks.Delete
  [COLOR=Blue]ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), Address:=dir[/COLOR]
Next
[COLOR=Blue]Set cnf = Nothing[/COLOR]
Application.ScreenUpdating = True
End Sub

 

[mkoziol2]

That's 99.9 percent what I need One thing isn't working though I have the code:

"For i = 9 To fnsh"

This was because everything in the spreadsheet above row 9 is titles and categories etc... so I don't want those to create folders.

When I run your code it does exactly what I wanted it to, except for some reason it negates my f"For" statement and it creates folders and links for everything in the title area. Any idea why? Thanks again this is already a large improvement.

 

[p45cal]

Could you paste your exact code here? And what kind of module is the code in (worksheet, workbook, userform, standard)? In the same book as the target sheet?

 

[mkoziol2]

I'll try to be as specific as possible. The code I originally posted is what I was using it created the folders as I wanted, I then changed it to your modified version of my code and it did what I wanted except it made folders and links for the boxes in my title area that didn't get created before the change. So as of now I tried what you posted which was my modified code with the hyperlink capabilities below

Private Sub CommandButton1_Click()
Dim cnf
Dim dir As String
Dim fnsh As Long
Dim i As Long
Set cnf = CreateObject("Scripting.FileSystemObject")
fnsh = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 9 To fnsh
dir = "P:\Drawings\ECRN's\2012 ECRN\" & Range("A" & i).Value
If Not cnf.FolderExists(dir) Then
cnf.CreateFolder (dir)
End If
'Range("A" & i).Hyperlinks.Delete
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), Address:=dir
Next
Set cnf = Nothing
Application.ScreenUpdating = True
End Sub

How this is meant to work is when a change is made to a document this sheet will keep record of them. The first column is a "date code"-esque command, I want a folder to be created of that code and also create a link to it so any information to do with the change can be found there. It will all be in a single sheet and basically after the user enters the code they would press a button and execute the code above.

As I mentioned it does work but for some reason it ignores my starting at row 9 command once the addition of the hyperlink was added. Thank you again.

 

[mkoziol2]

Well don't know what changed but it seems to be working this morning after i messed with it not sure what changed but it works. Thank you for your help.

 

[p45cal]

Well don't know what changed but it seems to be working this morning after i messed with it not sure what changed but it works. Thank you for your help.

Good, because I couldn't replicate this behaviour!