Excel Macro that writes column 1 data into multiple text file names

This is a simple request... it can be a excel macro which would be the best - but a vb script could suffice.

I just want something to take a bunch of info from column a in a excel sheet.  Say 100 or so rows...  and turn it into text files that are named what each row is.

EXAMPLE:

COLUMN A
----------------------
peanut butter        |
----------------------
Jelly                      |
----------------------
Eggs                     |
----------------------
Waffles                |
----------------------

TURNED INTO FILES MADE IN DIR - C:\Documents and Settings\mookyjooky\Desktop\Col2txtfiles\

peanut butter.txt
Jelly.txt
Waffles.txt
Eggs.txt
mookyjookyAsked:
Who is Participating?
 
DaveCommented:
I've taken a slightly different approach then Sid

This is an Excel as your preference, which will automatically save to the preferred desktop path regardless of o/s. If the path doesn't exist it creates it

The code creates the text files with .txt extensions, having checked first that the cell is not blank

Cheers

Dave
Sub FileWrite()
    Dim rng1 As Range
    Dim X
    Dim objFSO
    Dim objTF
    Dim objWS
    Dim lrow As Long
    Dim strDeskTop
    Dim ObjFolder
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objWS = CreateObject("wscript.shell")
    strDeskTop = objWS.SpecialFolders("Desktop")
    On Error Resume Next
    Set ObjFolder = objFSO.getfolder(strDeskTop & "\Col2txtfile\")
    On Error GoTo 0
    If Len(ObjFolder) = 0 Then MkDir (strDeskTop & "\Col2txtfile")
    Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
    X = rng1
    For lrow = 1 To UBound(X)
        If Len(X(lrow, 1)) > 0 Then Set objTF = objFSO.createTextFile(strDeskTop & "\Col2txtfile\" & X(lrow, 1) & ".TXT")
    Next
End Sub

Open in new window

0
 
SiddharthRoutCommented:
TESTED AND TRIED

You may use this vbscript. Please do remember to amend it. For example supply the correct path, file name , sheet name and column Name

Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")

Const xlUp = -4162

oXL.DisplayAlerts = False
oXL.visible = true        
set wb = oXL.Workbooks.Open("C:\sample.xls")
set ws = wb.Sheets("Sheet1")

LastRow = ws.Range("A" & ws.Rows.Count).End(xlup).Row

for i = 1 to lastrow
     Set txtfile = FSO.OpenTextFile("C:\" & ws.Range("A" & i).Value, 8, True)
     txtfile.Close
     set txtfile = nothing
next 

wb.Close

oXL.DisplayAlerts = True

msgbox "Done"

Open in new window


To create a VBS file, copy the above code and paste it in notepad. Save the file with a .VBS extension. To run it, simply double click the vbs file.

Sid
0
 
BadotzCommented:
And neither solution cleans up after itself:

#1
Set oXL = Nothing
Set FSO = Nothing
set wb = Nothing
set ws = Nothing

#2
Set objFSO = Nothing
Set objWS = Nothing
Set ObjFolder = Nothing
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SiddharthRoutCommented:
Badotz: Yeah you are right. Thanks for pointing out ;)

UPDATED CODE

Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")

Const xlUp = -4162

oXL.DisplayAlerts = False
oXL.Visible = True
Set wb = oXL.Workbooks.Open("C:\sample.xls")
Set ws = wb.Sheets("Sheet1")

lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For i = 1 To lastrow
     Set txtfile = FSO.OpenTextFile("C:\" & ws.Range("A" & i).Value, 8, True)
     txtfile.Close
     Set txtfile = Nothing
Next

wb.Close

oXL.DisplayAlerts = True

Set ws = Nothing
Set wb = Nothing
oXL.Quit
Set oXL = Nothing
Set FSO = Nothing

MsgBox "Done"

Open in new window

0
 
DaveCommented:
>And neither solution cleans up after itself:

Cosmetic redundancy when code is on a procedural level.
0
 
SiddharthRoutCommented:
Cosmetic redundancy when code is on a procedural level.

Dave, you have successfully managed to bring a smile on my face once again!

Ah! that avatar! Not my eyes...

Sid
0
 
BadotzCommented:
>>Cosmetic redundancy when code is on a procedural level.

You can't teach an old dog new tricks ;-)
0
 
DaveCommented:
Is that me or you :)
0
 
BadotzCommented:
Oh, most definitely me - I'm a frakkin' dinosaur ;-)
0
 
mookyjookyAuthor Commented:
Thanks!  --- Also, if anyone is wondering what this is for... I copied all my todo lists into an excel and I'm uploading them into google docs to use in awesome note.  They need to be actual notes, therefore the need for .txt files.  Thanks guys!
0
 
SiddharthRoutCommented:
mookyjooky: Could you please tell me what was wrong with my code?

Sid
0
 
mookyjookyAuthor Commented:
I simply wasnt able to get SiddharthRout's answer to work, it could of been because of my general lack of understanding of the process.  Brett's worked instantanous.

I would be happy to say both worked, if thats the case.  I just wasnt able to.  Sorry!
0
 
DaveCommented:
While Sid did answer first I thought my code was better tailored to the question - otherwise I would not have posted.

- Asker did state a preference for macro over vbs
- Question had stated a particular destination on Desktop which my code derives regardless of OS version
- the Array approach and CreatetextFile is quicker than the loop

I think Sids code does work, but presumably the tailoring needed (input file etc) made it harder to implement.

Cheers

Dave


0
 
SiddharthRoutCommented:
1) The OP did say that he wanted a macro or a vbs so I gave vbs as vbs has the advantage that you manually don't have to open Excel to run the code.

