+ Reply to Thread
Results 1 to 2 of 2

Macro to export specific range of data to text file for each worksheet

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2010, 2013
    Posts
    17

    Macro to export specific range of data to text file for each worksheet

    I am seeking help to write a code that will export variable ranges in each worksheet of a workbook to separate tab-delimited text files that have the same filename but are appended with the name of the worksheet they came from (e.g., \filename_03.txt, \filename_04.txt, ...). For example, I want data starting from I6 down to the last cell of data in column K. Columns I:K will be consistent, but the number of rows will not be the same for each worksheet. Column I has formulas for I6:I60000, so Range selection in code should probably select from K6 down, then over two columns to the left. I've tried writing this, but keep getting a error (see "ExportAll" code below). The range should then be exported to a tab-delimited text file. This should then be repeated for all but the last three worksheets.

    Code should do the following:

    -select range I6:K#, where # = number of rows from K6 thru the last occupied cell in K
    -output range to text file with specified filename appended with "_" & worksheet name
    -be prompted for filename and save location, ideally from same location as workbook
    -repeat for each consecutive worksheet, except for last three ("all","data","summary")
    -should take same filename indicated previously, just with a new appended name


    I have a code that will do this process for one worksheet at a time, but is rather cumbersome for multiple worksheets. (I found these macros on the Web: http://www.cpearson.com/excel/ImpText.aspx).


    Please Login or Register  to view this content.

    I've also tried using the code below to process multiple worksheets, but I get an error on Line 28


    Please Login or Register  to view this content.

    Any suggestions on how to do this properly?

  2. #2
    Registered User
    Join Date
    01-04-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2010, 2013
    Posts
    17

    Re: Macro to export specific range of data to text file for each worksheet

    here is an example file. I would want the macro to process worksheets 03-06, but not "all", "data" or "summary". The exported files would then be: "\20120511_Au-cit_pH5_test_03.txt", "\20120511_Au-cit_pH5_test_04.txt", etc.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Export Each Column on Worksheet as Text File
    By ruezo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-26-2012, 11:33 AM
  2. Transponse and Resize Range when Export to Text File
    By neobavesten in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2009, 08:55 AM
  3. Replies: 0
    Last Post: 10-29-2008, 06:21 PM
  4. macro to export a range to a text file?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2006, 09:30 AM
  5. Export a Named range to Tab seperated text file
    By Peter Kamau in forum Excel General
    Replies: 1
    Last Post: 02-20-2006, 09:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1