Make cell contents

[jaypatel]

Hi,

i have a cell contents in cell b1 such as "A0039". What I require is for cell c1 to include the path and add ".xls" to it. So cell c1 should be "L:\Email Accounts\A0039.xls". Can this macro run from cells C1 to C65536.

Cheers

Jay

 

[al_b_cnu]

Hi

Cant you just put ="L:\Email Accounts\"&B1&".xls" into cell C1 & copy down?

HTH

Alan

 

[Joe4]

If you want to use a macro that captures the path of the current file, this should work:

Sub MyFileName()

    Application.ScreenUpdating = False
    
    Dim cell As Range
    For Each cell In Range("B1:B65536")
        If Len(cell) > 0 Then cell.Offset(0, 1) = ThisWorkbook.Path & "\" & cell & ".xls"
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub