Creating a flat file from Excel file

[LivelyDesigns]

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

[pike]

Hi Lynn
something like...

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 

[LivelyDesigns]

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

[pike]

Ok
change write to print to remove the ""

[pike]

can also remove the

Debug.Print 

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