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.

automatically populate saved excel document as a hyper link in another workbook?

Discussion in 'Business Applications' started by deb0and, Feb 7, 2009.

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

    deb0and Thread Starter

    Joined:
    Oct 17, 2007
    Messages:
    53
    Hi

    I was wondering if the above can be achieved?


    My scenario is I have an issues log and the file name is saved as the issue title is there a way to get the saved file name into another workbook appearing as a hyper link back to the original?

    I am assuming VBA will be needed and added on to the code below which i already have, the code takes the value of C9 (the issue title) and saves it in the right folder.

    Code:
    If Range("C9") = "" Then
    Msgbox "Please ensure you fill out the Issue Title. Thanks"
    Else
    Saveactiveworkbook.saveas "C:\filename\filename\ & Range("C9")"
    Msgbox " Thank you, your issue has now been saved."
    Endif
    
     
  2. Sponsor

  3. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    The code above renames the current workbook to a name saved in cell C9, is this what you want to do?
     
  4. deb0and

    deb0and Thread Starter

    Joined:
    Oct 17, 2007
    Messages:
    53
    Yeah below is the code i have which, like you say renames the filename what ever is in C9, i then want the code to go further and open another workbook located in the same file and add the file name previously saved as a hyper link in the file just opened. I think that makes sense.:D

    I have two files save1234.xls which is my issues log which people fill out, then they save the log by pressing a save button which runs the following code, but it doesnt work properly yet

    Code:
    Private Sub CommandButton1_Click()
    Dim path As String, file As String
    Dim x As Long
    path = "C:\Users\Deb0And\Documents\practice\"
    file = Workbooks("save1234.xlsm").Sheets("Sheet1").Cells(9, 3).Value
    If file = "" Then
    MsgBox "Please ensure you fill out the Issue Title. Thanks"
    Else
    ActiveWorkbook.SaveAs "C:\Users\Deb0And\Documents\practice\" & Range("c9")
    Workbooks.Open Filename:="C:\Users\Deb0And\Documents\practice\" & "link.xls"
    x = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A" & x + 1).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=path & file & "link.xls"
    'Cells(x + 1, 3) = "=hyperlink(A" & x + 1 & "B" & x + 1 & ".xls)"
    ActiveWorkbook.Close True
    MsgBox " Thank you, your issue has now been saved."
    End If
    End Sub
    
     
  5. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    One line that stands out from what you've said is this one...

    Code:
    Workbooks.Open Filename:="C:\Users\Deb0And\Documents\practice\" & "link.xls"
    
    The Name of the workbook that the code is looking for is called 'link.xls'

    Unless this is correct, you'll need to pass additional info for the correct book to be opened.
     
  6. deb0and

    deb0and Thread Starter

    Joined:
    Oct 17, 2007
    Messages:
    53
    mmm, i think i have got my self a little confused here:eek:

    I will try to explain it a bit better, hopefully;)

    In a folder i have two excel spreadsheets, the first called save1234.xls and the second called link.xls.

    The save1234 has the save button containing the code previously submitted which when clicked is supposed to do 2 things

    1) Save - save1234 as a different work book by using whatever is typed in C9 and using that as the new file name.

    2) after the file has been saved it should open the workbook link.xls and insert the new file name as a hyper-link back to the originally saved work book that was typed in C9.


    so for example i open save1234.xls type TEST in range C9 and use my 'save button' using the code i will now have three workbooks in the same file. save1234 - the original
    TEST.xls the one just saved and link.xls.

    when i open link.xls there should be a hyper link in cell A1 called TEST which i can click on and hey presto be back in the TEST.xls workbook.

    blimey hopefully that explains it better
     
  7. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I think I got you now, try this.

    I presume you're on 2007 from the xlsm, but I think this should work on other versions as I've adjusted the code to pick up the name of your orignal save1234 (regardsless of file extension) - which also serves to enable you to run the code on offspring books.

    Code:
    Private Sub CommandButton1_Click()
    Dim PATH As String, File, THISbk As String
    Dim x As Long
    THISbk = ActiveWorkbook.Name
    PATH = "C:\Users\Deb0And\Documents\practice\"
    File = Workbooks(THISbk).Sheets("Sheet1").Cells(9, 3).Value & ".xlsm"
    If File = "" Then
    MsgBox "Please ensure you fill out the Issue Title. Thanks"
    Else
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs PATH & Range("c9")
    Application.DisplayAlerts = True
    Workbooks.Open Filename:=PATH & "link.xls"
    x = Cells(Rows.Count, 1).End(xlUp).Row
     Range("A" & x + 1).FormulaR1C1 = _
            "=HYPERLINK(""" & PATH & File & """,""" & PATH & File & """ )"
    ActiveWorkbook.Close True
    MsgBox " Thank you, your issue has now been saved."
    End If
    End Sub
    
     
  8. deb0and

    deb0and Thread Starter

    Joined:
    Oct 17, 2007
    Messages:
    53
    thanks the changes have made the code run much quicker and smother, however
    the code doesn't open the workbook link.xls? It adds the hyper link to the existing document?

    Thanks for your help
     
  9. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I've tested it again with no problems, but have made a modification for the code to explicitly select the workbook "link.xls" to make sure.

    Code:
    Private Sub CommandButton1_Click()
    Dim PATH As String, File, THISbk As String
    Dim x As Long
    THISbk = ActiveWorkbook.Name
    PATH = "C:\Users\Deb0And\Documents\practice\"
    File = Workbooks(THISbk).Sheets("Sheet1").Cells(9, 3).Value & ".xlsm"
    If File = "" Then
    MsgBox "Please ensure you fill out the Issue Title. Thanks"
    Else
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs PATH & Range("c9")
    Workbooks.Open Filename:=PATH & "link.xls"
    Workbooks("link.xls").Activate
    x = Cells(Rows.Count, 1).End(xlUp).Row
     Range("A" & x + 1).FormulaR1C1 = _
            "=HYPERLINK(""" & PATH & File & """,""" & PATH & File & """ )"
    ActiveWorkbook.Close True
    Application.DisplayAlerts = True
    MsgBox " Thank you, your issue has now been saved."
    End If
    End Sub
    
     
  10. deb0and

    deb0and Thread Starter

    Joined:
    Oct 17, 2007
    Messages:
    53
    thanks, i have had to do jig some code around as it still didnt work , but it works great now :D Thanks for pointing me in the direction.

    here is the code i am using

    Code:
    Sub Button3_Click()
    Dim PATH As String, File, THISbk As String
    Dim x As Long
    THISbk = ActiveWorkbook.Name
    PATH = "C:\Users\Deb0And\Documents\practice\"
    File = Workbooks(THISbk).Sheets("Sheet1").Cells(9, 3).Value & ".xlsm"
    If File = "" Then
    MsgBox "Please ensure you fill out the Issue Title. Thanks"
    Else
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs PATH & Range("c9")
    Application.DisplayAlerts = True
    Workbooks.Open "C:\Users\Deb0And\Documents\practice\link.xls"
    With Workbooks("link.xls").ActiveSheet
        .Activate
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A" & x + 1).FormulaR1C1 = _
            "=HYPERLINK(""" & PATH & File & """,""" & PATH & File & """ )"
    End With
    ActiveWorkbook.Close True  'Not sure about that line now
    MsgBox " Thank you, your issue has now been saved."
    End If
    End Sub
    
     
  11. 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/798351

  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