1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Save URL as shortcut

Discussion in 'Business Applications' started by awsmitty, May 15, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. awsmitty

    awsmitty Thread Starter

    Joined:
    Nov 6, 2009
    Messages:
    51
    In excel (2010), using vba, I would like to save a web page's URL to a certain folder, similar to saving the URL to your Favorites Folder, or similar to clicking File>Send>Shortcut to the Desktop from the menu bar on iexplorer. I am not talking about an of the content like downloads, pdf, or any of the JavaScript, just the URL that is up there in the address bar. Is this possible?


    Something like
    Dim CurrentWebAddress as String


    CurrentWebAddress = ....The Current, Active Web Address


    Thanks


    awsmitty
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Is the opened web page embedded into the Excel file or are you talking about having Excel evaluate seperately opened instance of IE and saving the URL?


    Rollin
     
  3. awsmitty

    awsmitty Thread Starter

    Joined:
    Nov 6, 2009
    Messages:
    51
    Rollin_Again, thanks for looking into this. The later, having Excel evaluate separately opened instance of IE and saving the URL. If necessary I can close all other web pages except the one involved before running the macro if that helps


    In other words I would like to save ... say ... http://forums.techguy.org/business-applications/1148307-save-url-shortcut.html, i.e., this very web page as a shortcut to a folder, for now we can use the Desktop.


    It would be exactly like File>Send>Shortcut to the Desktop or saving to your Favorites Folder, as mentioned above, except when I'm done they will be going to dynamic destinations (paths), determined by the web page and subject matter involved.


    Hope that helps clarify.

    I should add, I'm trying to make a shortcut, to the hard drive (a folder), not a hyperlink inside a excel worksheet.

    Thanks again


    awsmitty
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try the code below just change the path where the shortcut will be saved in the following line >> vPath = "C:\TestFolder\"

    If you don't want to "hard code" the save path you'll need to provide me with the logic to determine where the shortcuts will be saved to or we can trigger a Windows Explorer type dialogue box to pop up that will allow you to pick the destination by clicking through the folder tree.

    Code:
    Sub Create_ShortCut()
    
    Dim vPath as String
    Set objShell = CreateObject("Shell.Application")
    Set objWindows = objShell.Windows
    Set WshShell = CreateObject("WScript.Shell")
        
        'Set the save path
        vPath = "C:\TestFolder\"
        
        
    For Each vWindow In objWindows
         If (InStr(1, vWindow, "Internet Explorer", vbTextCompare)) Then
             Set objLink = WshShell.CreateShortcut(vPath & vWindow.document.Title & ".url")
             objLink.TargetPath = vWindow.locationURL
             objLink.Save
         End If
    Next
    
    Set objShell = Nothing
    Set objWindows = Nothing
    Set WshShell = Nothing
    
    End Sub
     
  5. awsmitty

    awsmitty Thread Starter

    Joined:
    Nov 6, 2009
    Messages:
    51
    Rollin_Again, thanks again


    Your code works perfectly. I'm driving to Atlanta and I'm buying you a beer. Many thanks


    So far as your thoughts on "trigger a Windows Explorer type dialogue box to pop up that will allow you to pick the destination by clicking through the folder tree", I like that thought. I don't know how to do that, but it could be helpful.


    What's going on is I have a bunch, a bunch of media, music and movie, filed under the major actor, actresss, musician, band, etc. So, for example, Forrest Gump is in Tom Hanks, In Gary Sinise's folder is a shortcut to Forrest Gump also Sally Fields' folder. Where this piece of code comes into play is to store say the IMDB data on the movie, actors, etc via a shortcut to the web. So, I should know the tree, path. This workbook has hyperlinks to all of the folders. At one time it had hyperlinks to the web info and the music, movie, actors, musicians, etc, but the workbook got too darn big, huge, literally thousands of hyperlinks, took forever to load, but I have an old slow machine. The end result is a workbook that can create and change a playlist so easy, plus access to other web info that I do look up from time to time.


    Going back to your code, could you give me a brief explanation of the code, in particular the set statements, what they're doing. I would like to see your idea of a "Windows Explorer type dialogue box". It could be helpful here and in the future


    Again, thanks


    awsmitty
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Here is the modified version with the feature to use the folder tree dialogue to pick the save location (now you have to buy me two beers)

    As the code is written now it will open the folder tree from the root of the C: drive. If you want the folder tree to open from a different default location (e.g - C:\Program Files) you just need to change the path in red on the following line of code

    Set ShellApp = objShell.BrowseForFolder(0, "Please choose a folder", 0, "C:\")

    Code:
    Sub Create_ShortCut()
    
    Dim vPath As String
    Set objShell = CreateObject("Shell.Application")
    Set objWindows = objShell.Windows
    Set WshShell = CreateObject("WScript.Shell")  
        
    For Each vWindow In objWindows
         If (InStr(1, vWindow, "Internet Explorer", vbTextCompare)) Then
         Set ShellApp = objShell.BrowseForFolder(0, "Please choose a folder", 0, "C:\")
         vPath = ShellApp.self.Path & "\"
             Set objLink = WshShell.CreateShortcut(vPath & vWindow.document.Title & ".url")
             objLink.TargetPath = vWindow.locationURL
             objLink.Save
             
         End If
    Next
    
    Set objShell = Nothing
    Set objWindows = Nothing
    Set WshShell = Nothing
    
    End Sub
    
     
  7. awsmitty

    awsmitty Thread Starter

    Joined:
    Nov 6, 2009
    Messages:
    51
    Rollin_Again


    Make it three beers, four, make it a case!


    Early this morning I employed your code into the beginnings of this new workbook. It is so much faster than me manually, open this folder, move that file, paste here, find the correct web link, stick it in, etc. I'd say, estimating, 20-50X faster. It flies!! It's a thrill But when you're 60, weird, new and/or different can be thrilling.

    Again, many many thanks

    I want to give you another example. Years ago, early 70's, I was a high school office boy for a newspaper Company. My boss was a B. B. King nut, among other things (B. B. King just past away). He wanted me to take his albums and put them on 8-track, the big thing back then. I made me a copy too, of course. They have gone from LP, to 8-track to cassette, to cd, to hard drive. I have crap over 35 years old, and yes I still listen/watch to them. Sometimes ya get in a mood if ya know what I mean.


    Again, thanks, and I'm kinda serious about the beer! I think this is going to be so much better than my
    old workbook.

    awsmitty
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1148307

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice