VBA - excel to notepad

tlarse

New Member
Joined
Sep 29, 2011
Messages
4
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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...
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
After a little experimentation, I think I have a solution for you.
Try the following:
Code:
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
 
Upvote 0
Try something like this...
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,741
Members
452,940
Latest member
rootytrip

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