Posted: Sat Feb 07, 2009 3:28 pm Post subject: VBA- saving data to another excel woorkbook as hyperlink.
Im hoping there are some VBA gurus out there....
My scenario is, I have 2 Workbooks issueslog.xls & links.xls
In the issueslog.xls I have created an issues log, when the file is saved it saves the document as the data entered in the range C9 which in this case is the issue title. All this works great.
What i am trying to achieve is when the issueslog.xls is saved it also saves a hyper link to itself in the links.xls workbook. I am not sure if this can be done and my VBA is limited.
I assume the vba will need to be tagged on to my code already used for saving the issueslog.xls. which is below.
If Range("C9") = "" Then
Msgbox "Please ensure you fill out the Issue Title. Thanks"
Saveactiveworkbook.saveas "C:\filename\filename\ & Range("C9")"
Msgbox " Thank you, your issue has now been saved."
Not your usual programming question but non the less a very puzzling one for me indeed.
well i have managed to put together the following code, but still not actually doing what i want tit to do.
It doesn't open the link.xls workbook and add the file name as a hyper link.
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"
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 & """ )"
MsgBox " Thank you, your issue has now been saved."
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Looking for more Windows Networking info?
Sign up to the WindowsNetworking.com Monthly Newsletter, written by Enterprise Security MVP Deb Shinder, containing news, the hottest tips, Networking links of the month and much more. Subscribe today and don't miss a thing! View a sample newsletter.
Become a WindowsNetworking.com member!
Discuss your Windows Networking issues with thousands of other Windows Newtorking experts. Click here to join!