PDA

View Full Version : Sleeper: Loop through range to copy/paste hyperlink



pdeshazier
05-30-2005, 05:54 AM
For all cells in a column, i need to make a hyperlink and i wrote the following macro to copy the 'display as text' property to the 'hyperlink' property (hope i'm using correct lingo). Now, I need to put this in a loop so the macro will go through the entire column and make the appropriate hyperlinks. Have tried to do simple loops (but i really have no experience w/loops in VBA) and keep getting error messages, so can someone please help??? Thanks.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524" _
, TextToDisplay:= _
"www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524 (http://www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524)"

Bob Phillips
05-30-2005, 06:53 AM
For all cells in a column, i need to make a hyperlink and i wrote the following macro to copy the 'display as text' property to the 'hyperlink' property (hope i'm using correct lingo). Now, I need to put this in a loop so the macro will go through the entire column and make the appropriate hyperlinks. Have tried to do simple loops (but i really have no experience w/loops in VBA) and keep getting error messages, so can someone please help??? Thanks.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524" _
, TextToDisplay:= _
"<A href="http://www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524"[/QUOTE" target=_blank>www.meditech.com/dts/srupd/ReqDetail.asp?GO=Y&PL=MG&Appl=BAR&ReqNum=9524"


Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, "A"), _
Address:=Cells(i, "A").Value
Next i

pdeshazier
05-30-2005, 07:17 AM
This code works (THANKS)! However, once the hyperlinks are created (which point to a certain website) and I click on them, i get an excel error "cannot open the specified file." Any idea why? thanks.

pdeshazier
05-30-2005, 07:21 AM
I think it's because the code above doesn't put the http:// in the hyperlink, but when I manually right click the cell, click hyperlink, CTRL C the 'text to display' and CTRL V into the 'hyperlink', it DOES put http:// in front. How can i incorporate this into the code above? Thanks.

Bob Phillips
05-30-2005, 07:26 AM
This code works (THANKS)! However, once the hyperlinks are created (which point to a certain website) and I click on them, i get an excel error "cannot open the specified file." Any idea why? thanks.

Sorry, didn't spot that. Try this version



Dim iLastRow As Long
Dim sURL As String
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
sURL = Cells(i, "A").Value
If Left(sURL, 7) <> "http://" Then
sURL = "http://" & sURL
End If
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, "A"), _
Address:=sURL
Next i

pdeshazier
05-30-2005, 07:43 AM
:beerchug: You're awesome. It worked. Just one more thing, I was an RPG IV programmer, but VBA is very different. Can you suggest how I should get started learning it? Thanks sooooooo much!!!

pdeshazier
05-30-2005, 08:00 AM
One more question:
Can you tell me exactly how this statement is working to determine the last row? thanks.:bug:

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Bob Phillips
05-30-2005, 08:11 AM
I was an RPG IV programmer, but VBA is very different. Can you suggest how I should get started learning it? Thanks sooooooo much!!!

Sorry I don't know your name, so I can't address you properly :)

If you have programmed in RPG IV you must have the basic concepts of programming understood, loops, functions, variables, etc. (I say must, but as I have no idea of RPG IV that should really be assume :)).

As such, the thing you will probably find the hardest is Object Orientatied coding (does RPG IV work on objects?) and the Excel object model. Once you have gotten to grips with these, you will be well on your way.

To help with this I would recommend

'-----------------------------------------------------------------
Excel VBA Programming For Dummis

http://j-walk.com/ss/books/xlvbaprogdum2.gif

Author: John Walkenbach

ISBN: 0-7645-7412-4

Publisher: Wiley, 2004, 390 pp., List price $24.99 (US)

Status: Available

Target Reader:This book is intended for experienced Excel users who want to learn the basics of VBA programming. It's good for Excel 2000, 2002, and 2003.

Comments: This is a light-hearted guide to learning VBA programming. Despite the title, you'll find lots of useful information, tips, and techniques. The book doesn't have a CD, but the sample files are available to download from the Web.

Publisher's Web Site (http://www.dummies.com/WileyCDA/DummiesTitle/productCd-0764574124,page-1.html): Contains downloads and a sample chapter.
'-----------------------------------------------------------------

Don't be put off by the 'For Dummies' title, it is quality stuff. I append a chapter list at the end for you to see the topics covered.

