Simple? Modify Macro: Add date in Column "C" to local filename

[jeffcoleky]

Dim URL As StringDim LocalFileName As String
Dim B As Boolean
Dim ErrorText As String
Dim C As Range
Dim sTxt As String
sTxt = Cells(1, "a").Value
Dim Lastrow As Long
    
Lastrow = Range("C:D").Find("*", , , , xlByRows, xlPrevious).Row
For Each C In Range("C1:D" & Lastrow)
    If Len(C) > 0 Then
        URL = C.Text
[B]          LocalFileName = "C:\temp\" & Range("A" & C.Row) & " " & Range("A" & C.Row) & " " & Evaluate("TRIM(RIGHT(SUBSTITUTE(""" & C.Text & """,""/"",REPT("" "",1000)),1000))")[/B]

        B = DownloadFile(UrlFileName:=URL, _
                         DestinationFileName:=LocalFileName, _
                         Overwrite:=DoNotOverwrite, _
                         ErrorText:=ErrorText)
        If B = True Then
            Debug.Print "Download successful"
        Else
            Debug.Print "Download unsuccessful: " & ErrorText
        End If
    End If
Next C


End Sub

Currently, this code does not include the data in column C in the local filename. However, I would like it to. The dates in column C are formatted as "mm'dd/yyyy" but I'm OK with the local filename formatted as "mmddyyy" or "mm-dd-yyyy".

Can anyone help me update the code above to include the date value in column C for each line?

 

[Andrew Poulsom]

I don't know why you have column A twice, but does this work for you?

LocalFileName = "C:\temp\" & Range("A" & C.Row) & " " & Range("A" & C.Row) & " " & Format(C.Value, "mmddyyyy")

 

[jeffcoleky]

Oops! I meant to type that the date is located in Column "B", not column C.

 

[Andrew Poulsom]

So what do you want as the filename (give column references)?

 

[jeffcoleky]

COLUMN A, Then COLUMN B, Then the formula already in the code "Evaluate("TRIM(RIGHT(SUBSTITUTE(""" & C.Text & """,""/"",REPT("" "",1000)),1000))")"

 

[Andrew Poulsom]

You can apply the Format function that I used to column B (if it contains a date).

 

[jeffcoleky]

do you mind writing it out for me? I'm dancing in circles trying to figure this simple step out. One of those days.

 

[Andrew Poulsom]

Is it?

LocalFileName = "C:\temp\" & Range("A" & C.Row) & " " & Format(Range("B" & C.Row), "mmddyyyy") & " " & Evaluate("TRIM(RIGHT(SUBSTITUTE(""" & C.Text & """,""/"",REPT("" "",1000)),1000))")

 

[jeffcoleky]

Is it?

LocalFileName = "C:\temp\" & Range("A" & C.Row) & " " & Format(Range("B" & C.Row), "mmddyyyy") & " " & Evaluate("TRIM(RIGHT(SUBSTITUTE(""" & C.Text & """,""/"",REPT("" "",1000)),1000))")

Worked great! exactly what needed! Thanks