Hi
oh this is gr8
thank u
regards
ruchi
On Wed, Jun 17, 2009 at 5:17 PM, Dilip Pandey<dilipan...@gmail.com> wrote:
> Hi Ruchi,
>
> This query is related to the "linking of the files onto a excel
> spreadsheet".
> For example, you have some week - wise spreadsheets in your c drive (e.g.
> C:\Ruchi\work). The files may have the names like (week4.xls, week5.xls,
> week6.xls) and so on. Now what you want to do, is to link up certain range
> or a cell from each of these files to a master spreadsheet.
> (MasterSales.xls). And, In Master file, you want to pick up M50 of each
> week's file, So the query is to have all the files linked to master file by
> changing some cell reference or some formulas in master file SO THAT when
> you change the week number in master file, respective week's file get linked
> up and result is obtained in the master spreadsheet.
>
> Thanks,
> Dilipandey
> --
> DILIP KUMAR PANDEY
> MBA-HR,B COM(Hons.),BCA
> Mobile: +91 9810929744
> dilipan...@gmail.com
> dilipan...@yahoo.com
> New Delhi - 110062
>
>
> On Tue, Jun 16, 2009 at 11:08 PM, god is gr8 i love u
> <ruchigab...@gmail.com> wrote:
>>
>> Hi Dilip
>>
>> can u plz help me to understand this query?
>> plz elaborate it
>>
>> thanks
>>
>> regards
>> ruchi
>>
>>
>>
>>
>>
>> On Tue, Jun 16, 2009 at 10:51 PM, Paul Schreiner<schreiner_p...@att.net>
>> wrote:
>> > 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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
