Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export to Text (Office XP)

    Hans,

    I use your code which is supposed to export to text and encounter error msg : Run time error '13' , Type mismatch but don't know did I go wrong. Pls enlighten me on this

    It is highlighted at this part of the code :

    strOutput = strOutput & Left(wsh.Cells(lngRow, lngCol) & _
    Space(255), wsh.Cells(1, lngCol))


    Sub ExportFixed()
    ' Modify as needed, keep trailing backslash
    Const strPath = "Cocuments and SettingsfrancisMy DocumentsExcel"
    Dim wsh As Worksheet
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim f As Integer
    Dim strOutput As String

    For Each wsh In ActiveWorkbook.Worksheets
    f = FreeFile
    Open strPath & wsh.Name & ".txt" For Output As #f
    lngMaxRow = wsh.Range("A65536").End(xlUp).Row
    lngMaxCol = wsh.Range("IV1").End(xlToLeft).Column
    For lngRow = 3 To lngMaxRow
    strOutput = ""
    For lngCol = 1 To lngMaxCol
    strOutput = strOutput & Left(wsh.Cells(lngRow, lngCol) & _
    Space(255), wsh.Cells(1, lngCol))
    Next lngCol
    Print #f, strOutput
    Next lngRow
    Close #f
    Next wsh

    Set wsh = Nothing
    End Sub

    thanks, kun

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Export to Text (Office XP)

    <P ID="edit" class=small>(Edited by macropod on 17-Aug-05 14:42. )</P>Hi kun,

    In the original post (<post#=505138>post 505138</post#>), row 1 had the required column widths. You evidently have something else (text). If you want to use the width of whatever text you have in row 1 to govern the output, try:
    strOutput = strOutput & Left(wsh.Cells(lngRow, lngCol) & Space(255), Len(wsh.Cells(1, lngCol)))
    or
    strOutput = strOutput & Left(wsh.Cells(lngRow, lngCol) & Space(255), 1+Len(wsh.Cells(1, lngCol)))
    to put and extra space in - useful if you're outputting row 1.

    Cheers
    PS: Hans' code also outputs from row 3 down. If you want to start outputting from another row, change the 3 in the line:
    For lngRow = 3 To lngMaxRow
    to suit.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Text (Office XP)

    Thanks, the code run beautifully but the end result is not what I want now. It will be handy in my code library for future reference.
    I have a new post about Excel to text.

    Appreciate your help.

    cheers, kun

Posting Permissions

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