[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

macro to generate multiple files from data in excel cells

Posted on 2011-09-27
4
Medium Priority
?
264 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
[X]
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
  • 2
4 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 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 93

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

656 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