Create txt file for each cell in a range

neeraj_chow

Board Regular
Joined
Aug 1, 2003
Messages
62
I am using excel 2003. I want a VBA code which when run would ask me range of cells as input to the function/proc and create seperate text files for values placed in each cell of the range. i.e. if the range has 10 cells, then there would be 10 files created with cell ref as filename eg filenames could be a1, a2, a3 etc

Require assistance on this one

Regards
Neeraj
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

sumuwin

New Member
Joined
Sep 8, 2009
Messages
38
Hi

seems a strange request but here's some code that should do it. Change 'mypath" to the folder you want to save the files.


Code:
Public Sub makeCellFile()
    
Dim myRange As Range
Dim mycell As Range
Dim mypath As String
mypath = "C:\Test\"
    
Set myRange = Application.InputBox("Select Range", "Create Files in Range", Type:=8)
     
For Each mycell In myRange
        
    myfile = Replace(mycell.Address, "$", "") & ".txt"
    myfile = mypath & myfile
    FileNum = FreeFile
        
    Open myfile For Output As FileNum
    Print #FileNum, mycell
    Close FileNum
        
Next
    
MsgBox "Files created"

End Sub

atb
sumuwin
 

neeraj_chow

Board Regular
Joined
Aug 1, 2003
Messages
62
The request was indeed strange, but thanks dude, that was exactly what I was looking for ;)
 

kev6264

New Member
Joined
Jun 16, 2006
Messages
44
Question...

How can I modify this vba to use the text contained in a cell as the .txt filename?

Public Sub makeCellFile()

Dim myRange As Range
Dim mycell As Range
Dim mypath As String
mypath = "C:\Misc"

Set myRange = Application.InputBox("Select Range", "Create Files in Range", Type:=8)

For Each mycell In myRange

myfile = Replace(mycell.Address, "$", "") & ".txt"
myfile = mypath & myfile
FileNum = FreeFile

Open myfile For Output As FileNum
Print #FileNum, mycell
Close FileNum

Next

MsgBox "Files created"

End Sub
 

TheLazyBone

New Member
Joined
Oct 28, 2010
Messages
7
Change this:

Code:
myfile = Replace(mycell.Address, "$", "") & ".txt"

To this:

Code:
myfile = mycell.value & ".txt"
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,891
Messages
5,514,004
Members
408,980
Latest member
Naomi_

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top