VBA - excel to notepad

[tlarse]

Hello,

I have created a macro that pastes a range of excel data (via a loop function) into a .dat file, which is later FTP'd to another team. The only issue that I'm running into is that they cannot accept the file with a space at the end, which is unfortunately automatically entered into the .dat file by my vba code.

Has anyone ever encountered this issue before? Any advice would be EXTREMELY appreciated.

Thanks in advance.

CODE:

Sheets(sheet1).Select
Endrow = Range("B999999").End(xlUP).Row
MyFile = "\\C:....."
fnum = FreeFile()
Open MyFile For Output As fnum
x = 2 to endrow
Print #fnum, Range("B"&X)
Next x
End Sub

 

[Cindy Ellis]

Hello, and welcome to Mr. Excel!
I can't be positive, since there were several syntax errors in the code that was posted, but it seems that closing the file with "close fnum" solves the problem of the extraneous character at the end.
Here's my version of your code...

Sub Write2Dat()
Dim Endrow As Long
Dim MyFile As String
Dim fnum
Dim X As Long
Sheets("Sheet1").Select
Endrow = Range("B999999").End(xlUp).Row
MyFile = "C:\Documents and Settings\...." 'your output filename here
fnum = FreeFile()
Open MyFile For Output As fnum
For X = 2 To Endrow
Print #fnum, Range("B" & X)
Next X
Close fnum
End Sub

Hope that helps,
Cindy

 

[tlarse]

Cindy,

Thank you for your help! I actually had the close fnum function in my code, but neglected to post it.

The space is still showing up in the dat file. Any other ideas?

Thanks again!

 

[Cindy Ellis]

Possibly...
It appears that numbers are followed by spaces, before the EOL/Linebreak character (not just at the end of the file...any line that is not a string).
Is your last line numeric?
I'm not sure of the solution yet...but I'm guessing that's the source of the problem.
Cindy

 

[Cindy Ellis]

After a little experimentation, I think I have a solution for you.
Try the following:

Sub Print2Dat()
Dim Endrow As Long
Dim MyFile As String
Dim fnum
Dim X As Long
Sheets("Sheet1").Select
Endrow = Range("B999999").End(xlUp).Row
MyFile = "C:\...."  'your filename here
fnum = FreeFile()
Open MyFile For Output As fnum
For X = 2 To Endrow
Print #fnum, Format(Range("B" & X))
Next X
Close fnum
End Sub

Hope that helps,
Cindy

 

[AlphaFrog]

Try something like this...

Sub Write2Dat()
    Dim MyFile As String
    Dim fnum
    Dim strTemp As String
    Dim X      As Long
    Sheets("Sheet1").Select
[COLOR="Red"]    For X = 2 To Range("B" & Rows.Count).End(xlUp).Row
        strTemp = strTemp & Range("B" & X) & vbCrLf
    Next X[/COLOR]
    MyFile = "C:\Temp\Test.txt"    'your output filename here
    fnum = FreeFile()
    Open MyFile For Output As fnum
    Print #fnum, [COLOR="Red"]strTemp[/COLOR]
    Close fnum
End Sub

 

[tlarse]

Cindy,

Thank you so much for your help. That worked perfectly!

Best

 

[tlarse]

Just found another alternative in case anyone is interested. This leaves the space after each line, except for the last.

Thanks.

Sub Write2Dat()
Dim Endrow As Long
Dim MyFile As String
Dim fnum
Dim X As Long
Sheets("Sheet1").Select
Endrow = Range("B999999").End(xlUp).Row
MyFile = "C:\Documents and Settings\...." 'your output filename here
fnum = FreeFile()
Open MyFile For Output As fnum
For X = 2 To Endrow - 1
Print #fnum, Range("B" & X)
Next X
Print #fnum, Range("B" & Endrow);
Close fnum
End Sub

 

[gregtx81]

Trimming those range values also seems to work.

 

[Cindy Ellis]

Thanks for the feedback, glad I could help.