Create folder shortcuts to network directory.

[Newbie212]

Hello, good people of MrExcel

I have project in witch im trying to somehow automate the distribution of network resources across users, by creating a multiple network directory shortcuts for different users or user groups.
I have encountered a few problems along the way, so im kindly asking for help or words of experience and wisdom

First problem. This is the code i use to create the shortcut.

Sub Shortcut_test()

Dim sShortcutLocation As String

nameofshortcut = Range("b2").Value
targetfolderpath = Range("a2").Value
sShortcutLocation = Range("a1").Value & "\" & nameofshortcut & ".lnk"


With CreateObject("WScript.Shell").CreateShortcut(sShortcutLocation)
    .targetpath = targetfolderpath
    .Description = "Shortcut to the file" & targetfolderpath
    .Save
   
End With

End Sub

Which generates a shortcut like this:



The shortcut works to an extend - It creates a shortcut and it opens the directory... but it "thinks" for quite some time before it opens it, and the users have no rights and cant use the files in directory the shortcut leads to.
The problem seem to be quotes around the address target field, if i remove them the shortcut works perfectly. Maybe there is a different way to make network shortcuts, because the quotes in shortcuts that lead to a directory on the local machine works perfectly fine.

So my first question - Is there a property to the code i use, or a different code alltogether that can create a shortcut without the quotes in the target field?

Second. Since im checking two generated lists - List of users, and list of shortcuts to send to the user list.(both lists can be between 1 to 160 rows.)
This is the code i use to count the rows in both my macros.

Sub Send_Shortcuts()
Dim folderPath As String
Dim i As Integer

Application.ScreenUpdating = False
         NumRows = Range("a1", Range("a1").End(xlDown)).Rows.Count
         Range("a1").Select
         For i = 1 To NumRows
     
         folderPath = Range("a" & i).Value 
  
         nameofshortcut = Range("l1").Value
         targetfolderpath = Range("k1").Value
         sShortcutLocation = folderPath & "\" & nameofshortcut & ".lnk"

      With CreateObject("WScript.Shell").CreateShortcut(sShortcutLocation)
         .targetpath = targetfolderpath
         .Save
 ActiveCell.Offset(1, 0).Select
        
      Next
      Application.ScreenUpdating = True
End Sub

Problem i found with this is that the FOR Cycle overflows if the list is empty or with only 1 entry. Currently i have IF flags set up that prevent this, but out of sheer curiosity - is the a better way to count the rows, without error if the list is composed of 0 or 1 entries?

Im open to all suggestions and constructive criticism. Thanks in advance!

 

[rollis13]

First:
You find double quotes because you have spaces in your folder/file names.
Second:
If column A doesn't have a header use: NumRows = Cells(Rows.Count, "A").End(xlUp).Offset(Abs(Cells(Rows.Count, "A").End(xlUp).Value <> ""), 0).Row - 1

 

[Newbie212]

First:
You find double quotes because you have spaces in your folder/file names.
Second:
If column A doesn't have a header use: NumRows = Cells(Rows.Count, "A").End(xlUp).Offset(Abs(Cells(Rows.Count, "A").End(xlUp).Value <> ""), 0).Row - 1

Thanks Rollis, ill try №2 tommorrow when i get back to work.

For №1 im certain there are no spaces in the real forlder/file names they are substituted with "_". The name in the example is just something i made for illlustration. Anyway ill check the code again - maybe i have left some empty spaces when i declare the strings or something.

Thanks for your time and your input.

 

[Newbie212]

First:
You find double quotes because you have spaces in your folder/file names.
Second:
If column A doesn't have a header use: NumRows = Cells(Rows.Count, "A").End(xlUp).Offset(Abs(Cells(Rows.Count, "A").End(xlUp).Value <> ""), 0).Row - 1

Update.

First:
The row count works great and it saved me couple of rows of code. I love it.

Second:
You were absolutely right it works perfectly now! In one of my columns with the shortcut source directories i have exported them all with one blank space at the end. Once i removed it works like a charm!
Thanks again Rollis!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.