Excel Training - paste as hyperlink

Hi everyone,

I just wanted to give you a heads up about Dunamis Training, LLC. We are dedicated solely to Excel/Access VBA programming. If you are interested in having a 2 day VBA class at your site or come to an offsite class, contact us. We are in the Pittsburgh area! Our instructors have decades of proven experience in VBA solutions at companies like FedEx and GM. We target business professionals, so you don't have to be a programming expert to participate.

Check out our web site: dunamistraining.com

If you have any questions, shoot them to us from our website "contact us", or call us. Thanks.

Chad Marinelli
Owner/Trainer
Dunamis Training, LLC
1-412-628-9464
chad@dunamistraining.com

I have a list of about 10,000 odd training records and I need to find, for each of the approx 300 course codes, the earliest and latest dates the course has been run.

Columns of data a

Course Code
Course Name
Start Date
Finish Date
User name

So for each of the 300 course codes, I need to get the earliest Start Date and the latest Finish Date.

I'm familiar with Max/Min functions, Named Ranges and how to find the unique Course Codes from the list. But not so sure about how to set up a formula to find the records for one course code against which I can apply the max/min function.

Any help would be appreciated.

Ward
in Perth

When I used Excel 2000, I used to be able to copy a cell, and then use the "Paste Hyperlink" command in the "Edit" menu to create a link to the target.

For the life of me, I cannot figure out how to do this in Excel 2010. If there's a way, can someone please show me?

(For the time being, I am using the inefficient method of clicking the hyperlink button on the "Insert" tab and manually navigating to the target.)

m

Using Excel 2002/2003 on XP-SP3. Have a spreadsheet with a column (B) of hyperlinks to online documentation (pdf files). I wanted to copy column(B) to D and convert HyperLink Address & TextToDisplay to local pdf file references, and preserve the orginal hyperlinks for future reference.

The code below works fine, except that when it changes the Address on the selected hyperlink (D), the source hyperlink address (B) changes as well. Strangely, the TextToDisplay does not (it works as expected).

Any chance someone can point out what I'm missing?


Sub HyperLinkChange()

Dim path, file As String
Dim h As Hyperlink
Dim x As Integer

Columns("B:B").Select
Selection.Copy
Columns("D:D").Select
ActiveSheet.Paste
Application.CutCopyMode = False

path = "http://www.abc.com/epubs/pdf/"
file = ""

For Each h In ActiveSheet.Columns("D:D").Hyperlinks
x = InStr(1, h.Address, path)
If x > 0 Then
file = Replace(h.Address, path, "")
h.Address = file ' This is the problem - it updates both hyperlinks
h.TextToDisplay = file
End If
Next
End Sub

Hi Everyone,

I regularly use this great piece of code to copy/paste data from a worksheet to an html email.
Code:

Sub Mail_Selection_Range_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
    Dim Rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    'Dim strbody As String
    Dim cell As Range
    Dim strto As String
    Dim strcc As String 'NEW
    
     
    For Each cell In ThisWorkbook.Sheets("Invite").Range("K11:K50") 'selecting delegates' email addresses
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
    
    'New
    For Each cell In ThisWorkbook.Sheets("Invite").Range("L12:L32") 'selecting supervisors' email addresses
        If cell.Value Like "?*@?*.?*" Then
            strcc = strcc & cell.Value & ";"
        End If
    Next cell
    If Len(strcc) > 0 Then strcc = Left(strcc, Len(strcc) - 1)
    
    'strbody = "Hi        " & "" & _
              "Can I place the following bookings please:" & "" & _
              "." & ""
    Application.Goto Reference:="Invite"
    Set Rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    Set Rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a range if you want
    'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .SentOnBehalfOfName = """Training Team"" "
        .To = strto
        '.CC = strcc 'new
        .BCC = ""
        .Subject = ThisWorkbook.Sheets("Invite").Range("B1").Value '& " - " & ThisWorkbook.Sheets("Invite").Range("C9").Value & " - Your Availability...please reply"
        .HTMLBody = strbody & RangetoHTML(Rng)
        '.VotingOptions = "I CAN ATTEND;I CANNOT ATTEND ON THIS DATE;PLEASE REMOVE ME FROM THE WAITLIST"
        .Importance = olImportanceHigh
        '.Attachments.Add "E:\profile\Desktop\Outlook Reminder for your course.ics"
        .Display   'or use .Send
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    'Dim MyData As DataObject
    'Set MyData = New DataObject
    'MyData.Clear '< This is not needed here, but I included it, as you may need it with a variation.
    'MyData.SetText ThisWorkbook.Sheets("Invite").Range("L1") 'ActiveCell.Value
    'MyData.PutInClipboard
    Sheets("Invite").Select
    Range("L1").Select
    
End Sub
Function RangetoHTML(Rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
 
    'Copy the range and create a new workbook to past the data in
    Rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    'Close TempWB
    TempWB.Close savechanges:=False
 
    'Delete the htm file we used in this function
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Is there any way to tweak it so that a web hyperlink is retained in the email? This code seems to strip the web link and just leaves ordinary text.

Thanks

I have a hyperlink to an external document on sheet 1. I want to use the past link function to link the hyperlink on sheet 1 to a cell in sheet 2. I select the paste special option / all formats / and paste link but the actual hyperlink to sheet 1 is not pasted into the cell on sheet 2. Only the "words" in the hyperlink are pasted.

Is there a way to paste a hyperlink in a sheet (that will be updated when the hyperlink is updated) to another sheet in the workbook?

Thanks for any help.

I am just beginning to learn how to make macros but I need help in a big way. what I need is a macro that will figure out how to schedule people for training based off their schedule and available training classes. I.e. i have a list of dates, times and number of people that can attend. I need something that would base the persons schedule on the available time and assign the class, then when it maxes the class out it moves to the next class. I know it is a lot to ask for but i am in desperate need for some help.

Can anyone recommend good free resources to help one understand the STRUCTURE of VBA coding? E.g. When and how to define variables, understanding the operators etc?

Thanks in advance!

Does anyone know where I can get training in Excel in NE UK?
--
Rob,
Teesside,
UK

Just curious if anyone ahs ever taken VBA training class and whether or not you'd recommend it.

The new employer is looking for some Excel VBA trainign for a small number of employees and looking for training centers (around Philadelphia if it matters). I'm not classically trained and I think much of what they are looking for will be related to auotmating tasks with Essbase but also some more complicated file processing in Excel outside Essbase.

I'm good with the normal everyday stuff, but curious if anyone has taken any outside training. They'd strongly prefer to go to a training place (Learning Tree, Lincoln Tech, etc) rahter than bringing someone in on-site.

Any recommendations/feedback on any training you've taken?