Exporting Excel data to text file using VBA not working

[Dewey32]

Hello -


I am new to VBA programming and I have written a macro (see below) to export a range of Excel cells values to a .txt file


Specifically, what I am trying to do is create a macro that

• Selects a section of integer data from a spreadsheet
• Copy it to a text file so that each integer spreadsheet cell value is separated by a space (no commas).

Example Spreadsheet data Example of Desired Text File Output



-- removed inline image ---

-- removed inline image ---


While the below code seems to be selecting the range of data correctly (in the below example, I am trying to select a 4 column x 35 row section of data) the text file output looks like this:



-- removed inline image ---


I think the problem lies in the For loop structure - can anyone see what is causing the problem?


THANKS!

Sub WriteTextFile()

Dim myFile As String
Dim rng As Range
Dim cellValue As Integer
Dim i As Integer
Dim j As Integer
Dim WholeLine As String

Application.DefaultFilePath = "E:\Folder1\Folder2\Folder3\Folder4"

myFile = Application.DefaultFilePath & "\Output_Text_File.txt"

Set rng = Range("B51:E85")

Open myFile For Output As #1

For i = 1 To 35
WholeLine = ""
For j = 1 To 4
cellValue = cellValue + rng.Cells(i, j).Value
WholeLine = WholeLine & cellValue & " "
Next j
Write #1, WholeLine
Next i

Close #1
MsgBox ("Done")
End Sub

 

[Tinbendr]

You need to set the loop counters the same as your row/column numbers.

[FONT=Times New Roman][SIZE=3][COLOR=#000000]For i = 51 To 85[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]WholeLine = ""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]For j = 2 To 5[/COLOR][/SIZE][/FONT]

 

[Dewey32]

Thanks Tinbendr, I tired your suggestion and instead of the output file looking like this

1 3 5 4
1 5 4 0
1 2 5 4
1 8 5 4
1 3 4 0
1 2 4 0
1 8 4 0
1 8 3 4

looks like this instead:
"0 0 0 0"
"0 0 0 0"
"0 0 0 0"
"0 0 0 0"
"0 0 0 0"
"0 0 0 0"
"0 0 0 0"
"0 0 0 0"

Do I need some kind of counter to step through the array?

Thanks again for your help.

 

[Tinbendr]

Sub WriteTextFile()

Dim myFile As String
Dim i As Integer
Dim j As Integer

Application.DefaultFilePath = "E:\Folder1\Folder2\Folder3\Folder4"

myFile = Application.DefaultFilePath & "\Output_Text_File.txt"

'Output Range("B51:E85")

Open myFile For Output As #1

For i = 51 To 85
    For j = 2 To 5
        Print #1, Sheet1.Cells(i, j);
    Next j
    Print #1, vbCr
Next i

Close #1
MsgBox ("Done")
End Sub

 

[Dewey32]

success.....thanks!!

 

[Dewey32]

Hello

I have encountered a problem using the output .txt generated by the above code when I use the file as input into a java program.

I've identified the problem as unwanted spaces in the .txt input file. For example, the input I am seeking should look like this:

6_4_7_5_0
5_3_8_1_6
1_3_6_9_2
Where the underscore, _, indicates a space. Specifically, I don't want a space at the beginning and end of the line and only one space to separate each number value from the others.

Instead, I am finding the above code has an output format like this
_6__4__7__5__0_
_5__3__8__1__6_
_1__3__6__9__2_
Where the underscore, _, indicates a space. It's like each number value is "flanked" by a space causing double spaces to occur in the middle of the line of numbers.

My web searches find that the TRIM and CLEAN functions can be used to remove unwanted spaces but the online code examples mostly address how to remove unwanted spacing while *importing* data from a text file into an Excel spreadsheet not how to remove unwanted spacing while*exporting* Excel data to a text file which is what I am trying to do.

Does anyone have any suggestions of how I can adapt the above code so that the resulting output .txt file has no spaces at the beginning and end of the line and only one space in between the each number?

Thanks

 

[Tinbendr]

When you look at it with Notepad, how many spaces do you see?

 

[Dewey32]

When I open it it in Notepad I see one space before the first number followed by 2 spaces between each of the following numbers with the final number having one space after it.


Hope this example shows what I am seeing in Notepad

_6__4__7__5__0_
_5__3__8__1__6_
_1__3__6__9__2_
Where the underscore indicates a space.

 

[Cindy Ellis]

Give this a try. The logic isn't as neat and tidy as I would like, but it seems to work, at least on a limited data set. I added a few variables to make it more generic, in case your data isn't always in the same location:

Sub WriteTextFile()

Dim myFile As String
Dim i As Integer
Dim j As Integer
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long


'Application.DefaultFilePath = "E:\Folder1\Folder2\Folder3\Folder4"
Application.DefaultFilePath = "C:\users\cellis\desktop"


myFile = Application.DefaultFilePath & "\Output_Text_File.txt"


'Output Range("B51:E85")


Open myFile For Output As #1
FirstCol = 2
LastCol = 5
FirstRow = 51
LastRow = 85


For i = FirstRow To LastRow
    For j = FirstCol To LastCol - 1 '1 less than the final column number
       Print #1, Format(Worksheets("Sheet1").Cells(i, j).Value, "#0 ");
    Next j
    Print #1, Format(Worksheets("Sheet1").Cells(i, LastCol).Value, "#0");
    Print #1, vbCr
Next i


Close #1
MsgBox ("Done")
End Sub

 

[Dewey32]

Thanks Cindy.

I entered your code as a macro into my spreadsheet and ran several of the output files through my java file. So far everything is working great.

I'd like to thank both you and Tinbendr for helping out on this. You both saved me a lot of time and taught me a few things about VBA coding.
I really appreciate your help!