Thread: Insert Hyperlink to WorkBook in which VBA Code resides

Hey crew,

I have this code that saves the newly created worksheet (from Code that imports text to excel) as a new workbook:

Private Sub SaveWS()
    Dim wb As Workbook
    If Application.Version > 11 Then
        ActiveWorkbook.Worksheets(cboRptname.Value).Copy
        Set wb = ActiveWorkbook
        wb.SaveAs Filename:="\\Server1\Folder1\Folder2\Folder3\" & BoxYrFolder.Value & "\" & BoxQtr.Value & "\Cart\" & _
            BoxMonthText.Value & "\" & cboRptname.Value & " " & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls", _
            FileFormat:=xlExcel8
             
    Else
    ActiveWorkbook.Worksheets(cboRptname.Value).Copy
        Set wb = ActiveWorkbook
        wb.SaveAs Filename:="\\Server1\Folder1\Folder2\Folder3\" & BoxYrFolder.Value & "\" & BoxQtr.Value & "\Cart\" & _
            BoxMonthText.Value & "\" & cboRptname.Value & " " & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls", _
            FileFormat:=xlWorkbookNormal
    End If
ActiveWorkbook.Close
End Sub

After this piece of code run (from a form), it goes back to the workbook in which the code resides and do other stuff (clears the sheet that just got converted to a brand new workbook, clears connections and querytables, etc, etc.).

After all the above is done, I would like to go to

Workbooks(ThisWorkbook.Name).Worksheets("Generator").Range("A7")

And add a hyper link to the file just created and saved by the code above. And I want the text to be displayed "cboRptname.Value & " " & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls"

Can anybody help?

EDIT: I tried the macro recorder, but I use xl07 and I want users on xl03 to use the file as well, and I'm always concerned with the way xl07 records code that it may not work on earlier versions.

Stab in the dark here, might get your far enough along to work it out exactly for yourself:

With Workbooks(ThisWorkbook.Name).Worksheets("Generator")
    .Hyperlinks.Add Anchor:=.Range("A7"), _
        Address:="\\Server1\Folder1\Folder2\Folder3\" _
            & BoxYrFolder.Value & "\" & BoxQtr.Value & "\Cart\" _
            & BoxMonthText.Value & "\" & cboRptname.Value & " " _
            & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls", _
        TextToDisplay:=cboRptname.Value & " " & cboYear.Value _
            & cboMonth.Value & cboDay.Value & ".xls"
End With

Good stabbing JB, I was trying a to modify my recorded macro, and the only thing different that I had was that I selected A7 first and then on the hyperlink syntax, after Anchor I stated .Selection. Your method is faster.

I put it along with my other procedures and it does the trick.

Thanks,

Ron,

The speed cost of using a SELECT method is always huge. I recommend you never do that, always bring your code here and let us help you devise a non-select approach, the speed result is worth the extra work.

Most efficient method will always be to address your commands directly to the sheet cells using fully resolved addresses, that way it never matters what is "active" in terms of workbooks, worksheets, the command still executes directly to where you indicated.

Notice how I used the WITH method to add that full parent at the start and then attach it to all the commands that occur in the WITH section?