Is it possible to make an external file from the contents of a cell? For
example if I have "Text acbd" as the contents of cell A1, can I write it to a
new file called Text1.txt?
If so, can the file name Text1.txt be in another cell, B1 for example?
So, I've got A1 = "Text abcd", B1 = Text1.txt, is there a command or VB
routine that I can use to make a file called Txt1.txt with the contents "Text
abcd"?

Thanks,
Ian

The simple answer to your question is that you need to write VBA code to do
what you describe. You'll want to consider things like identifying the
cell(s) you want to have involved in the process, whether the cell with the
filename will also provide the path, etc.




"BigIan" wrote:

Is it possible to make an external file from the contents of a cell? For
example if I have "Text acbd" as the contents of cell A1, can I write it to a
new file called Text1.txt?
If so, can the file name Text1.txt be in another cell, B1 for example?
So, I've got A1 = "Text abcd", B1 = Text1.txt, is there a command or VB
routine that I can use to make a file called Txt1.txt with the contents "Text
abcd"?

Thanks,
Ian

Substitute "pathname" with your real pathname!

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"pathname" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub


Regards,
Stefi

€žBigIan€� ezt Ã*rta:

Is it possible to make an external file from the contents of a cell? For
example if I have "Text acbd" as the contents of cell A1, can I write it to a
new file called Text1.txt?
If so, can the file name Text1.txt be in another cell, B1 for example?
So, I've got A1 = "Text abcd", B1 = Text1.txt, is there a command or VB
routine that I can use to make a file called Txt1.txt with the contents "Text
abcd"?

Thanks,
Ian

Thanks for your help Stefi, I can't get it to work though. I get a 400 error
when I run it.
I'm not very well up in VB so this is just a guess, but does your code just
define the filename? I see pathname + filename from cell B1 but I don't see
where the cell content of A1 goes.
What I've done so far is to copy your code exactly on to a VB code box and
replace "pathname" with "c:\" (I left the quotation marks in) so it looks
like this:

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"c:\" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub

Then I ran it as a macro. Do I need to change or add something else?

Thanks,
Ian

"Stefi" wrote:

Substitute "pathname" with your real pathname!

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"pathname" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub


Regards,
Stefi

€žBigIan€� ezt Ã*rta:

Is it possible to make an external file from the contents of a cell? For
example if I have "Text acbd" as the contents of cell A1, can I write it to a
new file called Text1.txt?
If so, can the file name Text1.txt be in another cell, B1 for example?
So, I've got A1 = "Text abcd", B1 = Text1.txt, is there a command or VB
routine that I can use to make a file called Txt1.txt with the contents "Text
abcd"?

Thanks,
Ian

What version do you use? I tested it in XL2000 and it worked!
Nonetheless try this:

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"c:\" & Range("B1"), FileFormat:=xlTextWindows, _
CreateBackup:=False
End Sub

Regards,
Stefi

€žBigIan€� ezt Ã*rta:

Thanks for your help Stefi, I can't get it to work though. I get a 400 error
when I run it.
I'm not very well up in VB so this is just a guess, but does your code just
define the filename? I see pathname + filename from cell B1 but I don't see
where the cell content of A1 goes.
What I've done so far is to copy your code exactly on to a VB code box and
replace "pathname" with "c:\" (I left the quotation marks in) so it looks
like this:

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"c:\" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub

Then I ran it as a macro. Do I need to change or add something else?

Thanks,
Ian

"Stefi" wrote:

Substitute "pathname" with your real pathname!

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"pathname" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub


Regards,
Stefi

€žBigIan€� ezt Ã*rta:

Is it possible to make an external file from the contents of a cell? For
example if I have "Text acbd" as the contents of cell A1, can I write it to a
new file called Text1.txt?
If so, can the file name Text1.txt be in another cell, B1 for example?
So, I've got A1 = "Text abcd", B1 = Text1.txt, is there a command or VB
routine that I can use to make a file called Txt1.txt with the contents "Text
abcd"?

Thanks,
Ian

Her code simply saves the active sheet as a text file.

Try this instead

Sub WriteCellToFile()
Dim fso As Object
Dim ts As Object
Dim strCellContents As String
Dim strFileName As String
Dim strPath As String

strCellContents = Range("A1").Text
strFileName = Range("B1").Text
strPath = Range("C1").Text

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile(strPath & strFileName, True)
ts.writeline (strCellContents)
ts.Close

End Sub

Change this line:

Set ts = fso.CreateTextFile(strPath & strFileName, True)

to

Set ts = fso.CreateTextFile(strPath & strFileName, False)

if you want to be sure to NOT OVERWRITE existing files

Hi Stefi,

It worked! I think the problem was that I hadn't set up the module bit in VB
correctly, your code was fine.

Thanks for your help,
Ian


"Stefi" wrote:

What version do you use? I tested it in XL2000 and it worked!
Nonetheless try this:

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"c:\" & Range("B1"), FileFormat:=xlTextWindows, _
CreateBackup:=False
End Sub

Regards,
Stefi

€žBigIan€� ezt Ã*rta:

Thanks for your help Stefi, I can't get it to work though. I get a 400 error
when I run it.
I'm not very well up in VB so this is just a guess, but does your code just
define the filename? I see pathname + filename from cell B1 but I don't see
where the cell content of A1 goes.
What I've done so far is to copy your code exactly on to a VB code box and
replace "pathname" with "c:\" (I left the quotation marks in) so it looks
like this:

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"c:\" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub

Then I ran it as a macro. Do I need to change or add something else?

Thanks,
Ian

"Stefi" wrote:

Substitute "pathname" with your real pathname!

Sub txtsave()
ActiveWorkbook.ActiveSheet.SaveAs Filename:= _
"pathname" & Range("B1"), FileFormat:=xlText, _
CreateBackup:=False
End Sub


Regards,
Stefi

€žBigIan€� ezt Ã*rta:

Is it possible to make an external file from the contents of a cell? For
example if I have "Text acbd" as the contents of cell A1, can I write it to a
new file called Text1.txt?
If so, can the file name Text1.txt be in another cell, B1 for example?
So, I've got A1 = "Text abcd", B1 = Text1.txt, is there a command or VB
routine that I can use to make a file called Txt1.txt with the contents "Text
abcd"?

Thanks,
Ian

That's perfect Duke, just what I needed.

Thanks a lot,
Ian

"Duke Carey" wrote:

Her code simply saves the active sheet as a text file.

Try this instead

Sub WriteCellToFile()
Dim fso As Object
Dim ts As Object
Dim strCellContents As String
Dim strFileName As String
Dim strPath As String

strCellContents = Range("A1").Text
strFileName = Range("B1").Text
strPath = Range("C1").Text

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile(strPath & strFileName, True)
ts.writeline (strCellContents)
ts.Close

End Sub

Change this line:

Set ts = fso.CreateTextFile(strPath & strFileName, True)

to

Set ts = fso.CreateTextFile(strPath & strFileName, False)

if you want to be sure to NOT OVERWRITE existing files