• RSS
  • Twitter
  • FaceBook

Security Forums

Log in

FAQ | Search | Usergroups | Profile | Register | RSS | Posting Guidelines | Recent Posts

VBA- saving data to another excel woorkbook as hyperlink.

Users browsing this topic:0 Security Fans, 0 Stealth Security Fans
Registered Security Fans: None
Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Programming and More

View previous topic :: View next topic  
Author Message
mandriva
Just Arrived
Just Arrived


Joined: 17 Nov 2005
Posts: 1
Location: WAKEFIELD

Offline

PostPosted: Sat Feb 07, 2009 3:28 pm    Post subject: VBA- saving data to another excel woorkbook as hyperlink. Reply with quote

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.

thanks Shocked [/code]
Back to top
View user's profile Send private message Visit poster's website
mandriva
Just Arrived
Just Arrived


Joined: 17 Nov 2005
Posts: 1
Location: WAKEFIELD

Offline

PostPosted: Tue Feb 10, 2009 8:22 pm    Post subject: Reply with quote

Hi again,

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
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   

Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Programming and More All times are GMT + 2 Hours
Page 1 of 1


 
Jump to:  
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

Community Area

Log in | Register