Avoid blank lines when saving copied data from an excel sheet as a text file.

[Excel Beginner]

Hi,

I have an excel sheet with data as follows with first row being the
heading:
Company ID Company Name
AIG Ai Claims Solutions
AUH Autologic Holdings
BCI_SOS Blue Circle Industries

I have written the following code to write records to the output text
file as AIG#Ai Claims Solutions#
AUH#Autologic Holdings#
BCI_SOS#Blue Circle Industries#

But it is also adding two extra blank lines at the bottom of the text
file.

Can someone help me resolve this.

Thanks
********************************************************************************
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 an excel sheet with data as follows with first row being the
heading:
Company ID Company Name
AIG Ai Claims Solutions
AUH Autologic Holdings
BCI_SOS Blue Circle Industries

I have written the following code to write records to the output text
file as AIG#Ai Claims Solutions#
AUH#Autologic Holdings#
BCI_SOS#Blue Circle Industries#

But it is also adding two extra blank lines at the bottom of the text
file.

Can someone help me resolve this.

Thanks
****************************************************************************
****
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

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