After that, when you have achieved some proficiency, you might want to move onto

'-----------------------------------------------------------------
Excel 2002 VBA Programmer's Reference

http://www.xldynamic.com/images/books/excel2002vba.gif

Author(s): John Green Rob Bovey Stephen Bullen Robert Rosenberg]
Format: Paperback, 2nd ed., 600pp.
ISBN: 1-861005-70-9
Publisher: Wrox
'-----------------------------------------------------------------

Be careful with this one though. There is a revised 2003 version. Do NOT buy the 2003 version, the revision has been badly done, not a patch on the 2002 version.

You might also think this covers much of the same ground as John's book, so check out for yourself.

The other thing to do is to visit newsgroups and/or forums like this and check out what questions are being asked and the responses, and have a go at answering some yourself. Even if your reply gets kicked back (hopefully nicely), you will learn something.

And most important of all, get yourself a meaningful project. something you want to automate, that will allow you to learn productively.

Anyway, the ToC as promised




Table of Contents

Part I: Introducing VBA

Chapter 1. What is VBA?
Chapter 2. Jumping Right In
Part II: How VBA Works With Excel

Chapter 3. Introducing the Visual Basic Editor
Chapter 4. Introducing Excel's Object Model
Chapter 5. VBA Sub and Function Procedures
Chapter 6. Using the Excel Macro Recorder
Part III: Programming Concepts

Chapter 7. Essential VBA Language Elements
Chapter 8. Working With Range Objects
Chapter 9. Using VBA and Worksheet Functions
Chapter 10. Controlling Program Flow and Making Decisions
Chapter 11. Automatic Procedures and Events
Chapter 12. Error-Handling Techniques
Chapter 13. Bug Extermination Techniques
Chapter 14. VBA Programming Examples
Part IV: Developing Custom Dialog Boxes

Chapter 15. Custom Dialog Box Alternatives
Chapter 16. Custom Dialog Box Basics
Chapter 17. Using Dialog Box Controls
Chapter 18. Dialog Boxes Techniques and Tricks
Part V: Creating Custom Menus and Toolbars

Chapter 19. Customizing the Excel Toolbars
Chapter 20. When the Normal Excel Menus Aren't Good Enough
Part VI: Putting It All Together

Chapter 21. Creating Worksheet Functions - And Living to Tell About It
Chapter 22. Creating Excel Add-Ins
Chapter 23. Interacting With Other Office Applications
Part VII: The Part of Tens

Chapter 24. Top Ten VBA Questions (and Answers)
Chapter 25. (Almost) Ten Excel Resources

pdeshazier
05-30-2005, 08:18 AM
:help Another complication:
This code will need to run for every sheet I have in my workbook. The column will always be the same, but how do I incorporate into the code the ability to run it on every sheet? My goal is to have a command button on the very last sheet that when clicked will run the macro for every spreadsheet in the workbook.

Bob Phillips
05-30-2005, 08:18 AM
Can you tell me exactly how this statement is working to determine the last row? thanks.:bug:
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Rows.Count is a built-in constant that tells VBA how many rows there are ina worksheet.

This is then used as a start point in the deterimination
Cells(Rows.Count,"A")
effectively starts at the last row in column A.

It then works its way up until the last non-empty cell (that is the first one it meets on the way up)
.End(xlUp)

and returns the row number
.Row

which it uses then on.

An alternative way is to look down for the row before the first empty cell
Range("A1").End(xlDown).Row

I prefer the former method in case there are any blank lines. Consider this scenario

A1: Value 1
A2: Value 2
A3: blank
A4: Value 3

the xlUp method returns 2. the xlDown methid returns 2, so Value 3 would not get processed.

pdeshazier
05-30-2005, 08:19 AM
Thanks for suggesting the above tools for learning. Will go get a couple of them

pdeshazier
05-30-2005, 08:20 AM
Ingenious. Thanks so much for making sense of it. i was thinking of going down rather than up, but you make a great point about the blank cells.

pdeshazier
05-30-2005, 08:21 AM
Missed the name part. My name is Pam and I am soooo glad to meet you.:hi:

Bob Phillips
05-30-2005, 08:22 AM
:help Another complication:
This code will need to run for every sheet I have in my workbook. The column will always be the same, but how do I incorporate into the code the ability to run it on every sheet? My goal is to have a command button on the very last sheet that when clicked will run the macro for every spreadsheet in the workbook.



