Posted: Sat Feb 07, 2009 3:28 pm Post subject: VBA- saving data to another excel woorkbook as hyperlink.
Hi
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.
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
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.
any ideas?
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
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
Featured Links*
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!