Add path and .xls to cell to produce filename

C

claytorm

HI

I've got a column full of filenames (around 100 at the moment), bu
without the .xls suffix.

I want to add a fixed path and the .xls suffix to each. I then plan o
using the resulting filename to pull required data from these files.

I've read elsewhere that INDIRECT would not be appropriate because i
only works on open files?

Is this procedure likely to take an age to run? Should I think abou
database solutions?

Thanks

Bertie
 
T

Tom Ogilvy

for each cell in Range("A1:A10")
cell.offset(0,1).Formula = "='C:\Myfolder\[" & trim(cell.value) &
".xls]Sheet1'!$A$1"
Next
 
S

Soo Cheon Jheong

Hi,

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

Dim RNG As Range
Dim CL As Range
Dim str_F As String
Dim str_P As String
Dim str_S As String
Dim str_C As String

Set RNG = Range("A1:A10")
str_P = "C:\Data\"
str_S = "Sheet1"
str_C = "A5"

Application.ScreenUpdating = False

For Each CL In RNG
str_F = Trim(CL.Value) & ".xls"
If Dir(str_P & str_F) <> "" Then
str_F = str_P & "[" & str_F & "]"
str_F = "='" & str_F & str_S & "'!" & str_C
CL(1, 2).Formula = str_F
End If
Next

RNG.Offset(0, 1).Value = RNG.Offset(0, 1).Value
Application.ScreenUpdating = True

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
Top