Well... the short answer is "Yes and No".
First, what does this have to do with the title "Vlookup"??
Your question is WAY too vague.
There can BE no files called "c:week25", "c:week26".
they would have to be at a MINIMUM "C:\week25.xls", etc.
Also... are they all really in the root of C:\ ???
then... you say you want to create a "link"..
do you really mean a hyperlink? or something else?
If you mean a hyperlink, what do you want to "assign" it to?
the number you enter?
"assuming" lots of stuff you haven't shared, I created this change event:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fso, fname, WKno, BasePath
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If (Target.Value <> "") Then
BasePath = "C:\temp\"
WKno = Target.Value
If (Len(WKno) < 2) Then WKno = "0" & WKno
Set fso = CreateObject("Scripting.FileSystemObject")
fname = BasePath & "week" & WKno & ".xls"
If (fso.fileexists(fname)) Then
' MsgBox "file Exists" & Chr(13) & fname
Application.EnableEvents = False
ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address),
Address:=fname
Application.EnableEvents = True
End If
End If
End If
End Sub
It checks to see if the number you entered exists in the C:\temp\ folder and
the file will be called "week" & ## & .xls.
Then creates a hyperlink to the file.
hopefully, this will get you moving in the right direction.
Paul
________________________________
From: Jack <j...@jackcwood.co.uk>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Monday, June 15, 2009 2:14:02 PM
Subject: $$Excel-Macros$$ Vlookup
Hi I need to create a link to other workbooks on c: called week 25
week 26 etc based on a number in a cell ie ='c:week' & "A4" - A4
being the week number to add to file path. Can this be done ?
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
