+ Reply to Thread
Results 1 to 3 of 3

Help with Macro...

  1. #1
    tcg_gilbert
    Guest

    Help with Macro...

    I'm trying to pull some text out of a lengthy excel document to do some text
    mining on. I'd like to have a macro (or another solution that would work) to
    pull the headers out of each line (record) and save each line as a text file.
    Any help would be greatly appreciated!
    Thanks

    So, if I had an excel (.csv file) file that captured email content (someone
    in the past did this manually) like this:

    Subject,Body,Date
    Lunch Plans,Do you have lunch plans?,4/5/2006
    What's Up?,What are you up to today?,4/5/2006

    Would be converted to 2 .txt files like this:

    Text File 1:
    Subject:
    Lunch Plans

    Body:
    Do you have Lunch Plans?

    Date:
    4/5/2006

    Text File 2:
    Subject:
    What's Up?

    Body:
    What are you up to today?

    Date:
    4/5/2006




  2. #2
    Steve Yandl
    Guest

    Re: Help with Macro...

    If this is really a lengthy Excel document, are you sure you want all the
    text files?

    The sub below should do what you want.......Steve Yandl



    Sub MakeTxtFiles()

    Const ForWriting = 2

    Dim rngBase As Range
    Dim rowCount As Integer
    Dim fNum As Integer

    Set rngBase = ActiveSheet.UsedRange
    rowCount = rngBase.Rows.Count

    Set fso = CreateObject("Scripting.FileSystemObject")

    If Not fso.FolderExists("C:\Notes") Then
    fso.CreateFolder ("C:\Notes")
    End If

    For fNum = 1 To (rowCount - 1)
    Set newNote = fso.OpenTextFile("C:\Notes\Text File " _
    & CStr(fNum) & ".txt", ForWriting, True, 0)
    newNote.WriteLine "Subject:"
    newNote.WriteLine Cells(fNum + 1, 1).Value
    newNote.WriteLine ""
    newNote.WriteLine "Body:"
    newNote.WriteLine Cells(fNum + 1, 2).Value
    newNote.WriteLine ""
    newNote.WriteLine "Date:"
    newNote.WriteLine Cells(fNum + 1, 3).Value
    newNote.Close
    Next fNum

    Set fso = Nothing

    End Sub



    "tcg_gilbert" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to pull some text out of a lengthy excel document to do some
    > text
    > mining on. I'd like to have a macro (or another solution that would work)
    > to
    > pull the headers out of each line (record) and save each line as a text
    > file.
    > Any help would be greatly appreciated!
    > Thanks
    >
    > So, if I had an excel (.csv file) file that captured email content
    > (someone
    > in the past did this manually) like this:
    >
    > Subject,Body,Date
    > Lunch Plans,Do you have lunch plans?,4/5/2006
    > What's Up?,What are you up to today?,4/5/2006
    >
    > Would be converted to 2 .txt files like this:
    >
    > Text File 1:
    > Subject:
    > Lunch Plans
    >
    > Body:
    > Do you have Lunch Plans?
    >
    > Date:
    > 4/5/2006
    >
    > Text File 2:
    > Subject:
    > What's Up?
    >
    > Body:
    > What are you up to today?
    >
    > Date:
    > 4/5/2006
    >
    >
    >




  3. #3
    tcg_gilbert
    Guest

    Re: Help with Macro...

    I'll give this a shot! Thanks a bunch!

    "Steve Yandl" wrote:

    > If this is really a lengthy Excel document, are you sure you want all the
    > text files?
    >
    > The sub below should do what you want.......Steve Yandl
    >
    >
    >
    > Sub MakeTxtFiles()
    >
    > Const ForWriting = 2
    >
    > Dim rngBase As Range
    > Dim rowCount As Integer
    > Dim fNum As Integer
    >
    > Set rngBase = ActiveSheet.UsedRange
    > rowCount = rngBase.Rows.Count
    >
    > Set fso = CreateObject("Scripting.FileSystemObject")
    >
    > If Not fso.FolderExists("C:\Notes") Then
    > fso.CreateFolder ("C:\Notes")
    > End If
    >
    > For fNum = 1 To (rowCount - 1)
    > Set newNote = fso.OpenTextFile("C:\Notes\Text File " _
    > & CStr(fNum) & ".txt", ForWriting, True, 0)
    > newNote.WriteLine "Subject:"
    > newNote.WriteLine Cells(fNum + 1, 1).Value
    > newNote.WriteLine ""
    > newNote.WriteLine "Body:"
    > newNote.WriteLine Cells(fNum + 1, 2).Value
    > newNote.WriteLine ""
    > newNote.WriteLine "Date:"
    > newNote.WriteLine Cells(fNum + 1, 3).Value
    > newNote.Close
    > Next fNum
    >
    > Set fso = Nothing
    >
    > End Sub
    >
    >
    >
    > "tcg_gilbert" <[email protected]> wrote in message
    > news:[email protected]...
    > > I'm trying to pull some text out of a lengthy excel document to do some
    > > text
    > > mining on. I'd like to have a macro (or another solution that would work)
    > > to
    > > pull the headers out of each line (record) and save each line as a text
    > > file.
    > > Any help would be greatly appreciated!
    > > Thanks
    > >
    > > So, if I had an excel (.csv file) file that captured email content
    > > (someone
    > > in the past did this manually) like this:
    > >
    > > Subject,Body,Date
    > > Lunch Plans,Do you have lunch plans?,4/5/2006
    > > What's Up?,What are you up to today?,4/5/2006
    > >
    > > Would be converted to 2 .txt files like this:
    > >
    > > Text File 1:
    > > Subject:
    > > Lunch Plans
    > >
    > > Body:
    > > Do you have Lunch Plans?
    > >
    > > Date:
    > > 4/5/2006
    > >
    > > Text File 2:
    > > Subject:
    > > What's Up?
    > >
    > > Body:
    > > What are you up to today?
    > >
    > > Date:
    > > 4/5/2006
    > >
    > >
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1