VBA - Resolving Variables in a Loop

Question

I'm using VBA to pull excel books from a SharePoint and save them in a specified folder, and I have about 11 analysts whose workbooks I need to pull. I would rather not copy/paste the formula I'm using 11 times, so I'm trying to store their name's in variables and then use a loop. I can't seem to get it to work right though.

Here's a sample of what I have so far:

    Sub DownloadTimeTrackers()
Dim i As Integer
Dim Name1 As String
Dim Name2 As String
Dim Name3 As String

Name1 = "Barb"
Name2 = "Alex"
Name3 = "Cole"

For i = 1 To 3 Step 1

Const strUrl As String = "mypath\Secured\Name(i) Time Tracker.xlsm"
Dim strSavePath As String
Dim returnValue As Long

 strSavePath = "C:\Users\lxys\Desktop\TimeTrack\Name(i) Time Tracker.xlsm"
 returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

Next I

End Sub

I excluded some code that assists in pulling the report from SP, but I don't believe it's relevant for this..

If I substitute Name(i) for the actual name "Barb", the formula works fine, and saves to my desktop folder.

How do I get Name(i) to substitute for Barb, Alex, Cole, etc? I've tried Namei, Name + i, and Name(i) with no luck. Any suggestions why this doesn't seem to resolve correctly? I don't get any errors.

EDIT:

Based on feedback, I changed my program to the below, but I receive a "Subscript Out of Range" Error:

    Sub DownloadTimeTrackers()
Dim i As Integer
Dim Name() As Variant
Dim strUrl As String

Name = Array("Barb", "Alex", "Cole", "Cim", "Adam", "Lane", "Kristin", "Mike", "Nicki", "George", "Lori")


For i = 1 To 11 Step 1

        strUrl = "\\collab.sfcollab.org@SSL\DavWWWRoot\sites\WSS005469\DisputeTeam\Secured\" & Name(i) & "Time Tracker.xlsm"

Dim strSavePath As String
Dim returnValue As Long

 strSavePath = "C:\Users\fip1\Desktop\TimeTrack\" & Name(i) & "Time Tracker.xlsm"
 returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

Next i



End Sub

I found the error, I didn't have a space in " Time Tracker.xlsm"

Answer 1

Instead of

Dim Name1 As String
Dim Name2 As String
Dim Name3 As String

Name1 = "Barb"
Name2 = "Alex"
Name3 = "Cole"

use

Dim Name() As Variant
Name = Array("Barb", "Alex", "Cole")

This creates an "array" of names that can be indexed by your variable i. Note that this array will be zero-based by default, i.e. the first position will be Name(0)

---

You also need to change

Const strUrl As String = "mypath\Secured\Name(i) Time Tracker.xlsm"

to

Dim strUrl As String
strUrl = "mypath\Secured\" & Name(i) & " Time Tracker.xlsm"

(you can't have a "constant" which has varying values)

---

And change

strSavePath = "C:\Users\lxys\Desktop\TimeTrack\Name(i) Time Tracker.xlsm"

to

strSavePath = "C:\Users\lxys\Desktop\TimeTrack\" & Name(i) & " Time Tracker.xlsm"

---

Your refactored code would be

Sub DownloadTimeTrackers()
    Dim strUrl As String
    Dim strSavePath As String
    Dim returnValue As Long
    Dim i As Integer
    Dim Name() As Variant

    Name = Array("Barb", "Alex", "Cole")

    For i = LBound(Name) To UBound(Name)

        strUrl = "mypath\Secured\" & Name(i) & " Time Tracker.xlsm"
        strSavePath = "C:\Users\lxys\Desktop\TimeTrack\" & Name(i) & " Time Tracker.xlsm"
        returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

    Next i

End Sub

(I also changed your loop to go from the lower bound of the array to the upper bound. That way you can add extra names to the array and not have to worry about changing the For statement.)