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.FileSystemObject")
   Set a = fs.OpenTextFile("C:\Users\Wassim\Desktop\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,
W.E.BAsked:
Who is Participating?
 
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
For your requirements this could be
   Set a = fs.CreateTextFile("C:\Users\Wassim\Desktop\" & ActiveSheet.Name, True)                   

Open in new window


or

   Set a = fs.CreateTextFile("C:\Users\Wassim\Desktop\" & Range("C4"), True)

Open in new window

0
 
regmigrantCommented:
I'm not entirely sure what your problem is but the following changes will create or open the textfile in append mode - note the '8' added to the opentextfile to indicate append

Sub Save_Sheets_txt()
Dim i As Integer, s As String
    Set fs = CreateObject("Scripting.FileSystemObject")

    Set a = fs.OpenTextFile("C:\Users\wassim\Desktop\testfile.txt", 8, 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

Open in new window

0
 
W.E.BAuthor Commented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Michael FowlerSolutions ConsultantCommented:
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

   Set a = fs.CreateTextFile("C:\Users\Wassim\Desktop\" & ActiveSheet.Name & "-" Range("C4"), True)

Open in new window


Michael
0
 
regmigrantCommented:
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

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)

Open in new window


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
0
 
W.E.BAuthor Commented:
thank you.
this does it.
0
All Courses

From novice to tech pro — start learning today.