Hi everybody,
I have an Excel sheet with a long list of filenames in column A (filenames include full pathname and all files are .xls files).
I want column B to show the info in cell C3 of Sheet 1 of each .xls file listed in Column A.
I want to do this either with a macro or just a formula in the cells.
The filenames are themselves the result of a macro and so they will change everyday.
Can anybody help me?
Thanks!
Martin.
Last edited by martin_b; 03-18-2005 at 06:52 AM.
I would do it with a ocmbination of a formula and a macro.
The formula would be something like this: (in column B)
=INDIRECT(CONCATENATE(A1,"Sheet1!$C$3"))
And then let a macro open the file in column A, copy the formula as value and close the file again. Like this:
pathname = "C:\"
While ActiveCell.Value <> ""
Range("A" & ActiveCell.Row).Select
Workbooks.Open Filename:=pathname & file, UpdateLinks:=0
Range("B" & ActiveCell.Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Windows(file).Activate
ActiveWorkbook.Close
ActiveCell.Offset(1, 0).Select
Wend
I haven't tested this, so there may be errors in it, but you'll get the idea.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks