How to link to files

[Piotr]

Hi
I have many .xls files in one folder, they have different file names
but all of them have one worksheet called "Import", what I wish to do
is to make Excell import data from every .xls file in directory but
only the data which is placed in "Import" Sheet.
Is this possible ?

I couldnt find any other idea for importing data from many workbooks,
maybe you have some better ideas ?

regards
Peter

 

[Karen27]

Hi Peter,

You could create a new workbook call it 'Import.xls' say.

Then create as many sheets as you require (i..e one for each external
file), and within each of those sheets you create a formula like like
in cell 'A1' - =[Filename]Import!$A$1

Then relatavely copy the formula down and across as many rows and
columns needed in each of the sheets to cover all of the cells of each
import sheet in the external files.

You could even do the whole thing in the one if you wanted.

When you open up the dependant file, remember to update/refresh your
links to capture the updates in you supporting files.

That's one simple way, there are many others. You may wish to create a
simple import macro. You should be able to do most of it using the
macro recorder. That would be the best way.

Regards
Karen

 

[Macgru]

Hi
I have many .xls files in one folder, they have different file names
but all of them have one worksheet called "Import", what I wish to do
is to make Excell import data from every .xls file in directory but
only the data which is placed in "Import" Sheet.
Is this possible ?

I couldnt find any other idea for importing data from many workbooks,
maybe you have some better ideas ?

regards
Peter

try to modify below code:

Sub total()
Dim Name As String, Path As String, File As String

Path = "c:\" ' your path
Name = Dir(Path & "*.xls")

Application.DisplayAlerts = False
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="d:\total.xls" ' your destination

Do While File <> ""
Workbooks.Open Path + Name
File = Left(Name, Len(Name) - 4)
Sheets("import").Copy After:=Workbooks("total").Sheets(1)
ActiveSheet.Name = File
Workbooks(Name).Close
Name = Dir
Loop
Sheets(1).Delete
Application.DisplayAlerts = True
End Sub

mcg

 

[Piotr]

Thanks the idea is great but I have problem with following
Name = Dir(Path & "*.xls")
it doesnt add any value to variable Name, and the loop is skipped
Im working on it but I dont know if I will do it