Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007

    Unanswered: How to covert a text wrapped text file to Excel file?

    Someone very nice shared with me a very useful function to convert text file to excel and it works very well. However, when processing a big text wrapped file with too many columns (see attached file), it errors out with the following error message.

    Run-time error '1004'; Application-defined or object-defined error.

    highlight at this line in the following function
    .ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select

    Any insight to resolve this problem? Thank you very much!

    Here is the function:

    Public Function ProcessTextFile(ByVal TextFileName As String, ByVal delim1 As String, ByVal ExcelFileName As String)

    Dim appExcel As Excel.Application
    Dim intHandle As Integer
    Dim strLine As String
    Dim varElements As Variant
    Dim intCol As Integer
    Dim intRow As Integer

    Set appExcel = New Excel.Application
    With appExcel
    '.Visible = True
    .Visible = False
    intHandle = FreeFile
    Open TextFileName For Input As #intHandle
    Do Until EOF(intHandle)
    intRow = intRow + 1
    Line Input #intHandle, strLine

    varElements = Split(strLine, delim1)
    For intCol = 0 To UBound(varElements)
    .ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
    .Selection = varElements(intCol)
    Next intCol
    Close #intHandle
    .ActiveWorkbook.SaveAs filename:=ExcelFileName
    End With
    Set appExcel = Nothing

    End Function
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2007

    if open the file use wordpad, there will be no wrap

    The original tab delimited text file extension is .REC. If I manually open it with notepad, it still has text wrapped. If I manually open it with wordpad, there is no text wrap. The problem I have is I use the function to directly convert the .REC file to excel. I guess it opens it as notepad first and then convert to excel. Is there any way I can use access VBA to specify it to be opened using wordpad then convert to excel.

    Any help will be greatly appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts