W.E.B
asked on
Excel vba
Hello,
can you please help,
I'm using below code,
I need to save the created file as
1- sheet name
2- cell c4 name
Sub Save_Sheets_txt()
Dim fs As Object, a As Object, i As Integer, s As String
Set fs = CreateObject("Scripting.Fi leSystemOb ject")
Set a = fs.OpenTextFile("C:\Users\ Wassim\Des ktop\test. txt", True)
i = 2
While Not IsEmpty(Cells(i, 1))
s = s & "'" & Cells(i, 1) & "',"
i = i + 1
Wend
a.WriteLine s
a.Close
End Sub
thank you for any help,
can you please help,
I'm using below code,
I need to save the created file as
1- sheet name
2- cell c4 name
Sub Save_Sheets_txt()
Dim fs As Object, a As Object, i As Integer, s As String
Set fs = CreateObject("Scripting.Fi
Set a = fs.OpenTextFile("C:\Users\
i = 2
While Not IsEmpty(Cells(i, 1))
s = s & "'" & Cells(i, 1) & "',"
i = i + 1
Wend
a.WriteLine s
a.Close
End Sub
thank you for any help,
ASKER
I need the file created name as
the sheet name
or
cell c4
Example1
sheet1 = 58
so sheet created should be 58.txt
Example2
Cell c4 = Day
so sheet created should be day.txt
thanks
the sheet name
or
cell c4
Example1
sheet1 = 58
so sheet created should be 58.txt
Example2
Cell c4 = Day
so sheet created should be day.txt
thanks
If you are creating a new file use the the CreateTextFile method rather than the OpenTextFile method
http://msdn.microsoft.com/en-us/library/aa265018%28v=vs.60%29.aspx
As the name of the text file is a string this can be built at runtime eg
Michael
http://msdn.microsoft.com/en-us/library/aa265018%28v=vs.60%29.aspx
As the name of the text file is a string this can be built at runtime eg
Set a = fs.CreateTextFile("C:\Users\Wassim\Desktop\" & ActiveSheet.Name & "-" Range("C4"), True)
Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
slight correction to Michael's first code line - a missing & before the "Range"
However you said you want either Sheet name OR cell C4 - how do you decide which?
add the following before the file system object and replace <use sheetname> with the decision
If you use CreateTextFile as in this example you will always overwrite any existing file with the same name, if you use OpentextFile you can append records to an existing file or create a new one
However you said you want either Sheet name OR cell C4 - how do you decide which?
add the following before the file system object and replace <use sheetname> with the decision
dim fname as string
Fname = "C:\users\Wassim\Desktop\"
If <use sheetname> then
fname = fname & activesheet.name
Else
fname = fname & range("C4)
Endif
Set a = fs.CreateTextFile(fname, True)
If you use CreateTextFile as in this example you will always overwrite any existing file with the same name, if you use OpentextFile you can append records to an existing file or create a new one
ASKER
thank you.
this does it.
this does it.
Open in new window