Removing the extra commas in the text file

Donskie

New Member
Joined
Aug 24, 2019
Messages
1
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
'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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top