Solved

macro to generate multiple files from data in excel cells

Posted on 2011-09-27
4
222 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 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

13 Experts available now in Live!

Get 1:1 Help Now