Tip & How-To about Microsoft Excel for PC

Script to extract URLs in Excel Spreadsheets

The following script could be used to extract the actual URLs from the text link in an Excel spreadsheet. The script writes each URL in the cell next to the text link:

Sub main()
Dim h As Hyperlink

For Each h In ActiveSheet.Hyperlinks
h.Range.Offset(, 1) = h.Address
Next
End Sub

Here is how to use the script:
1. On the "Tools" menu, point to "Macro", and then click "Visual Basic Editor". This will open the Visual Basic Editor in another window.
2. On the extreme left, you will find the "Project - VBAProject". Double-click the icon named "Sheet1 (Sheet1)", "Sheet2 (Sheet2)", or whatever the name of your worksheet. This will open a blank window. Copy & paste the script into this window.
3. Press F5, then click on "Run".
4. Go back to your Excel window. You will now find the URLs listed in the next cell to the original one with the hyperlink.

Posted by on

Microsoft Excel for PC Logo

Related Topics:

Related Questions:

1 Answer

Microsoft Excel formats


Hi ahmed,
I suggest You Go to Official HPPrinter Page For You HP Printer Problem On Below HP forum URL Link:
HP P2035 Issue Printing from MS Excel HP Enterprise Business Community In Case You Still face Problem and Need Online Assistance Then Please Follow Us For On : HP Support

Mar 04, 2014 | HP LaserJet P2055d Printer

2 Answers

Excel 2000 Issue???


.Let's start with this for more info:
Is the URL icon there but blank? (Maybe an X in the middle of the box?) If it's there, do the following:

Right-click on the URL
2. Select Hyperlink from the menu that appears
3. Is Remove Hyperlink box checked or unchecked?

This is a setting that is machine-specific. Post the answer as a comment and we'll take the next step if necessary

Apr 02, 2009 | Microsoft Office 2000 Professional for PC

1 Answer

write into excel format


You can use ADO or SQL to open the excel file directly (provided it is constructed correctly, ie fields names) and write the data to it without Access.
That might be eaiser thatn converting it from Access to Excel, if that can even be done.

Feb 17, 2009 | Microsoft Visual Basic 6.0 for PC

1 Answer

Need Formula


TEXT CASE
Convert to UPPER, Proper or lower
Auto Convert to UPPER, Proper or lower
Stop VBA Being Case Sensitive

SELECT CASE VBA
Select Case Statement. Great alternative to Else If Statements

CONVERSION
VBA: CBool(),CByt(),CCur(),CDate(),CDbl()Double,CDec(),CInt(),CLng(),CSng(),CStr(),CVar().
Example
dDate=cDate("25-Jan-07")
Convert Numbers to Words
Convert Numbers to Dollar Amount in Words
Spreadsheet Converter. Versions: HTML/JavaScript,Excel ASP.Net and Excel Java/JSP. $
SQL Database Migration $
Converters. Database Converters, Spreadsheet Converters, Text Converters, E-mail Conversion, Document Conversion, PDF Conversion and more! $
CSV Converter $

IMPORTING
Import SQL Into Excel-SQL Tester $
Import Excel Into Access $

CONDITIONAL FORMATTING
Conditional Formatting
More Than 3 Conditional Formats
Conditional Format Fonts for greater than 3

CUSTOM FORMATS
Custom Formats
Excel see a cells format as having four Sections. These are, from left to right: Positives;Negatives;Zeros;Text.

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired format for non zeros. Note the use of -0.00 for negatives.

To hide zeros on the Workbook level go to Tools>Options>View - Zero Values.

Dec 15, 2008 | Microsoft Windows XP Home Edition

4 Answers

Running SQL queries on Excel


Another way to do it is to use the SQL Drill freeware Excel addin (http://www.sqldrill.com)
hth

Aug 14, 2007 | Microsoft Office 2003 Basic Edition...

Not finding what you are looking for?

1,165 people viewed this tip

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2658 Answers

Piyal Perera
Piyal Perera

Level 3 Expert

528 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18389 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Loading...