Solved

Excel vba

Posted on 2014-02-21
6
337 Views
Last Modified: 2014-02-21
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,
0
Comment
Question by:W.E.B
  • 2
  • 2
  • 2
6 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39878480
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
 

Author Comment

by:W.E.B
ID: 39878493
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
 
LVL 23

Expert Comment

by:Michael74
ID: 39878494
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 23

Accepted Solution

by:
Michael74 earned 300 total points
ID: 39878496
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39878507
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
 

Author Closing Comment

by:W.E.B
ID: 39878510
thank you.
this does it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now