• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to link a file on multiple files on network

aamirsq

Member
What i am trying to achieve is create a file and link all files on network drive (as i have to copy manually), File names are always 01 Jan 2013, 01 Feb 2013,01 Mar 2013, ... so on & usually Data is only on Sheet1 Starting from A6 to O100.


Problem is sometimes other peoples have opened these files & i have 'readonly' access only.


Btw when i tried pasting the link i came up with this


{=Excel.Sheet.8|'192.100.6.180Sales3 Request For Quotations1 RECEIVED RFQs20131 Jan 2013 - RFQ Received.xls'!'!Nov 2012!R6C1'}


i was think...separate sheet for each month..


Regards
 
Modifying the code found here:

http://www.extendoffice.com/documents/excel/627-excel-list-files.html#a2


I created this macro to generate list of all files in a folder and hyperlink to them.

[pre]
Code:
Option Explicit
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:" '<<< Startup folder to begin searching from
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & ""
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & ""
xFname$ = Dir(xDirect$ & "*.xls*", 7) 'Find all XL files in that folder
Do While xFname$ <> ""
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(xRow), Address:= _
xDirect$ & xFname$ _
, TextToDisplay:=xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub
[/pre]
It's currently set to find all XL files, but you can modify that by changing the file extension being looked for, or remove it to list all files in the folder.


Is this the knd of linking you are looking for, or do you need a cell/formula link?
 
This results brings only the names of file but i was looking for to bring the fetch all files data on separate sheets, lets says If "1 jan 2013" (actually its whole month)then on sheet1 and if "1 feb 2013" then sheet2 etc btw data data is only from A6 to O100 is required.


I wanted above not pasted as values but as linked so if there is any change in any file it could be reflected in my file automatically.
 
Back
Top