Results 1 to 3 of 3

Thread: Removing the extra commas in the text file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Removing the extra commas in the text file

    Hello,

    I have this VBA code below, but the output text file has a lot of commas in it.

    Note that i have hidden formulas in my excel file. Most likely this is the reason. Is there a way, then when i generate the text file it will only get the rows which has data.

    'Declaring the variables
    Dim FileName, sLine, Deliminator As String
    Dim LastCol, LastRow, FileNumber As Integer


    'Excel Location and File Name
    'Mname = ActiveSheet.Name & ".txt"
    'MName = ThisWorkbook.Name & ".txt"
    'MName = Worksheets("sheet1").Range("b1") & ".xls"


    MName = "NBD UP " & ActiveSheet.Range("t6") & ".txt"
    FileName = ThisWorkbook.Path & "" & MName


    'Field Separator
    Deliminator = ","


    'Identifying the Last Cell
    LastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    FileNumber = FreeFile


    'Creating or Overwrighting a text file
    Open FileName For Output As FileNumber


    'Reading the data from Excel using For Loop
    For i = 1 To LastRow
    For j = 1 To LastCol


    'Removing Deliminator if it is wrighting the last column
    If j = LastCol Then
    sLine = sLine & Cells(i, j).Value
    Else
    sLine = sLine & Cells(i, j).Value & Deliminator
    End If
    Next j


    'Wrighting data into text file
    Print #FileNumber , sLine
    sLine = ""
    Next i


    'Closing the Text File
    Close #FileNumber


    'Generating message to display
    MsgBox "Text file has been generated"


    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Removing the extra commas in the text file

    Welcome to the MrExcel board!

    Clearly I don't have your worksheet to test with or to understand, but does it change anything if you use these lines instead to find LastCol and LastRow?

    Code:
    LastCol = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    LastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,221
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Removing the extra commas in the text file

    Quote Originally Posted by Donskie View Post
    'Declaring the variables
    Dim FileName, sLine, Deliminator As String
    Dim LastCol, LastRow, FileNumber As Integer
    For the first Dim statement above, only Deliminator will be a String variable... FileName and sLine will both be declared as Variants. Similarly, for the second Dim statement above, only FileNumber will be an Integer... LastCol and LastRow will be declared as Variants. In VB, all variable must be individually declared as to their data type. You can write the first Dim statement like this (do similarly for the second one)...

    Dim FileName As String, sLine As String, Deliminator As String
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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