Solved

macro to generate multiple files from data in excel cells

Posted on 2011-09-27
4
238 Views
Last Modified: 2012-10-17
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!
0
Comment
Question by:cyber-33
  • 2
4 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 36713916
cyber-33,

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).

-Bear
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
        objText.Close
    Next rngFiles
    
    Set objText = Nothing
    Set objFSO = Nothing

End Sub

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36716953
cyber-33,

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.

Patrick
0
 

Author Comment

by:cyber-33
ID: 36718456
ltlbearand3:
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 :)

Thoughts?
0
 

Author Comment

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

816 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

8 Experts available now in Live!

Get 1:1 Help Now