Excel:replace filename in cell with the file's contents 1

[traceyr]

Hi all,
I have a spreadsheet with several columns containing the names of text files (all of which are in subdirectories of the current directory, e.g. spreadsheet in \A1\A2\, files in A1\A2\A3\).
Some cells are empty; if there is an entry, it is a valid filename.
The columns are currently 401 rows long.
Ideally I would like to replace each filename with the contents of that file. If that is not possible, then I would like to replace the filename with a hyperlink to that file.
Is this too ambitious for a newbie?
Thanks for your time.

 

[TonyJollans]

Hi traceyr,

I am not entirely sure what you mean when you say the columns are 401 rows long, but I assume you have a range of cells each with a filename.

Depending a bit on the nature of the text in your text files, you can read each in turn and pull in the text. How big are they? Is all the text on one line?

To change names to hyperlinks is fairly simple ..

[blue]For Each C in Range("[i]A1:X401[/i]")
    If C.Text <> "" Then
        ActiveSheet.Hyperlinks.Add Anchor:=C, Address:=C.Text
    End If
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[traceyr]

Thanks Tony, that works a treat. Many thanks.
To answer your questions:
(1) The columns currently have 401 cells each (e.g. title in A1, filenames in A2:A401), but that can vary.
(2) The files are from 1 line to approx. 20 (they are small scripts associated with AssetCenter parameters).
The aim is to document the parameter settings; placing the scripts in the cells themselves would be a lot tidier than having potentially hundreds of small files associated with the spreadsheet.
Thnaks

 

[TonyJollans]

Hi Tracey,

Try this to include the text in the Worksheet.

[blue]For Each C In Range("[i]A1:X401[/i]")
    Open C.Text For Input As #1
    Data = ""
    Do While Not EOF(1)
        Input #1, tony
        Data = Data & vbLf & tony
    Loop
    Close #1
    C.Value = Mid(Data, 2)
Next
[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[traceyr]

Tony,
That's just what I was looking for. Perfect!
Thanks very much indeed.
Tracey

 

[traceyr]

Tony,

Thanks again for your help. Here is one change I made to avoid an unpleasant 'spurious line feed' problem, which messed up the format of the scripts and removed quotes, commas etc according to its own arcane rules.

Changing 'Input' to 'Line Input' removed the problem:

FPath = <path>
For Each C In Range("j2:j140")
If InStr(1, C.Text, "\") > 0 Then
Fname = FPath & C.Text
Open Fname For Input As #1
Data = ""
Do While Not EOF(1)
Line Input #1, Zeile
Data = Data & vbLf & Zeile
Loop
Close #1
C.Value = Mid(Data, 2)
End If
Next