Announcement

Collapse
No announcement yet.

Creating a flat file from Excel file

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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&[email protected] [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: [email protected]

    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
    Re: Creating a flat file from Excel file

    Hi Lynn
    something like...
    Code:
    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 | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

    Comment


    • #3
      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.

      Comment


      • #4
        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 | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

        Comment


        • #5
          Re: Creating a flat file from Excel file

          can also remove the
          Code:
          debug.print
          Code:
          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 | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

          Comment

          Working...
          X