Results 1 to 10 of 10
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Export Excel to TextFile (VBA/Excel/XP)

    I seem to be having a
    Alan

  2. #2
    Star Lounger
    Join Date
    Sep 2001
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Excel to TextFile (VBA/Excel/XP)

    You might simply construct a string and then write it to a file. The following code snipplet might be handy (I actually used it in Word and didn't try it in Excel, but I assume it will work nevertheless.):

    intFileNumber = FreeFile
    Open strFileName For Binary As intFileNumber

    ' write data, and close file
    Put #intFileNumber, , strToWrite
    Close intFileNumber

    Regards,
    hedgehog

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Export Excel to TextFile (VBA/Excel/XP)

    Perhaps I did not make myself clear.

    What I have is a row in Excel that has numbers across 15 columns. I need to write these from multiple rows to a text file. The decimal places HAVE to line up and the right position of each number has to be in a specific position.

    I have searched and searched. Surely someone has encountered this problem before. My data goes to the mainframe after my users mess with it.
    Alan

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export Excel to TextFile (VBA/Excel/XP)

    There are two ways to do this.

    Assuming 10 characters in total and that the number will never exceed 10 characters including the decimal point and the decimals.

    Dim sNumber as String
    Dim numberToFormat as Single (or Double or any other numeric type that supports decimals)

    numberToFormat=123.45
    1. sNumber=Format(numberToFormat,"0000000.00") would output 123.45 as 0000123.45 or
    2. If you don't want the leading zeros, sNumber=Format(numberToFormat,"#.00") and then sNumber=Space(10-Len(sNumber)) & sNumber
    which will give bbbb123.45 (b=space)

    Regards,

    Kevin Bell

  5. #5
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Export Excel to TextFile (VBA/Excel/XP)

    Cool! This is EXACTLY what I needed. The boss is happy and all is right with the world. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Alan

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Export Excel to TextFile (VBA/Excel/XP)

    Excel's text import wizard understands fixed length (space padded) fields. I wonder whether there is an export to fixed length (space padded) fields somewhere in the product? Oh well, some other time.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Export Excel to TextFile (VBA/Excel/XP)

    Save as Formatted Text Space Delimited (good old *.prn) does this, but it sounds on a quick reading that the poster would need to first restructure the data in Excel from one row to columns and restrict only that data to the sheet to be saved.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Export Excel to TextFile (VBA/Excel/XP)

    I'm the poster. My predecessor in this project did some funky stuff so I have to follow. The mainframe is looking for this data in certain places and formats.

    I have been reading some stuff about <font face="Georgia">TextStream</font face=georgia> and the <font face="Georgia">Column</font face=georgia> property but it does not seem to fit. You would think there was a tab to a Right position or tab to decimal. Maybe in version 15?
    Alan

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export Excel to TextFile (VBA/Excel/XP)

    Those are formatting alignments you might see in a word processor. They have nothing to do with the actual alignment, which you can see if you take a formatted Word doc and save it as text. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Export Excel to TextFile (VBA/Excel/XP)

    As another afterthought (since you have already been provided a good solution), have you tried installing the generic text file print device and using it to print to file? The macro recorder coughs up this starter code:

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Generic / Text Only on FILE:", _
    PrintToFile:=True

    Not sure that the layout will meet your precise needs. I see from the Help that the last parameter can be the file name to print to.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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