DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Exporting to a txt file

  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Exporting to a txt file

    I have multiple rows within a worksheet and need them to automatically save into a separate txt file.

    Example:

    I have a workbook with rows b-z. Instead of taking each of the rows and copying and pasting it into another separate excel file, then saving in a txt format, is there a way to automate this?

    So that the following will occur:

    Column B 950 line is one file
    Column C 950 lines is another
    column D lines in another txt file.....etc

    I have some code listed below, however, it reads the first column cell by cell. It does not group column by column grouping all data within that column as on txt file

    Thanks



    Code:
    Sub notebook_save() 
        Set wkbk = Workbooks.Add 
        Sheets("Sheet1").Select 
        RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row 
        For i = 1 To RowCount 
            Sheets("Sheet1").Select 
            Range("a" & i).Select 
            ActiveCell.EntireRow.Copy 
            Sheets("Sheet2").Select 
            Range("a1").Select 
            ActiveSheet.Paste 
            Application.DisplayAlerts = False 
            ActiveWorkbook.SaveAs _ 
            Filename:="c:\save" & i & ".txt", _ 
            FileFormat:=xlTextMSDOS 
            wkbk.Close 
        Next 
        Application.DisplayAlerts = True 
    End Sub

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Try this version:
    Code:
    Sub notebook_save()
      On Error GoTo Er
      Dim I As Long
      Dim c As Long
      Dim wb As Workbook
      Dim ws As Worksheet
      Dim wst As Worksheet
      
      Set ws = Sheets(1)
      c = ws.UsedRange.Columns.Count
      
      Set wb = Workbooks.Add
      Set wst = wb.Sheets.Add(, Worksheets(Worksheets.Count))
          
      
      For I = 1 To c
        ws.Activate
        ws.UsedRange.Columns(I).Copy
        wst.Paste
        Application.DisplayAlerts = False
        wst.SaveAs ActiveWorkbook.Path & "\save" & I & ".txt", xlTextMSDOS, , , , , False
        Application.DisplayAlerts = True
        wst.UsedRange.Clear
        
      Next I
      
    Ex:
      Application.DisplayAlerts = False
      wb.Close False
      Application.DisplayAlerts = True
      ws.Activate
      Set wst = Nothing
      Set wb = Nothing
      Set ws = Nothing
      Exit Sub
    Er:
      MsgBox Err.Description
      Resume Ex
      Resume
      
    End Sub

  3. #3
    Join Date
    Apr 2007
    Posts
    3
    Hi Ron,

    On the txt output files I recived #REF! for each of the lines items. Why would this be happening?

    Thanks

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    I only tested this on a very simple test sheet, one without formulas in any of the cells. It has got to be the formulas. Try using the PasteSpecial:
    wst.PasteSpecial xlPasteValuesAndNumberFormats

  5. #5
    Join Date
    Apr 2007
    Posts
    3
    It worked! Thanks

Similar Threads

  1. Replies: 6
    Last Post: 08-16-2006, 10:32 PM
  2. Getting the number of lines in a txt file
    By Drew_gable in forum VB Classic
    Replies: 2
    Last Post: 05-28-2006, 07:42 AM
  3. Replies: 0
    Last Post: 02-10-2006, 04:00 AM
  4. Generating Reports from the TXT file.
    By Imran in forum ASP.NET
    Replies: 0
    Last Post: 08-26-2000, 05:08 AM
  5. Creating a form that saves to a txt file
    By vale in forum ASP.NET
    Replies: 1
    Last Post: 08-23-2000, 07:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center