none
Converting Worksheets to Text File RRS feed

  • Question

  • I am trying to convert several of the worksheets in my workbook to text files.

    I tried saving each as a prn file, but some of the data was getting cut off.

    Next I tried this:

    Code:
    'loop worksheets one by one, for each worksheet, open file "WkshtName.prn" as #1 for output and call:
    
    Sub WriteToFile(mySheet As Worksheet, intColCount As Integer)
    
    Dim intRowNum As Integer, j As Integer
    Dim strWholeLine As String
    Dim myCell As Range
    Dim myText As String
    
    
    'Application.ScreenUpdating = False
             For intRowNum = 1 To      mySheet.UsedRange.Rows.Count
                        'only write this row if not hidden
                        
                If Range("A1").Offset(intRowNum - 1, 0).Rows(1).Hidden = False Then
                    For j = 1 To intColCount
                                                         
                    'only write this column if not hidden
                        If Range("A1").Offset(, j - 1).Columns(1).Hidden = False Then
                         Set myCell = Range("A1").Offset(intRowNum - 1, j - 1)
                         myCell.Activate
                         myText = myCell.Text
                         strWholeLine = strWholeLine & myText & PadBlanks(myCell.Width / 4 - Len(myText))
                        End If
                         
                    Next j
                    Print #1, strWholeLine
                    strWholeLine = ""
                End If
    
             Next intRowNum
                    
    ErrExit:
        Close #1
        Application.ScreenUpdating = True
        Exit Sub
    ErrHandle:
        MsgBox "Line: " & intRowNum & " Col: " & j & " Text: " & ActiveCell(intRowNum, j).Text
        MsgBox Err.number & ":  " & Err.Description
        Resume ErrExit
    End Sub


    This works for all of my worksheets except one. That worksheet randomly freezes in the middle, stopping the entire process. I have identified that it always freezes on one specific column, but the "freeze row" varies. 

    I never even get to the error handler - it just quits in the middle.

    Any suggestions?

    Thanks!
    TemiU

    Temi Unger .Net Developer

    Thursday, October 15, 2015 6:00 PM

Answers

  • I think I got it now.

    Since it was always breaking on a cell with a numeric value, I set the myText variable equal to the value of the cell instead of the text of the cell, and then formatted myText to display the proper numeric format:

    If Range("A1").Offset(, j - 1).Columns(1).Hidden = False Then
                         Set myCell = Range("A1").Offset(intRowNum - 1, j - 1)
                        
                         myCell.Activate
                         If IsNumeric(myCell.Value)
                                            myText = CStr(myCell.Value)
                                            myText = Format(myText, "#,###,###")
                                        Else
                                            myText = myCell.Text
                                        End If
                         strWholeLine = strWholeLine & myText & PadBlanks(myCell.Width / 4 - Len(myText))
                        End If

    This works, it was just a bit tricky on specific cells where the number needed to be displayed in text format, not in numeric format, but some more "If" statements helped with that.



    Temi Unger .Net Developer

    • Marked as answer by TemiU Sunday, October 18, 2015 7:29 AM
    Sunday, October 18, 2015 7:29 AM

All replies

  • Hi TemiU,

    Since the issue is rely on the context of the worksheet, I am not able to find the root cause for this issue. To narrow down this issue, I suggest that you debug the code to see which line of code cause this issue.

    >>I never even get to the error handler - it just quits in the middle.<<

    I am not able to see that "On Error" in the code above, if you want to the code go to the specific label, we need to using "On Error" like below:

    Sub WriteToFile(mySheet As Worksheet, intColCount As Integer)
    
    Dim intRowNum As Integer, j As Integer
    Dim strWholeLine As String
    Dim myCell As Range
    Dim myText As String
    
    On Error GoTo ErrHandle
    ...
    End sub

    You can get more detail about error handling from link below:
    How to Use "On Error" to Handle Errors in a Macro

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by André Santo Friday, October 16, 2015 12:52 PM
    Friday, October 16, 2015 6:39 AM
    Moderator
  • Hi Fei,

    You are correct that I forgot to put in the "On Error Goto" line.

    However, this did not solve my issue.  The program is still breaking randomly without ever getting to the error handler.

    Debugging is of no use, since when I step through the code, it always runs.  It only does not work when I try to run it without the debugger.

    Thanks,

    Temi


    Temi Unger .Net Developer

    Sunday, October 18, 2015 6:22 AM
  • I think I got it now.

    Since it was always breaking on a cell with a numeric value, I set the myText variable equal to the value of the cell instead of the text of the cell, and then formatted myText to display the proper numeric format:

    If Range("A1").Offset(, j - 1).Columns(1).Hidden = False Then
                         Set myCell = Range("A1").Offset(intRowNum - 1, j - 1)
                        
                         myCell.Activate
                         If IsNumeric(myCell.Value)
                                            myText = CStr(myCell.Value)
                                            myText = Format(myText, "#,###,###")
                                        Else
                                            myText = myCell.Text
                                        End If
                         strWholeLine = strWholeLine & myText & PadBlanks(myCell.Width / 4 - Len(myText))
                        End If

    This works, it was just a bit tricky on specific cells where the number needed to be displayed in text format, not in numeric format, but some more "If" statements helped with that.



    Temi Unger .Net Developer

    • Marked as answer by TemiU Sunday, October 18, 2015 7:29 AM
    Sunday, October 18, 2015 7:29 AM