Solved

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

Posted on 2011-02-11
21
341 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:mookyjooky
  • 7
  • 7
  • 3
  • +1
21 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875869
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
 
LVL 50

Accepted Solution

by:
Dave Brett earned 125 total points
ID: 34876130
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
 
LVL 29

Expert Comment

by:Badotz
ID: 34877986
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34878037
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34878039
>And neither solution cleans up after itself:

Cosmetic redundancy when code is on a procedural level.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34878047
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
 
LVL 29

Expert Comment

by:Badotz
ID: 34878049
>>Cosmetic redundancy when code is on a procedural level.

You can't teach an old dog new tricks ;-)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34878069
Is that me or you :)
0
 
LVL 29

Expert Comment

by:Badotz
ID: 34878085
Oh, most definitely me - I'm a frakkin' dinosaur ;-)
0
 

Author Closing Comment

by:mookyjooky
ID: 34884642
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34884658
mookyjooky: Could you please tell me what was wrong with my code?

Sid
0
 

Author Comment

by:mookyjooky
ID: 34901314
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34901783
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34903177
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34903240
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34903289
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34903308

:)

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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34903358
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
 

Author Comment

by:mookyjooky
ID: 34906044
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34911882
> 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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

705 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

18 Experts available now in Live!

Get 1:1 Help Now