Dim sh As Worksheet
Dim iLastRow As Long
Dim sURL As String
Dim i As Long
For Each sh In ThisWorkbook.Worksheets
With sh
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
sURL = .Cells(i, "A").Value
If Left(sURL, 7) <> "http://" Then
sURL = "http://" & sURL
End If
.Hyperlinks.Add Anchor:=.Cells(i, "A"), Address:=sURL
Next i
End With
Next sh

Bob Phillips
05-30-2005, 08:31 AM
Ingenious. Thanks so much for making sense of it. i was thinking of going down rather than up, but you make a great point about the blank cells.

There is a somewhat analagous situation when inserting or deleting rows.

If you start at the top and work you way down, the pointers can get confused, and so you may not process some lines. So it better to work bottom up in these cases.

So for instance, put these values in A1:A8 - 17,1,17,2,17,17,17,3, then run this code



Sub InsertRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = 17 Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub



We were supposed to insert a blank row abover every 17, not quite what we achieved :)).

Restore the data, make this minor change to the code



Sub InsertRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = 17 Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub


and run it again. Success!

Bob Phillips
05-30-2005, 08:33 AM
Missed the name part. My name is Pam and I am soooo glad to meet you.:hi:

:hi: Pleased to meet you too Pam, and welcome to VBAX. :thumb

Best Regards

Bob

pdeshazier
05-30-2005, 08:57 AM
Thanks a million times for your help. You've made me and my coworkers very happy! I hope to work w/you again in the future as I get more experience.

pdeshazier
05-30-2005, 11:32 AM
Dim sh As Worksheet
Dim iLastRow As Long
I open my workbook and run this code, but it only executes on the worksheet my cursor is on. How do I get it to execute for all sheets?:banghead:


Dim sURL As String
Dim i As Long
For Each sh In ThisWorkbook.Worksheets
With sh
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
sURL = .Cells(i, "A").Value
If Left(sURL, 7) <> "http://" Then
sURL = "http://" & sURL
End If
.Hyperlinks.Add Anchor:=.Cells(i, "A"), Address:=sURL
Next i
End With
Next sh

Bob Phillips
05-30-2005, 11:35 AM
I open my workbook and run this code, but it only executes on the worksheet my cursor is on. How do I get it to execute for all sheets?

Pam,

It should execute on every sheet. What it doesn't do is to activate those sheets, so you won't see it happening. But if you go to the other sheets, you should see that they have been linked okay.

pdeshazier
05-30-2005, 11:56 AM
It's not executing on every sheet. Only the sheet the cursor happens to be on. Any way I can attach my file? I uploaded it, but don't see that it's attached so you can see it..:bug:

Bob Phillips
05-30-2005, 11:58 AM
It's not executing on every sheet. Only the sheet the cursor happens to be on. Any way I can attach my file? I uploaded it, but don't see that it's attached so you can see it..:bug:

Pam,

Use this site and load it there, and let me know the URL it gives you http://cjoint.com/index.php

pdeshazier
05-30-2005, 12:47 PM
it's running multiple times on the active sheet's column rather than running once then going to next sheet and running , etc.

pdeshazier
05-30-2005, 12:49 PM
just now saw that you sent this. trying to load it there, but got this:
Warning: unlink(): No such file or directory in /home/cjoint/www/index.php on line 180

Warning: Cannot modify header information - headers already sent by (output started at /home/cjoint/www/index.php:180) in /home/cjoint/www/index.php on line 454

Bob Phillips
05-30-2005, 01:35 PM
Pam,

If you put it in a zip file, you should be able to add it to your post here. There is an Additional Options part below the message box that has an Attachments section.

Bob Phillips
05-30-2005, 01:38 PM
I have just posted a working example at that site at http://cjoint.com/?fEw438qiRC (http://cjoint.com/?fEw438qiRC)

johnske
05-30-2005, 01:58 PM
... Can you suggest how I should get started learning it? ...

Hi Pam,

Welcome to VBAX, aside from our help forums, VBAX also offers training and certification in VBA, have a look here http://www.vbaexpress.com/training.htm or have a read through the first lesson for free here http://www.vbaexpress.com/training/lesson01.htm

Regards,
John :hi: