Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Creating a flat file from Excel file

  1. #1
    Join Date
    25th January 2012
    Location
    Muskegon, MI
    Posts
    10

    Creating a flat file from Excel file

    I am trying to get data from an excel worksheet to a flat file for data input into a Coast Guard file.

    Example of data in worksheet:

    [company] COMPANY: So & So Charters DBA: Capt. Jack Sparrow ADDRESS1: 3881 Home Rd ADDRESS2: CITY: My Town STATE: MI ZIP: 99999 EMAIL: captjack.so&socharters@hotmailcom [VESSEL] MC 2222 NC


    The flat file needs to output the above data like this:

    [company]
    COMPANY: So & So Charters
    DBA: Capt. Jack Sparrow
    ADDRESS1: 3881 Home Rd
    ADDRESS2:
    CITY: My Town
    STATE: MI
    ZIP: 99999
    EMAIL: captjack.so&socharters
    [VESSEL] MC 2222 NC
    [company]
    COMPANY: La-Te-Da
    DBA: Capt. K King
    ADDRESS1: 12345 S West St
    ADDRESS2:
    CITY: K-Zoo
    STATE: MI
    ZIP: 99999
    EMAIL: Capt.k.king@La-Te-Da.com

    We only do this once a year and it would help if we had a way to automate the file output.

    Thanks in advance,

    Lynn
    Remember: Data you don't have at least two copies of is data you just don't care about.

  2. #2
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    3,301

    Re: Creating a flat file from Excel file

    Hi Lynn
    something like...
    VB:
    Sub ExportRange() 
        Dim ExpRng As Range 
        Set ExpRng = ActiveCell.CurrentRegion 
        FirstCol = ExpRng.Columns(1).Column 
        LastCol = FirstCol + ExpRng.Columns.Count - 1 
        FirstRow = ExpRng.Rows(1).Row 
        LastRow = FirstRow + ExpRng.Rows.Count - 1 
         
        Open ThisWorkbook.Path & "\ptest File.txt" For Output As #1 
        For r = FirstRow To LastRow 
            For c = FirstCol To LastCol 
                Debug.Print Cells(r, c).Value 
                data = Cells(r, c).Value 
                Write #1, data 
                 
            Next c 
        Next r 
        Close #1 
    End Sub 
    
    
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Golden Rules | Forum Rules | Freebie Stuff | Smallman Freebies

  3. #3
    Join Date
    25th January 2012
    Location
    Muskegon, MI
    Posts
    10

    Re: Creating a flat file from Excel file

    Thank you very much.

    The only problem I see is the the output txt is all in Double Quotes on each line and will truncate. It looks like this:

    "[company] "
    "COMPANY: So & So Charters"
    "DBA: Capt. Jack Sparrow"
    "ADDRESS1: 3881 Home Rd "
    "ADDRESS2: "
    "CITY: My Town "
    "STATE: MI "
    "ZIP: 99999 "
    "EMAIL: captjack.so&socharters "
    "[VESSEL] MC 2222 NC "

    Is there a way to add a Clean and Trim to the output?

    I will gladly donate to your cause.

    Thanks again!

    Lynn
    Remember: Data you don't have at least two copies of is data you just don't care about.

  4. #4
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    3,301

    Re: Creating a flat file from Excel file

    Ok
    change write to print to remove the ""
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Golden Rules | Forum Rules | Freebie Stuff | Smallman Freebies

  5. #5
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    3,301

    Re: Creating a flat file from Excel file

    can also remove the
    VB:
    Debug.Print 
    
    
    VB:
    Sub ExportRange() 
        Dim ExpRng As Range 
        Set ExpRng = ActiveCell.CurrentRegion 
        FirstCol = ExpRng.Columns(1).Column 
        LastCol = FirstCol + ExpRng.Columns.Count - 1 
        FirstRow = ExpRng.Rows(1).Row 
        LastRow = FirstRow + ExpRng.Rows.Count - 1 
         
        Open ThisWorkbook.Path & "\ptest File.txt" For Output As #1 
        For r = FirstRow To LastRow 
            For c = FirstCol To LastCol 
                data = Cells(r, c).Value 
                print #1, data 
                 
            Next c 
        Next r 
        Close #1 
    End Sub 
    
    
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Golden Rules | Forum Rules | Freebie Stuff | Smallman Freebies

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: January 19th, 2012, 20:26
  2. File Locking A Flat File Over The Network
    By mumphis in forum EXCEL HELP
    Replies: 6
    Last Post: October 1st, 2006, 23:40
  3. Extract info from flat .txt file using VBA
    By jsuiter in forum EXCEL HELP
    Replies: 2
    Last Post: March 6th, 2006, 00:50
  4. Creating new File in Excel
    By ShadowMoon in forum EXCEL HELP
    Replies: 4
    Last Post: October 5th, 2004, 21:45

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
  •  
porno