Loop through Folders with Hyperlinks

[Tegglet]

{NB: this is a moderated redirect from this thread: Inserting Hyperlink into a table}


I can get the code line below working by substituting my own strings but I need to loop through a folder of files writing the hyperlink for each file to the table and so need to use variables instead if strings. The code below does not interpret the variable names, presumably because the whole statement is between quotes.

MySQL = "Update tblTest Set tblTest.[MyHyperlink] = 'Yahoo Home Page#http://www.yahoo.com'"

I have tried a number of things unsuccessfully. How can I substitute the strings for variables so that the hyperlink reflects the current filename?

[twinnyfo]

Tegglet,

Welcome to Bytes!

It sounds like your problem is unique to this thread. We would ask you to start a new thread with a new question. You can refer back to this thread if you wish. Also, please flesh out your question a bit and show us what you have tried so far, so that we can better provide guidance to a solution.

If you prefer, I can move your post to begin a new thread. Please let me know.

Thanks!

[Tegglet]

Yes please do that.
I am about to get my dinner and will respond with more details as requested later this evening.

Many thanks.

[twinnyfo]

Please let me know if this is an appropriate thread title.

Glad I could be of some hepp!

[NeoPa]

Here's some template code to handle that sort of thing.

Assuming the Name and the link are in two variables called strName & strLink then the code could include something like :

strName = "Yahoo Home Page"
strLink = "http://www.yahoo.com"
MySQL = Replace("UPDATE [tblTest] SET [MyHyperlink]='%N#%L'" _
              , "%N", strName)
MySQL = Replace(MySQL, "%L", strLink)

[twinnyfo]

Thanks, NeoPa!

I've been busy at work, haven't had a chance to look much into this.

[Tegglet]

Hi NeoPa,
Many thanks, that did the trick. Never thought of %var, back to DOS and batch files ;-)
Regards
Ted
I have marked this as "Best Reply". Is that all I need to do to close it?

[twinnyfo]

Tegglet,

That it! Glad we could be of hepp! NeoPa has all sorts of tricks up his sleeve. He taught me that trick recently and it has helped me in constructing my complicated SQL strings.

Hope you have a great day!

[NeoPa]

Tegglet:
Never thought of %var, back to DOS and batch files ;-)

Absolutely. In another lifetime I was a bit of a BAT/CMD guru.

If you're interested I have some code I use very heavily to handle multiple replacement vars. It's called MultiReplace() and I find it invaluable. It works with vbBinaryCompare to ensure comparisons are exact.

'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
'  first with the second wherever found in strMain.
'Using VbBinaryCompare means that case is recognised and not ignored.
'08/05/2013 Updated to support passing of an array directly into avarArgs.
Public Function MultiReplace(ByRef strMain As String _
                           , ParamArray avarArgs() As Variant) As String
    Dim intX As Integer
    Dim avarVals() As Variant
 
    'Code to handle avarArgs passed as an existing array.
    If (UBound(avarArgs) = LBound(avarArgs)) _
    And IsArray(avarArgs(LBound(avarArgs))) Then
        ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
        For intX = LBound(avarVals) To UBound(avarVals)
            avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
        Next intX
    Else
        avarVals = avarArgs
    End If
    If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
    MultiReplace = strMain
    For intX = LBound(avarVals) To UBound(avarVals) Step 2
        MultiReplace = Replace(Expression:=MultiReplace, _
                               Find:=Nz(avarVals(intX), ""), _
                               Replace:=Nz(avarVals(intX + 1), ""), _
                               Compare:=vbBinaryCompare)
    Next intX
End Function

With that you can simply use :

strName = "Yahoo Home Page"
strLink = "http://www.yahoo.com"
MySQL = MultiReplace("UPDATE [tblTest] SET [MyHyperlink]='%N#%L'" _
                   , "%N", strName _
                   , "%L", strLink)

[Tegglet]

Thanks everybody for your help so far.
I am getting in a right pickle with this project so I am starting a new topic describing the core requirements, which are to read the filenames of the contents of a folder into a table and convert the names to hyperlinks. Whatever happened to good old plain and simple get and put?