Removing the extra commas in the text file

[Donskie]

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

 

[Peter_SSs]

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?

LastCol = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Rick Rothstein]

'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