+ Reply to Thread
Results 1 to 3 of 3

Creating a text file in VBA from a column in Excel2016

  1. #1
    Registered User
    Join Date
    06-18-2014
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Creating a text file in VBA from a column in Excel2016

    Hi
    I'm sure this is simple, but it's driving me mad at the moment.

    I have a column of data that I need to convert to a text file.

    The code I'm using is:
    Set rng = ActiveSheet.Range("O5", "O40000") 'this is the range which will be written to text file
    stFilename = "E:\Rajja\TxtFiles\AAH.txt"
    stSeparator = "|"
    stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
    '-------------------------------------------------------------------------------------

    For lRow = 1 To rng.Rows.count
    If rng.Columns.count = 1 Then
    stNextLine = rng.Rows(lRow).Value
    Else
    stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
    End If
    If stOutput = "" Then
    'stOutput = stNextLine
    Else
    stOutput = stOutput & vbCrLf & stNextLine
    End If
    Next lRow

    Set fso = CreateObject("ADODB.Stream")
    With fso
    .Type = 2
    .Charset = stEncoding
    .Open
    .WriteText stOutput
    .SaveToFile stFilename, 2
    End With
    Set fso = Nothing


    The problem I'm having is that because the first 140 cells are empty, the text file starts with the first filled cell and I want to include the empty cells. What am I missing?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Creating a text file in VBA from a column in Excel2016

    Please take a few minutes to re-read the forum rules, and then edit your post to add CODE tags.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Creating a text file in VBA from a column in Excel2016

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Changing Scatter Plot Axes Labels (Excel2016)
    By aanthnnyyy in forum Excel General
    Replies: 3
    Last Post: 09-25-2016, 08:58 AM
  2. Replies: 3
    Last Post: 04-21-2016, 02:52 AM
  3. Replies: 2
    Last Post: 08-16-2013, 02:45 AM
  4. Replies: 1
    Last Post: 02-23-2013, 08:36 AM
  5. Need help with creating a text file via excel
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 AM
  6. Need help with creating a text file via excel
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  7. Need help with creating a text file via excel
    By ANDRE.TASSEL@ELECTROLUX.CO.UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1