First of all I rin this Macro: -

Sub ListFiles()
F = Dir("C:\*.XLS")
Do While Len(F) 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

This list the filenames in the directory.


Now I want this macro to copy the information from the file name i
cell A1.
I tried this but there was no luck: -

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/01/2004 by Ian
'

Workbooks.Open Filename:= (A1)
Range("A:B").Copy Destination:=Workbooks("Book1").Range("A:B")
MsgBox ("Completed Copying")
End Sub

ANY IDEA HOW TO MAKE THIS REFERENCE LOOK AT CELL A1 as a file name

--
Message posted from http://www.ExcelForum.com

Sub Macro1()
Dim sName as String
sName = Dir("C:\Data\*.xls")
do while sName < ""
Workbooks.Open Filename:=sName
set rng = Workbooks("Book1.xls"). _
Worksheets(1).Cells(1,256).End(xltoleft)
if not isempty(rng) then _
set rng = rng.offset(0,1)

ActiveWorkbook.columns("A:B").copy _
Destination:=rng
Activeworkbook.Close SaveChanges:=False
sName = dir()
Loop
End Sub


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/01/2004 by Ian
'

Workbooks.Open Filename:= Range("A1").Value
Range("A:B").Copy Destination:=Workbooks("Book1").Range("A:B")
MsgBox ("Completed Copying")
End Sub

--
Regards,
Tom Ogilvy

ianripping wrote in message
...
First of all I rin this Macro: -

Sub ListFiles()
F = Dir("C:\*.XLS")
Do While Len(F) 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

This list the filenames in the directory.


Now I want this macro to copy the information from the file name in
cell A1.
I tried this but there was no luck: -

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/01/2004 by Ian
'

Workbooks.Open Filename:= (A1)
Range("A:B").Copy Destination:=Workbooks("Book1").Range("A:B")
MsgBox ("Completed Copying")
End Sub

ANY IDEA HOW TO MAKE THIS REFERENCE LOOK AT CELL A1 as a file name?


---
Message posted from http://www.ExcelForum.com/