Troubleshooting code for export to text file.

I was wondering if someone would be willing to help me troubleshoot this code. I got the code from here: http://www.cpearson.com/excel/ImpText.aspx

I'm sure that it works fine. But I was trying to modify it to fit my needs. I can step through it fine, but it doesn't generate the text file for the cells I'm selecting. At least, I think I'm selecting them. I might have goofed up the cell ranges.

I want to select 15 rows in a single column. So each cell will go on it's own line in a text file, and I don't want any character separation. In the macro that calls the function(ExportToTextFile), I changed

Sep:=";",

to this

Sep:="",

So here's the full code that I'm trying to debug. I have a separate macro that calls this function:

Function ExportToTextFile(FName As String, _
        Sep As String, SelectionOnly As Boolean, _
        AppendData As Boolean)

        Dim WholeLine As String
        Dim FNum As Integer
        Dim RowNdx As Long
        Dim ColNdx As Integer
        Dim StartRow As Long
        Dim EndRow As Long
        Dim StartCol As Integer
        Dim EndCol As Integer
        Dim CellValue As String

        'Range(Cells(2, 1), Cells(17, 1)).Copy
        Application.ScreenUpdating = False
        On Error GoTo EndMacro:
        FNum = FreeFile


        If SelectionOnly = True Then
        With Selection
        StartRow = .Cells(8).Row
        StartCol = .Cells(5).Column
        EndRow = .Cells(23).Row
        EndCol = .Cells(5).Column
        End With
        End If

        If AppendData = True Then
        Open FName For Append Access Write As #FNum
        End If

        For RowNdx = StartRow To EndRow
        WholeLine = ""
        For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
        Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
        Print #FNum, WholeLine
        Next RowNdx

EndMacro:
        On Error GoTo 0
        Application.ScreenUpdating = True
        Close #FNum

End Function

It appears that nothing happens when I get to this line of

Open FName For Append Access Write As #FNum

If anyone can take the time to explain to me what I'm doing wrong, I would really appreciate it.

Thanks,
-gshock

If AppendData is false, you'll never open a file, yet the code continues. That's bad.

I can't envision why you would set the row and column numbers as you do. And if SelectionOnly is not true, you wouldn't set them at all.

Why do you have the OnError statement? The first step if you get errors is to find out why. Start by taking it out.

Take out Application.ScreenUpdating = False too, until the code is stable.

Edit: And in fairness to Chip, that code is modified substantially from the link. Did you try his code as-is?

Yeah, but you took out the wrong code. Go back to the original, pass the input parameters correctly, and step through it.

After you get it working, edit as desired.

Shg, thanks for your patience with me on this one.

Ok, I followed your suggestion and copied Chip's original code exactly. For my needs, I created a loop, and I'm calling the function 4 times for each iteration of the loop. I commented out the Application.Screenupdating=False, and the OnError statements as you suggested from your last post. When I step through the code, it seems to be grabbing the text that I want, but it's not creating the text file. Can you help me figure out what's wrong? Thanks again for your time. I'm very grateful for all of your help.

Sub exp_File_Export()
    For Each Cell In Range("E6:BB6")
        If Cell.Value = "Make Expression File" Then
            count = count + 1
        End If
    Next Cell    
    
        Dim RetStr As String, Flags As Long, DoCenter As Boolean
        Flags = BIF_RETURNONLYFSDIRS
        Flags = Flags + BIF_NEWDIALOGSTYLE
        
        RetStr = GetDirectory(CurDir, Flags, DoCenter, "Please select a location to store .exp files")
        If RetStr <> "" Then MsgBox RetStr
    
    Sheets("Expression_Files").Activate
    Set Wsht = Worksheets("Expression_Files")
                        
    For i = 5 To 6          'Change this back to "count" when stable.
        Cells(6, i).Select
        Set Rng = ActiveCell
        Wrd = "Make Expression File"
        With Wsht
                Set Fnd = Rng.Find(Wrd, LookAt:=xlPart, MatchCase:=True)
                
                If Not Fnd Is Nothing Then
                    For j = 8 To 23
                        Range(Cells(8, i), Cells(23, i)).Select
                        ExportToTextFile FName:="C:\Test1.txt", Sep:=" ", _
                        SelectionOnly:=True, AppendData:=True
                    Next j
                    
                    For j = 28 To 70
                        Range(Cells(28, i), Cells(70, i)).Select
                        ExportToTextFile FName:="C:\Test2.txt", Sep:=" ", _
                        SelectionOnly:=True, AppendData:=True
                    Next j
                
                    For j = 75 To 101
                        Range(Cells(75, i), Cells(101, i)).Select
                        ExportToTextFile FName:="C:\Test3.txt", Sep:=" ", _
                        SelectionOnly:=True, AppendData:=True
                    Next j
                
                    For j = 106 To 117
                        Range(Cells(106, i), Cells(117, i)).Select
                        ExportToTextFile FName:="C:\Test4.txt", Sep:=" ", _
                        SelectionOnly:=True, AppendData:=True
                    Next j
                End If
        End With
    Next i
End Sub
--------------------------------------------------------------------------
Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


'Application.ScreenUpdating = False
'On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.count).Row
        EndCol = .Cells(.Cells.count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.count).Row
        EndCol = .Cells(.Cells.count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
'On Error GoTo 0
'Application.ScreenUpdating = True
Close #FNum

End Sub

Regards,
-gshock

I just tried a small example and it worked find.

Do you rellay intend to open the file for Append (add your stuff to the end)?

These loop is unnecessary, arent't they?

                    For j = 8 To 23
                        Range(Cells(8, i), Cells(23, i)).Select
                        ExportToTextFile FName:="C:\Test1.txt", Sep:=" ", _
                        SelectionOnly:=True, AppendData:=True
                    Next j

Just

                        Range(Cells(8, i), Cells(23, i)).Select
                        ExportToTextFile FName:="C:\Test1.txt", Sep:=" ", _
                        SelectionOnly:=True, AppendData:=True

... but it's not creating the text file.

Sure you're looking in the right directory? The file is created immediately on execution of the Open statement.