Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

macro to generate multiple files from data in excel cells

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

791 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