Combine ActiveCell.Value and Characters to search file

Question

I'm trying to hyperlink quote names in a workbook to the location of the files. Not all of the files are present, I would like to test against a boolean to see if they exist before creating the hyperlink. I have been using the filename from active cell to retain a string and search. The naming convention is 'XX-MMDDYY.XX. Saved files are under the name Quote_XX-MMDDYY.XX. I have combined "Quote_" wit the active cell to search for the file, but my macro only seems to loop through the list.

Sub LoopRange()

    Dim currRow As Integer, lastRow As Integer
    Dim ws As String, quoteID As String
    Dim path As String
    Dim FileName As String
    path = "C:\Some file path\"

    ws = "Quote LOG"
    currRow = 3
    lastRow = Sheets(ws).Cells(Sheets(ws).Rows.Count, "A").End(xlUp).Row

    While currRow <= lastRow
        Sheets(ws).Cells(currRow, 1).Select
        quoteID = "Quote_" & ActiveCell.value
        FileName = path & quoteID
        If Dir(FileName) <> "" And quoteID <> "" Then
            Sheets(ws).Hyperlinks.Add anchor:=Cells(currRow, 2), Address:=FileName, TextToDisplay:=quoteID
        End If
        currRow = currRow + 1
    Wend

End Sub

You need another backslash at the end of the path variable. `path = "C:\Some file path\"` — SierraOscar, Jul 20 '15 at 14:25

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

Did MacroMan's tip help? If not, let me know and I'll take a crack.

One thing though, that is very good to learn for VBA, is how to (avoid .Activate and .Select)[How to avoid using Select in Excel VBA macros. I have tweaked your code with this in mind, it should run a little smoother:

Sub LoopRange()

Dim currRow As Integer, lastRow As Integer
Dim ws As String, quoteID As String
Dim path          As String
Dim FileName      As String
Dim cellValue     As String

path = "C:\Some file path\"

ws = "Quote LOG"
currRow = 3
lastRow = Sheets(ws).Cells(Sheets(ws).Rows.Count, "A").End(xlUp).Row

While currRow <= lastRow
    'Sheets(ws).Cells(currRow, 1).Select
    cellValue = Sheets(ws).Cells(currRow, 1)
    'quoteID = "Quote_" & ActiveCell.Value
    quoteID = "Quote_" & Sheets(ws).Cells(currRow, 1)
    Debug.Print quoteID
    FileName = path & quoteID & ".pdf"
    If Dir(FileName) <> "" And quoteID <> "" Then
        Sheets(ws).Hyperlinks.Add anchor:=Cells(currRow, 2), Address:=FileName, TextToDisplay:=quoteID
    End If
    currRow = currRow + 1
Wend

End Sub

edited May 23 '17 at 11:51

Community

1
1

answered Jul 20 '15 at 15:51

BruceWayne

22,923
15
65
110

1

Yes, it did to define the path. For a while I just had it looping through afterwards – PeaButter Jul 20 '15 at 16:38
1

Thank you for the foresight NOT to use Active cell! – PeaButter Jul 20 '15 at 19:11
@PeaButter - Thanks, it's such a great thing to learn when starting VBA. Also, you could skip the entire "cellValue" variable, as it's not used anywhere. I edited it to show how to get around `.select` and `.activate`, but you could also have used `quoteID = "Quote_ " & cellValue`. Otherwise, that variable could be commented/deleted out. – BruceWayne Jul 20 '15 at 19:15
1

I've ran it a couple times (with an incomplete database), it is much more robust than active cell. – PeaButter Jul 20 '15 at 20:14
@PeaButter, it's working correctly, yeah? One way to speed it up too, is to add `Application.ScreenUpdating = False` at the beginning, under the declared values, and then `Application.ScreenUpdating = True` at the end, before `End Sub`. – BruceWayne Jul 20 '15 at 20:16
1

I'll give that a whirl too, sometime this morning. I'll keep you posted and let you know how it works out. – PeaButter Jul 21 '15 at 12:06
1

@PeaButter - how'd it go? Any comments or suggestions for code improvement? – BruceWayne Jul 21 '15 at 16:03
1

I didn't get a chance until lunch to get into that macro. This did update more robustly, I never got a "Not Responding" on the header of the workbook as this Macro ran. Apologies for the delayed response! – PeaButter Jul 21 '15 at 16:29

score 0 · Answer 2 · answered Jul 20 '15 at 15:53

My filenames were a mismatch from the code. I needed to add "Quote_" to quoteID and the filetype, in this case ".pdf" to FileName

** Sub LoopRange()

Dim currRow As Integer, lastRow As Integer
Dim ws As String, quoteID As String
Dim path As String
Dim FileName As String
path = "C:\Users\pxbotr\Documents\Quote DataBase\"

ws = "Quote LOG"
currRow = 3
lastRow = Sheets(ws).Cells(Sheets(ws).Rows.Count, "A").End(xlUp).Row

While currRow <= lastRow
    Sheets(ws).Cells(currRow, 1).Select
    quoteID = "Quote_" & ActiveCell.Value
    FileName = path & quoteID & ".pdf"
    If Dir(FileName) <> "" And quoteID <> "" Then
        Sheets(ws).Hyperlinks.Add anchor:=Cells(currRow, 1), Address:=FileName, TextToDisplay:=quoteID
    End If
    currRow = currRow + 1
Wend

End Sub

**

One way to shorten the code, albeit a little, is to chage `ws = "quoteLog"` and do `dim ws As Worksheet` then `Set ws = Sheets("Quote LOG")` then in the later code you can just do `lastRow = ws.cells(ws.rows.count, ...` and `ws.Hyperlinks.add ...` — BruceWayne, Jul 20 '15 at 20:18

Combine ActiveCell.Value and Characters to search file

2 Answers2