Solved

Macro/Code To Open Launch URL in Cell

Posted on 2013-01-14
13
332 Views
Last Modified: 2013-01-26
Hello Experts,

I'm looking for a code, that will do a couple of things. But I would imagine, the coding itself would be super easy - just not for someone like me.

The code would need to do this...

Step 1:  Check to see if cells, A1, B1, C1 have values - IF YES, then continue to step 2. (IF NO, then do nothing.)

Step 2:  Open the URL, located in cell D1.  And by open, I mean automatically open my browser window (if possible) to the URL that is located in the cell.

Thank you in advance for your help!

~ Geekamo
0
Comment
Question by:Geekamo
  • 7
  • 6
13 Comments
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
I forgot to mention, I plan on having a button - when pressed, then the code runs.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
Hi, Geekamo.

Please see attached. The code is...
Option Explicit

Sub Goto_Web()

If Range("A1") <> "" And Range("B1") <> "" And Range("C1") <> "" And Range("D1") <> "" Then
    ActiveWorkbook.FollowHyperlink Address:=Range("D1").Value
End If

End Sub

Open in new window

Regards,
Brian.Goto-Web.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
@ Brian

The code you created worked great on my home computer (Macbook Air) without any hiccups, however on my work computer (PC) - when the code was run, Excel basically freezes and Internet Explorer fails to load.

Any ideas?

If this is just some weird glitch, that I can't technically troubleshoot as much because it's a work computer - I wonder if my solution would be to have the code just copy that URL into memory, so the user can just paste the URL into Internet Explorer on their own.

What do you think?

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Geekamo,

Let's experiment. The attached has three extra ways for getting the data, please try each one...
(1) Click on D10 - it should open a new page in your browser.
(2) Click on D11 - it should open a new page in your browser.
(3) On the Ribbon's menu bar, click on "Data", then click on the "Refresh All" button. This should import the page's details into the yellow cells.

Let me know what happens for each, please.

Thanks,
Brian.Goto-Web-V2.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
@ Brian,

Hmm, I tried looking for your code in this workbook - but I was unable to find it? Are you hiding it?  Not sure if I'm missing something here.

As for the steps,...

Step 1 - Did not open my browser. (I do see the URL, but it doesn't appear blue like a hyperlink would)
Step 2 - Did not open my browser. (I clicked, but the cell appears empty to me?)
Step 3 - It loaded text from I guess Office.com, all the way down the yellow column.

I'm testing this on my Macbook Air, but I'm a little hesitant testing the workbook on my work computer if I can't see the code.  So now I'm back to my first question, is it blocked or am I just missing something here?

This is all French to me!  :-)

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Geekamo.

There is no code - other than that already in the first file.

Options 1 & 2 are simply the two types of hyperlinks, while the "yellow" data is a connection. To see exactly what they are...
 - Right-click on D10 and select "Edit Hyperlink...".
 - D11 is simply a formula.
 - On the Ribbon's menu bar, click on "Data". Click on "Connections". You'll see a single connection called "Connection". Click the "Properties..." button and then the "Definition" tab. In "Connection String" you'll see the address being used.

I've no experience of Macs so I don't know why (1) and (2) didn't work. Hopefully the above will give you enough confidence to try them on your work PC.

Regards,
Brian.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:Geekamo
Comment Utility
@ Brian,

Wow, tonight isn't my night.  Options 1 & 2, - YES, they work.  I was looking at the wrong cells. lol Ugh.  As for checking the connection thing, I don't see it. So maybe my Mac version doesn't support that? (I've included a screenshot)

~ Geekamo
Screen-Shot-2013-01-16-at-11.56..png
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Geekamo,

Thanks!

As for checking the connection thing, I don't see it.
Yes, the Mac looks quite different to a PC. A number of options...
 - Click on G1 - did new entries appear in the Ribbon?
 - Click on G1 - check the options available in the "Refresh" drop-down.
 - Do a search in Help for "Connection Properties" or similar.
 - Check out the Connection properties on your work PC before doing a Refresh.

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
@ Brian,

Nope, there doesn't appear to be anything new.  Ok, I will look into "Connection Properties" for Excel Mac version. And I'll let you know what happens with my work computer.

~ Geekamo
Screen-Shot-2013-01-17-at-12.40..png
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Geekamo. Interesting that "Parameters" is greyed out.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Geeka. All OK with the work computer? Did you get the Connection issued on the MAC?
0
 
LVL 1

Author Comment

by:Geekamo
Comment Utility
Brian,

Not exactly.  This project has been put on the back burner so I haven't had the time to invest into trouble shooting it further.  Your first solution did work, on my Mac computer - so as far as I was concerned the points were yours.

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks for the update Geekamo. If the clock starts again and you have any issues, please feel free to post here. (Apologies, after a week or so, it might take me a few days to notice a new post.)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now