Creating .txt files based on cell contents

  • Thread starter Thread starter Matt Bennette
  • Start date Start date
M

Matt Bennette

I currently have a list of names and want to create a notes file for each
that I can hyperlink to. The hyper linking is fine. However I have over 300
files I need to create.

Any suggestions would be gratefully accepted

Many Thanks
 
Mat,

Right click your sheet tab, view code and paste the code below in.
Change MyPath to the directory you want the text files in. It will read down
column A and for every name it will create a text file using that name(If it
doesn't already exist) and create a hyperlink to it.

Sub Lime()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
MyPath = "C:\"
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
Fname = MyPath & c.Value & ".txt"
If Dir(Fname, vbNormal) = "" Then
Open Fname For Output As #1
Close #1
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=MyPath & c.Value & ".txt"
End If
Next
End Sub

Mike
 
Thanks dude that worked a treat. !!! Genius

Mike H said:
Mat,

Right click your sheet tab, view code and paste the code below in.
Change MyPath to the directory you want the text files in. It will read down
column A and for every name it will create a text file using that name(If it
doesn't already exist) and create a hyperlink to it.

Sub Lime()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
MyPath = "C:\"
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
Fname = MyPath & c.Value & ".txt"
If Dir(Fname, vbNormal) = "" Then
Open Fname For Output As #1
Close #1
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=MyPath & c.Value & ".txt"
End If
Next
End Sub

Mike
Click to expand...
 
If I might impose on your genius once more.

I have been trying to extract email addresses from word documents, about 1000.

Ideally the result would produce a list of filenames in column A and the
email address extracted from that file in column B.

Usually, there would only be 1 email address per file.

Again Many thanks
 
Back
Top