By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 739 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Loop through Folders with Hyperlinks

P: 7
{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.

Expand|Select|Wrap|Line Numbers
  1. 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?
Aug 27 '18 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. strName = "Yahoo Home Page"
  2. strLink = "http://www.yahoo.com"
  3. MySQL = Replace("UPDATE [tblTest] SET [MyHyperlink]='%N#%L'" _
  4.               , "%N", strName)
  5. MySQL = Replace(MySQL, "%L", strLink)

Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
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!
Aug 27 '18 #2

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

Many thanks.
Aug 27 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
Please let me know if this is an appropriate thread title.

Glad I could be of some hepp!
Aug 27 '18 #4

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. strName = "Yahoo Home Page"
  2. strLink = "http://www.yahoo.com"
  3. MySQL = Replace("UPDATE [tblTest] SET [MyHyperlink]='%N#%L'" _
  4.               , "%N", strName)
  5. MySQL = Replace(MySQL, "%L", strLink)
Aug 27 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Thanks, NeoPa!

I've been busy at work, haven't had a chance to look much into this.
Aug 27 '18 #6

P: 7
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?
Aug 28 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 3,284
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!
Aug 28 '18 #8

NeoPa
Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
  2. '  first with the second wherever found in strMain.
  3. 'Using VbBinaryCompare means that case is recognised and not ignored.
  4. '08/05/2013 Updated to support passing of an array directly into avarArgs.
  5. Public Function MultiReplace(ByRef strMain As String _
  6.                            , ParamArray avarArgs() As Variant) As String
  7.     Dim intX As Integer
  8.     Dim avarVals() As Variant
  9.  
  10.     'Code to handle avarArgs passed as an existing array.
  11.     If (UBound(avarArgs) = LBound(avarArgs)) _
  12.     And IsArray(avarArgs(LBound(avarArgs))) Then
  13.         ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
  14.         For intX = LBound(avarVals) To UBound(avarVals)
  15.             avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
  16.         Next intX
  17.     Else
  18.         avarVals = avarArgs
  19.     End If
  20.     If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
  21.     MultiReplace = strMain
  22.     For intX = LBound(avarVals) To UBound(avarVals) Step 2
  23.         MultiReplace = Replace(Expression:=MultiReplace, _
  24.                                Find:=Nz(avarVals(intX), ""), _
  25.                                Replace:=Nz(avarVals(intX + 1), ""), _
  26.                                Compare:=vbBinaryCompare)
  27.     Next intX
  28. End Function
With that you can simply use :
Expand|Select|Wrap|Line Numbers
  1. strName = "Yahoo Home Page"
  2. strLink = "http://www.yahoo.com"
  3. MySQL = MultiReplace("UPDATE [tblTest] SET [MyHyperlink]='%N#%L'" _
  4.                    , "%N", strName _
  5.                    , "%L", strLink)
Aug 29 '18 #9

P: 7
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?
Aug 29 '18 #10

Post your reply

Sign in to post your reply or Sign up for a free account.