2) My code had explanations on how the code needs to be run and if followed correctly as I mentioned it, I don't see a reason for the code to fail. In fact that is the reason why I also mentioned that it is "Tested and Tried"

3) mookyjooky: I have no qualms about you selecting Dave's answer. My only problem is that I had replied first and I expected a response from you whether the code worked or not. Please remember that I spent 'MY' private time to solve your query and it at least deserved an acknowledgment... You didn't even tell me that you were facing a problem in implementing my code. How would I have known what problems were u facing?

Unfortunately if Dave feels that his code is superior and my answer doesn't deserve to be even be an assist then I rest my case... :)

Sid
0
 
DaveCommented:
To be clear I didn't say you didn't deserve an assist - I have no qualms with that whatsover. And as per my earlier comment your code does indeed work - I had tested it as I was surprised you had used OpenTextfile and I wanted to ascertain if that wrote a new file name (and it does, althougth I note it needed a .txt extension to make a recognsiable file)

I think though that mookyjooky has given a decent explanation of why the question was closed as it is, ie the difficulty in applying the tailoring. Depending on experience
> For example supply the correct path, file name , sheet name and column Name
may be a slam dunk, or close to indecipherable.

So it was (2) that needed more explantion for implementation as opposed to it being a more subjective coding preference (a preference for macro over vbs was clearly stated) or code execution speed. The later differences being better grounds for sharing points

Again, I have no issue with a points split from here  (as Sids code does work). But I do think Sid - and this is in no way meant to sound condescending as we have all been where you are on this question - that you can take something from the way your answer assumed certain adjustments were straightforward.

Thx mookyjooky for replying so quickly

Cheers

Dave
0
 
SiddharthRoutCommented:
Dave, :)

It is not about points but about letting the expert know whether the suggestion worked or not which is in my opinion, a basic courtesy.

Sid
0
 
DaveCommented:

:)

But if the code wasn't readily in a condition where it could be run immediately than the Asker is never in position to make that call.

And given that I posted less than an hour after you and my code worked as is, there wasn't a feedback lag to note for mookyjooky to state "I can't get this working". When you have a problem solved, it's very uncommon to persist with a second approach. Especially when the solution that worked was in the preferred format

Cheers

Dave
0
 
SiddharthRoutCommented:
Dave

My point is, If ID: 34884642 had one more line like,

"Sid, I tried your suggestion but was unable to get it to work but brettdj's solution did what I wanted hence I am going with that answer"

I would have been OK with it. I was absolutely clueless as to why my answer was 'ignored'. And this is the basic courtesy that I was talking about.

Dave, If I had a question and you answered my query and within 30 mins another expert answered my query and I was 'OK' with that expert's answer, I will definitely let you know why I am choosing that expert's solution because I respect the fact that you took out the time to answer my query and you deserved an explanation of why I was not going with your answer :)

Sid
0
 
mookyjookyAuthor Commented:
This is my first question guys, and I appreciate all you help.  It's amazing that any of you would take the time to help total strangers with these questions.  The timing might of been an issue as well... and I can say this - I asked the question on Friday, and ran out of time to work on it before hanging out with my wife.  Then I got back to it 3 days later... and ran through all answers.  I'll be better about my etiquette next question.  ---  Everyone is cool now right?  =D
0
 
DaveCommented:
> Everyone is cool now right?

Yes :)

As per Sid's last comment, where multiple answers exist, an explanation for the answer you accept is helpful. In this case it made clear why you (rightly IMHO) closed the question as you did

In the Excel TA we have a well estblished etiquette for delaing with each other and Askers, Byundts (Brads) profile http://www.experts-exchange.com/M_1064321.html is well worth looking at.

The regulars in Excel respect and deal well with each other. In other zones you may find question closure causes more grief, hence it's always best to specify a closure reason if multiple people have posted and appear to have claims on points

Cheers

Dave


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.