• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

macro to generate multiple files from data in excel cells

I am looking for an macro that will:
1. interate through all rows of an active excel document and for each row:
a. Read the first contents of a first column,
b. Create a new file, using the first contents as a name
c Read the second contents of a second column
d. Write the second contents into the text file.
e. Close the text file

Can somebody help?

Thank you!
  • 2
1 Solution

Here is a Macro that can be added to your document that reads all the data in column a and uses it for the file name (Is that path in the file name?  If not, you may need to adjust the Macro to have the path).  It then grabs the data in the column B and save it to the file from column A.  Let me know if you have any questions.

Just open up your Excel File and Press ALT-F11 to open the Macro Editor.  Then paste this code and save.  You can run the Macro through the Macro Menu (This depends on what version you have on how to find it).

Sub SaveFiles()
    Dim objFSO As Scripting.FileSystemObject
    Dim objText As Scripting.TextStream
    Dim strStartCell As String
    Dim strEndCell As String
    Dim strCol As String
    Dim rngFiles As Range
    Dim strPath As String
    ' ** Set your Default Path Here **
    ' Make sure this value ends with a backslash "\"
    strPath = "C:\"
    ' Access for Writing Files
    Set objFSO = New Scripting.FileSystemObject
    ' Set Column and starting cell values
    strCol = "A"
    strStartCell = strCol & "1"
    strEndCell = strCol & CStr(Cells(Rows.Count, strCol).End(xlUp).Row)
    ' Loop through rows with Data
    For Each rngFiles In Range(strStartCell, strEndCell)
        Set objText = objFSO.CreateTextFile(strPath & rngFiles.Value)
        objText.Write rngFiles.Offset(0, 1).Value
        MsgBox rngFiles.Offset(0, 1).Value
    Next rngFiles
    Set objText = Nothing
    Set objFSO = Nothing

End Sub

Open in new window

Patrick MatthewsCommented:

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

The sample file should clearly illustrate both the input and the expected output given that input.  The sample file need not be very large, but it should have enough examples to cover the expected range of values/scenarios.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

cyber-33Author Commented:
I followed the instructions, but got the compile error: "User-Defined Type not defined" pointing to Scripting.FileSystemObject.

I suspect I need to have some sort of "include" to connect the compiler to this library...Not an expert in vb, though :)

cyber-33Author Commented:
The compilation problem was resolved by adding a reference in the Tools/Reference dialog to "Microsoft Scripting Runtime" library.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now