How to avoid blank lines when pasting from Excel into a text file.

[Prasad Vanka]

Hi,

I have a Excel sheet that contains data in the first two columns
(first row is the heading) as
Company ID Company Name
AIG Ai Claims Solutions
AUH Autologic Holdings
BCI_SOS Blue Circle Industries

When I am running the following macro I am getting two extra blank
lines at the bottom in the output text file. Can someone please help
me in avoiding this.

I want my output text file to have only 3 lines for the three rows
(rows 2, 3 and 4) in the Excel sheet.

Thank You.

***********************************************************************
Private Sub cmdCreateCompanyListFile_Click()
Dim iCount As Integer
Dim MyData As DataObject

Application.ScreenUpdating = False

Set MyData = New DataObject

iCount = 0
Range("A2").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 2) = ActiveCell.Value & "#" &
ActiveCell.Offset(0, 1).Value & "#"
ActiveCell.Offset(1, 0).Select
iCount = iCount + 1
End If
Loop Until IsEmpty(ActiveCell) = True

Range("C2", "C" & iCount + 1).Copy

MyData.GetFromClipboard
Open "\\Brscfp1c\winforms\PRINT\Finance\FI - Company Names.txt"
For Output As #1
Print #1, MyData.GetText(1)
Close #1

Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Application.ActiveWorkbook.Save
End Sub
*****************************************************************************

 

[Tom Ogilvy]

Private Sub cmdCreateCompanyListFile_Click()
Dim iCount As Integer
Dim cell as Range

Application.ScreenUpdating = False

iCount = 0
Range("A2").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 2) = ActiveCell.Value & "#" & _
ActiveCell.Offset(0, 1).Value & "#"
ActiveCell.Offset(1, 0).Select
iCount = iCount + 1
End If
Loop Until IsEmpty(ActiveCell) = True

Open "\\Brscfp1c\winforms\PRINT\Finance\FI - Company Names.txt" _
For Output As #1

for each cell in Range("C2", "C" & iCount + 1)

Print #1, cell.Value

Next
Close #1

Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Application.ActiveWorkbook.Save
End Sub

--
Regards,
Tom Ogilvy

Hi,

I have a Excel sheet that contains data in the first two columns
(first row is the heading) as
Company ID Company Name
AIG Ai Claims Solutions
AUH Autologic Holdings
BCI_SOS Blue Circle Industries

When I am running the following macro I am getting two extra blank
lines at the bottom in the output text file. Can someone please help
me in avoiding this.

I want my output text file to have only 3 lines for the three rows
(rows 2, 3 and 4) in the Excel sheet.

Thank You.

***********************************************************************
Private Sub cmdCreateCompanyListFile_Click()
Dim iCount As Integer
Dim MyData As DataObject

Application.ScreenUpdating = False

Set MyData = New DataObject

iCount = 0
Range("A2").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 2) = ActiveCell.Value & "#" &
ActiveCell.Offset(0, 1).Value & "#"
ActiveCell.Offset(1, 0).Select
iCount = iCount + 1
End If
Loop Until IsEmpty(ActiveCell) = True

Range("C2", "C" & iCount + 1).Copy

MyData.GetFromClipboard
Open "\\Brscfp1c\winforms\PRINT\Finance\FI - Company Names.txt"
For Output As #1
Print #1, MyData.GetText(1)
Close #1

Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Application.ActiveWorkbook.Save
End Sub

****************************************************************************
*

 

[Excel Beginner]

Hi Tom,

I am still getting one blank line at the bottom of the page.

regards,
Prasad Vanka

 

[Tom Ogilvy]

Well, we can't have that:

Private Sub cmdCreateCompanyListFile_Click()
Dim iCount As Integer
Dim cell As Range
Dim i As Long, sStr As String

Application.ScreenUpdating = False

iCount = 0
Range("A2").Select
i = 0
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 2) = ActiveCell.Value & "#" & _
ActiveCell.Offset(0, 1).Value & "#"
ActiveCell.Offset(1, 0).Select
iCount = iCount + 1
End If
Loop Until IsEmpty(ActiveCell) = True

Open "C:\Company Names.txt" _
For Output As #1

For Each cell In Range("C2", "C" & iCount + 1)
sStr = cell.Value
If i <> 0 Then sStr = vbCrLf & sStr
Print #1, sStr;
i = 1
Next
Close #1

Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Application.ActiveWorkbook.Save
End Sub

 

[Excel Beginner]

Thanks Tom,

It works now. But if possible can you please explain what is vbCrLf
and the following:
For Each cell In Range("C2", "C" & iCount + 1)
sStr = cell.Value
If i <> 0 Then sStr = vbCrLf & sStr
Print #1, sStr;
i = 1
Next